Results 1 to 6 of 6

Thread: VBA: Copy rows from one workbook to specific rows in another
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Copy rows from one workbook to specific rows in another

    Hello,

    I need to update the following code that I haphazardly mashed together from several sources on the Internet to be a bit more flexible if rows from the 12M file are added or removed. I also require the code to be able to find the first empty row in the Report file as I have eight of these 12M files in total that need to be stacked going down the Report file.

    Code:
    Sub LocationData()
    
    
    Dim x As Workbook
    Dim y As Workbook
    
    
    '## Open 12M Data (x) and Report (y) workbooks first:
    Set y = Workbooks.Open("File location\Report.xlsm")
    Set x = Workbooks.Open("File location\12M Data.xls")
    
    
    '##12M data copied first
    'un-merge all the cells in the file to allow filtering
        Range("a1:AA600").UnMerge
    'Choses all rows that contain the word Denominator
        Range("A9").AutoFilter Field:=3, Criteria1:="Denominator"
    'Copies only the visible cells
        Range("A10:A394").SpecialCells(xlCellTypeVisible).Copy
    'Opens the Report before pasting
    Set y = Workbooks.Open("File location\Report.xlsm")
    'Pastes the values starting from cell D7
        Workbooks("Report.xlsm").Worksheets("Location").Range("D7").PasteSpecial Paste:=xlPasteValues
    
    
    Set x = Workbooks.Open("File location\12M Data.xls")
        Range("A9").AutoFilter Field:=3, Criteria1:="Denominator"
        Range("B10:B394").SpecialCells(xlCellTypeVisible).Copy
    Set y = Workbooks.Open("File location\\Report.xlsm")
        Workbooks("Report.xlsm").Worksheets("Location").Range("C7").PasteSpecial Paste:=xlPasteValues
    
    
    Set x = Workbooks.Open("File location\12M Data.xls")
        Range("A9").AutoFilter Field:=3, Criteria1:="Denominator"
        Range("D10:D394").SpecialCells(xlCellTypeVisible).Copy
    Set y = Workbooks.Open("File location\\Report.xlsm")
        Workbooks("Report.xlsm").Worksheets("Location").Range("F7").PasteSpecial Paste:=xlPasteValues
    
    
    Set x = Workbooks.Open("File location\12M Data.xls")
        Range("A9").AutoFilter Field:=3, Criteria1:="Item 1"
        Range("D11:D395").SpecialCells(xlCellTypeVisible).Copy
    Set y = Workbooks.Open("File location\\Report.xlsm")
        Workbooks("Report.xlsm").Worksheets("Location").Range("G7").PasteSpecial Paste:=xlPasteValues
    
    
    Set x = Workbooks.Open("File location\12M Data.xls")
        Range("A9").AutoFilter Field:=3, Criteria1:="Item 2"
        Range("D13:D397").SpecialCells(xlCellTypeVisible).Copy
    Set y = Workbooks.Open("File location\\Report.xlsm")
        Workbooks("Report.xlsm").Worksheets("Location").Range("I7").PasteSpecial Paste:=xlPasteValues
    
    
    Set x = Workbooks.Open("File location\12M Data.xls")
        Range("A9").AutoFilter Field:=3, Criteria1:="Item 3"
        Range("D14:D398").SpecialCells(xlCellTypeVisible).Copy
    Set y = Workbooks.Open("File location\\Report.xlsm")
        Workbooks("Report.xlsm").Worksheets("Location").Range("K7").PasteSpecial Paste:=xlPasteValues
    
    
    'Close x - 12M workbook:
    'x = EmptyClipboard()
    Application.CutCopyMode = False
    x.Close SaveChanges:=False
    To put some of the code in context.

    1. I found that the Unmerge command is required as Columns A & B contain merged cells that cover several rows for each location, the filter command only works if they are unmerged first.
    2. AutoFilter - Filters column C, allows me to than copy the data I require in other columns
    3. SpecialCells - I now need this to copy from the first filtered row of data below the AutoFilter (row 9) to the last row containing data, minus 1 row (due to last row containing the overall total which isn't required here).
    4. I need the first set of data to paste into Row 7 downwards, but for the remaining seven 12M files, I need them to find the first empty row. Example - First 12M file pastes into Row 7 to 95, the second 12M file will need to paste into Row 96 and so on.

    If number 4 has to be spread across several Subs is fine as I have assigned eight macros to do this already with the current setup.

    I know my coding is a bit of a mess but I'm a novice at VBA who is self-teaching himself so any help would be greatly appreciated.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA: Copy rows from one workbook to specific rows in another

    4. I need the first set of data to paste into Row 7 downwards, but for the remaining seven 12M files, I need them to find the first empty row. Example - First 12M file pastes into Row 7 to 95, the second 12M file will need to paste into Row 96 and so on.
    You can find the first available row (after the last populated row) in any column like this (this example for column A):

    To set it equal to a number, broken out in multiple steps for illustration purposes:
    Code:
    Dim lastRow as Long
    Dim nextRow as Long
    lastRow = Cells(Rows.Count,"A").End(xlUp).Row
    nextRow = lastRow + 1
    To select the first available cell in column A after the last populated cell in column A:
    Code:
    Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy rows from one workbook to specific rows in another

    Hi Joe4,

    After much faffing about and wondering why your code wasn't working, I've just clocked on to the fact that the first cell I want to paste data into is actually the first cell in a Table. I didn't design the Report spreadsheet so didn't pick up on this little nugget of information sooner.

    Anyhow, I've found that the Table has been labelled as 'Table_FANG_QMS_StatsAggregator_PracticeGroup9'.

    What amendment to your coding is required for this? I've looked about on the forum but I can't quite figure out how to add it in.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA: Copy rows from one workbook to specific rows in another

    I don't work much with tables.
    Is it a blank table?
    Do you need to first insert blank rows into the table before pasting data into it (otherwise, wouldn't you be posting data after the end of the table, outside of it)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Nov 2013
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy rows from one workbook to specific rows in another

    Where I need to paste the data will be blank, there are some columns containing formulae but they won't be touched.

    The table has enough rows in it that there won't be a need to insert any, the first empty cell in the table is C7, Row 6 contains the headers.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA: Copy rows from one workbook to specific rows in another

    I found a few old links which discuss how to locate the last used row in a table (which is a little different than finding last used row in a column).
    https://www.mrexcel.com/forum/excel-...row-table.html
    https://www.mrexcel.com/forum/excel-...row-table.html
    https://www.mrexcel.com/forum/excel-...ble-excel.html
    Last edited by Joe4; Oct 11th, 2019 at 10:11 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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