Macro for comparing headers and copy/pasting respective columns

Hi everyone,

I dont seem to be able to find a solution for my problem, maybe someone's got a brainwave?

I've got two sheets in a workbook, one of them has headers with running months and the other headers for a 12-month period (e.g. Jan-09 to Dec-09).

My colleagues are inputting their data in the first sheet, starting with the current month. However, for reporting, I need the data for a fixed twelve month period.

My idea was to create a macro that compares the headers in the two sheets and copies the columns below the respective headers from the first sheet and pastes them in the second sheet.
Since the months in the first sheet are changing every month, I can't seem to find a solution how to do that...

I don't really want to use a look up formula, I'd rather do it with a macro that is linked to a button - so I can be sure it is only updated when I need it to be.

I'd be more than grateful if anyone could point me in the right direction - or even provides me with a code for that...

Thanks for your help!!
Sarah

Hi

The accountant in me totally understands about not wanting figures to change unless you've asked it to update!

The easiest way would be to turn off automatic calculations for that sheet so that you physically have to tell the sheet to calculate when you press F9. Personally, though, I wouldn't trust that.

One way to collate your data would be to use a macro to add the lookup formula you mentioned to each of the cells in the second sheet and using the column header as the criteria. HOWEVER you would then have the macro copy each of the columns that have been created by the formula and Paste Special Values so that your formulas are pasted over. This would give you the functionality of the Excel formula but also ensure that the data didn't change until you ran the macro.

Alternatively you could specify the months you want to collate within a list or array. You could then get the macro to look at the first item in the list (which you could update monthly to show the 12 months that you are currently working on) and use this as the criteria to search for on the first sheet. Once it has found it, copy it and paste it to the second sheet and then go back to your list. Take the second item as the criteria and so on.

This would be fairly simple as you could use a for...next loop from 1 to 12 to search the same 12 cells for the criteria. Then search on the first worksheet, copy entirecolumn, move to the second worksheet and then paste.

A

Feels good to be understood by the fellow accountants out there...

Thanks a lot for the tips!!
I'll probably give the one with the lookup formula a try, not sure if I can manage to do a macro for the list one... Would be worth a challenge though.