VBA Create Yearly Extract Using Column Headings

Hi, I wonder whether someone could possibly help me please.

I'm using the code below to extract data from a 'Source' sheet to populate a "Yearly Extract Summary" 'destination sheet. With the unique distinct values copied from column I on the 'Source' ("All Data") sheet to column B on the 'Destination' ("Desired Output") sheet. In addition the values from column J on the 'Source' sheet are summed and paste under the relevant month on the 'Destination' sheet.

Please Login or Register  to view this content.

The code works fine and the correct figures populate the correct columns and rows on the 'Destination' sheet.

As you can see from the code above, the monthly values have to be hard coded to match the column headings and this is fine when using a static 12 month period. But I'm now wanting to use a rolling 12 month period, which, at the moment, necessitates the need for me to change the code each month so I'd like to change the code but unsure where to even begin, having had help guidance to produce the initial script.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this.

Although I appreciate that the whole script would need to be tweaked, and I'd still like maintain the existing functionality in this section of code:

Please Login or Register  to view this content.

To help I have attached a file which contain 3 sheets.

• The "All Data" 'Source' sheet,

• The "Output" sheet, used for testing, and

• The "Desired Output" sheet which shows the results using the current code

To run the code, please use the button at the top of the "All Data" sheet.

Many thanks and kind regards

Chris

Hi

Not sure how but I attached my response to your earlier post

Re: VBa Dynamic Monthly Column Headings

repeated here

Replace your select case with

Please Login or Register  to view this content.

See attached, I've also added some code to set up a new year in the on change event of "Desired Output" sheet and clear the output.

Regards
Ian

Hi Chris

Wrong version got attached, see attached
Surely you need to reset ever month but feel free to remove or comment out the code in the on change event

Regards
Ian

Hi @ianxwood, thank you very much for coming back to me with this so quickly.

I've tested the updated code you kindly provide, although the problems I initially experienced are no longer there, thank you.

Whilst testing though, I'm sorry but I've noticed another issue. I changed some of the date values around (column J on the All Data) sheet, just to make sure everything was ok. I've attached an updated file with an illustration of the issue and the row in question is highlighted in orange on the "All Data" sheet.

If you look, the date in column J on the highlighted row, is "Feb 14", but when I run the macro, the figure appears under the "Feb 15" column on the "Desired Output" sheet, whereas this column should be blank.

I'm very sorry to have to trouble you again with this, but I just wondered whether you may be able to look at this please.

Many thanks and kind regards

Chris

Hi @Leith Ross thank you so much for taking the time to reply to my post and for putting the code together which works brilliantly, thank you so much.

You will no doubt see my last post to @ianxwood, so I hope this doesn't offend you, but I felt it only right that I should offer the courtesy in allowing him to continue to work on his take of the solution

May I ask though, because I'm eager to learn from this, may I ask if you could insert comments into the code, so I can understand the additions.

All the best and kind regards

Chris

Hi Chris

New version attached, this checks that pdate is within the range D7 to O7 on desired output before calculating the offset.

Regards
Ian

Hi @ianxwood, thank you very much for taking the time to come back to me with this. Thank you also for the code which works great!

May I ask, if it's not too much trouble, could you possibly add some comments to the code, so I can learn from this.

All the best and kind regards

Chris

Hi @Leith Ross, thank you very much for taking the time to come back to me with this, it's exactly what I was after!

All the best and kind regards

Chris

Hi Chris

New version attached with comments added.

Regards
Ian

Hi @Leith Ross, I hope you are well, and I wonder whether you may be able to help me please.

You will no doubt remember that you kindly helped me with a solution to the post I made a few days ago.

I'm sorry to trouble you but while I've been testing this I've come across a slight issue, which I'm hoping you may be able to help me with please.

I've noticed that in the case of where there is a row of data on the "All Data" sheet with a date in column J prior to my first column on the "Desired Output" sheet i.e. "April 14", although the value is correctly not paste to this sheet, the "Description", column I on the "All Data" Sheet is still shown in column B on the "Desired Output" sheet, thus creating a blank row.

I've tried to rectify this over that day or so, but I've been unable to do so. I just wondered whether you could possibly take a look at this please.

To help I've re-attached the last file you post with the code and highlighted the row on the "All Data" and "Desired Output" sheets.

Many thanks and kind regards

Chris

Hi @ianxwood, thank you very much for this, I really appreciate it.

Many thanks and kind regards

Chris

Hi all, I was unable to contact the original author of the code so I made a post here:http://www.vbaexpress.com/forum/show...act&highlight= where you will see I received that little bit of help I needed.

Many thanks and kind regards

Chris