Copy range until empty row and paste into new sheet

Question

I'm trying to write a VBA macro for Excel 2013. It's purpose is to merge two worksheets into a combined worksheet. (I tried to find a built in feature to do this but was unable to find what I needed).

What the macro needs to do is this:

1. Activate "Sheet3" and clear all rows starting with row 3 and down

2. Go into "Sheet1" and copy all rows starting with (A3:P3) and copy down until, and not including, the first row with null in the A column.

3. Go into "Sheet3" and paste those rows starting at A3.

4. Go into "Sheet2" and copy all rows starting with (A3:P3) and copy down until, and not including, the first row with null in the A column.

5. Go into "Sheet3" and past those rows starting at the first empty cell in column A.

I'm a novice at VBA but I've managed to find the following code and I'm trying to make it work to accomplish the above requirements.

Sub CreateCombinedSheet()
  lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
  ActiveSheet.Range("A3", ActiveSheet.Cells(lastRow, 12)).Copy
End Sub

I'm trying to write parts of it and test it as I go but I'm already getting a 1004 error with this:

Application-defined or object-defined error

Any thoughts on how I should work this?

Thanks

Answer 1

Activate "Sheet3" and clear all rows starting with row 3 and down

You do not need to activate a sheet to clear the rows. You may want to see THIS You can directly say

Sheets("BlahBlah").Rows("3:200").ClearContents

I have hardcoded 200 as an example. To find the end of rows, see the below point.

Go into "Sheet1" and copy all rows starting with (A3:P3) and copy down until, and not including, the first row with null in the A column.

Same for this. You do not need to go to that sheet. You need to first find the last row. I see that you are hardcoing the rows in your code. You don't need to do that. xl2007+ now has 1048576 rows. Use .Rows.Count Please see THIS

Go into "Sheet3" and paste those rows starting at A3.

To paste, you again don't need to go to that sheet. You can directly say

rng.Copy Sheet("BlahBlah").Rows(3)

Your point 4 and 5 are just variations of the above. I am sure you can now take it from here :). In case you still face any difficulty, simply post back.