+ Reply to Thread
Results 1 to 10 of 10

Copy specific data from Monthly auto generated workbook to master sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Copy specific data from Monthly auto generated workbook to master sheet

    I am wondering if this is possible. I get this monthly report that has 5 tabs in it. The last tab, ALL_FAILURES_1mon, is a list of part and serial numbers that have failed that month. From that tab I copy the information into a master workbook that houses all the failures broken up by part number, each part number is a separate tab. I am wondering if there is a way to search in the "Monthly_Report" document for all rows containing the part number, 07X-000-ZZZ" and copy the entire row into the master fails list. I have attached a couple examples with sensitive information blocked out.

    What I need is for when the macro is run, it will search "Monthly_Report.xlsx" ALL_FAILURES_1mon tab, for "07X-000-ZZZ" and copy all rows containing the part number and paste them in the next blank row of "Master_Fails_List" in appropriate tab.

    Any help would be greatly appreciated.

    *NOTE*I have attached both examples however my "Monthly_Report" document was too large so I had to upload it as a .xlsb but the original is .xlsx
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    Maybe:

    Sub onbeillp111zzaa()
    Dim i As Long
    Dim ws As Worksheet
    Dim wb2 As Workbook
    Set ws = Workbooks("Monthly_Report.xlsb").Sheets("ALL_FAILURES_1mon")
    Set wb2 = Workbooks("Master_Fails_List.xlsx")
    ws.Activate
    For i = 2 To Range("A" & Rows.count).End(3)(1).Row
        Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.count).End(3)(2)
    Next i
    End Sub

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    That worked on the example, when i tried to incorporate it into the actual file that has 1500 Rows and 21 part numbers, it gets a run time error, subscript out of range on the line below:
    Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.Count).End(3)(2)

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    A subscript out of range normally means it can't find some object. It could be your wb2 variable or the Sheet name variable. Try changing this:

    Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.Count).End(3)(2)
    To this:

    Rows(i).Copy wb2.Sheets(Range("A" & i).Text).Range("A" & Rows.Count).End(3)(2)

  5. #5
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    same error, does it matter that the new wb2 file has 21 tabs?

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    Is it possible to attach a file which error? Maybe duplicate it and replace sensitive data garble.

  7. #7
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    the actual files are too large, I could email them to you but i cannot attach them.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    Did you try to attach them. I just need a sample which would cause the error. Maybe a copy and delete some of the data.

  9. #9
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    OK, I changed the files to .xlsb in order to attach but they are actually .xlsx files.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy specific data from Monthly auto generated workbook to master sheet

    You were receiving the error because you sheet names changed too include other characters and you changed the name of the destination workbook as well. The code provided does not have that kind of flexibility.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. copy data from multiple sheet to master sheet with specific columns only
    By sinha.riteshabap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 06:59 AM
  2. [SOLVED] macro to auto copy data from multiple sheet to one master sheet
    By roger556 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2012, 01:52 AM
  3. [SOLVED] Search for heading in data sheet and copy range to corresponding sheet in master workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2012, 10:02 AM
  4. Updating master sheet with monthly results on other sheet within same workbook
    By aghoba in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-03-2011, 08:39 PM
  5. Copy and pasting specific sheet data to a master worksheet
    By simora in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2005, 01:06 AM

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