PDA

View Full Version : [SOLVED] Macro to paste data in next blank cell?



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