Search for heading in data sheet and copy range to corresponding sheet in master workbook

Hello,

I have a master workbook which contains sheets named 1, 2, 3, 4, 5, 6, 7 etc. I also have a data workbook which contains only one sheet with headings 1,2,3,4,5,6,7 etc in the first row, from C1:BT1. Each heading in the data sheet, corresponds to a sheet in the master workbook. What I would like to do is to copy each column from the data sheet to the corresponding sheet in the master workbook.


For example,

Search in the Data sheet for heading named 1 in the first row (in C1), copy range, C2:C1000, and paste it in the master workbook, in sheet named 1, in AC5

Then search in the data sheet for heading named 2 (in D1), copy range D2:D1000, and paste it in the master workbook, in sheet named 2, in AC5.

Then search in the data sheet for heading named 3 (in E1), copy range E2:E1000, and paste it in the master workbook, in sheet named 3, in AC5.

If this can be done with a macro it would be simply great as I need to paste thousands of columns to the master workbook which will otherwise take me days to complete.

Thank you,
Sans

Hi Steffen,
Thank you very much for your reply. I ran the macro from the master workbook but I get a subscript out of range error. Should there be any empty columns/rows in the data workbook between the heading and the column to be copied?

Thank you,
Sans

On line

Please Login or Register  to view this content.

I also changed the sheet references. Where do I use the

Please Login or Register  to view this content.

Also where in the code can I set which range to copy from the Data workbook to the masterworkbook, i.e. C2:C1000?

Thank you for your help
Sans

Excellent, thank you for the explanation. Upon running the code, I receive the message "70" and after I close the message window, I receive the Subscript out of range error.

One question, do I run the code from the master or the data workbook? I tried both ways but still receive the error.

Thank you very much for your help
Sans

Hi Steffen,

I am attaching the two worksheets so you can have a better look. One is the Master workbook and is named Master, and the other the Data workbook named Data. Now I am getting a slightly different error so I am most likely doing something wrong here.

Thank you,
Sans

If anyone else could help with the macro and tell me what I am doing wrong it would be great as I really need to work on these stats this weekend.

Thank you,
Sans

Hi Steffen,
thank you very much for your reply. I was still getting the error so I experimented around with the code and found out why. I have a sheet in the Master workbook which is named simply 0 and a heading in the Data named 0. For some reason if I change both sheets to anything other than 0, then the macro works. I don't know why this is. Also, if I run the macro from the Master workbook I get the subscript out of range error but it I run it from the data workbook it works correctly - if one of the sheets is not named 0

Do you possibly know why the sheet name 0 might be a problem?

Many thanks,
Sans