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

[ParaSitius]

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.

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.

 

[Joe4]

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:

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:

Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select

 

[ParaSitius]

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.

 

[Joe4]

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)?

 

[ParaSitius]

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.

 

[Joe4]

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-questions/1007660-vba-get-last-used-row-table.html
https://www.mrexcel.com/forum/excel-questions/1021090-first-empty-row-table.html
https://www.mrexcel.com/forum/excel-questions/1076238-vba-find-last-row-table-excel.html