roxnoxsox
09-03-2015, 06:21 AM
I'm creating a macro which will paste the contents of A3-P3 and all data below, into a new spreadsheet. I then need to do this for 2 other spreadsheets so that they're all pasted into the same thing, but I can't work out the formula which will insert my second and then third lot of data in the next blank row? So that it won't paste over the top. My data will vary in length each day? Does anyone have any suggestions? See below, what I have so far:
ChDir "C:\Testing"
Workbooks.Open Filename:= _
"C:\Testing\Duplicator Remover.xlsm"
Windows("Data.xlsx").Activate
Sheets("Dataset1").Select
Range("A3", Range("P3").End(xlDown)).Select
Selection.Copy
Windows("Duplicator Remover.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("Data.xlsx").Activate
Sheets("Dataset2").Select
So I want 'Dataset2' to paste underneath what I already have in the Duplicator Remover from dataset1?
EDIT: I found the solution myself online. For anyone else referencing:
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows +1).select
^ This looks for the next blank cell in column B. Found here: http://ccm.net/faq/14869-excel-a-macro-to-copy-and-paste-data-in-next-blank-cell
ChDir "C:\Testing"
Workbooks.Open Filename:= _
"C:\Testing\Duplicator Remover.xlsm"
Windows("Data.xlsx").Activate
Sheets("Dataset1").Select
Range("A3", Range("P3").End(xlDown)).Select
Selection.Copy
Windows("Duplicator Remover.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("Data.xlsx").Activate
Sheets("Dataset2").Select
So I want 'Dataset2' to paste underneath what I already have in the Duplicator Remover from dataset1?
EDIT: I found the solution myself online. For anyone else referencing:
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows +1).select
^ This looks for the next blank cell in column B. Found here: http://ccm.net/faq/14869-excel-a-macro-to-copy-and-paste-data-in-next-blank-cell