+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    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

  2. #2
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    Ok, then try this

    Please Login or Register  to view this content.
    Please take time to read the forum rules

  3. #3
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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

  4. #4
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    On which line?

    And what does the following line give you

    Please Login or Register  to view this content.
    Have you changed the sheet references to match the correct sheet?

  5. #5
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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

  6. #6
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    Set the msgbox after the lin arr = ...

    This part defines the range

    Please Login or Register  to view this content.
    It count the used range on the sheet and copies it you could instead define it like this

    Please Login or Register  to view this content.
    The other way is more dynamic and copies the complete range no matter the rows.

  7. #7
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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

  8. #8
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    You should have both workbooks open at the same time, then it doesnt matter wich workbook you run the macro from.

    When you get the error then press debug and hold the mouse "arr(i, 1)" does the value there correspond with the sheetname in the master workbook?

  9. #9
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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

  11. #11
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    Hi

    Please Login or Register  to view this content.
    This will work with your sample workbook.
    Be aware that i will give an error because you dont have all the sheets in the master workbook.

  12. #12
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: Search for heading in data sheet and copy range to corresponding sheet in master workb

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1