Match, Copy and Paste Column Headers - varying number of columns

Hi All.

As with many people on this forum, I have recently delved into VBA and am pretty much learning on the fly. This site, and the others like then have been of immense use. However I am embarking on a project that is not something ive been able to search and find. Most topics are covered with a simple search, this one is a bit beyond me, perhaps I’m using the wrong terminology.

A quick overview of the data we have:

We regularly receive details of monthly payments made and owed to us. There is one worksheet for Payments Paid, and one for Payments Due, in the same workbook. We then add a worksheet, Payment History. This is all against a customer ID.

Hopefully the attachment will show what I mean more clearly.

Payment History is a summary sheet and we construct this by putting ‘Paid month 1’ in a column, and then Due month 1 in the column next to it….then onwards with Paid2/Due2, Paid3/Due3 etc etc. As shown in the workbook.

What I need to do:

I have a macro stored in there that works brilliantly, although it’s not fully complete its just an example of what it should execute. Effectively, it looks for a column name in Payment Paid or Payment Due, matches it with the column name in Payment History and copies and pastes that data into the corresponding column in the summary sheet, Payment History.

In this example we have 12 months of history. I could write the code to copy and paste each column for that 12 months into the summary sheet, but the kicker is, the number of months can vary significantly.

So to avoid handling errors, I would love to know how to loop the code so that it stops copy and pasting at the last column it comes to and finds a match. This sounds pretty dynamic and I’m not even sure this is possible, it’s certainly was above my head. For starters how do I tell the macro to match headers that effectively have to be passed to the macro in the first place??


I really hope I have made sense. Please let me know if I need to elaborate on anything and I will try as best I can to clarify. In the meantime, thanks for reading and thanks in advance for any help.


Itcher