Macro that pastes at variable row

[CBurrows]

Hey Forum,

So I have two worksheets that I need a macro to copy all contents from both worksheet and paste it to one "Aggregate" worksheet.
The kicker is that Worksheet1 has to go first, and the number or rows with data varies each time the data is collected.
So how do I create a macro that will paste the data from Worksheet2 the row below the last row in worksheet1.

Example:
Worksheet 1 has 'x' rows with contents, the first row from Worksheet2 needs to be paisted at row:'x+1'

Thanks for anyhelp you can give!

 

[HalfAce]

Hi and welcome to the board.
There is more than one way to determine the next 'available' row in your sheet.
The first example finds the next row, based on the length of data in column A. (This can be done using any column you like by changing the "A" to the column of choice.)

Sub FindNextRowInColumnA()
Dim LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

MsgBox "The next available row in column A is row " & LstRw + 1

End Sub

This second example will find the first completely blank row. As in you have some columns with more data in them than other columns and you don't necessarily know which column is longest.

Sub FindNextAvailableBlankRow()
Dim LstRw As Long
LstRw = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

MsgBox "The first completely blank row is row " & LstRw + 1

End Sub

There are of course more ways than this, but using either of these should do what you've asked.

Hope it helps.

 

[patel45]

sub a()
LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(2).UsedRange.copy Sheets(1).cells(LastRow + 1,1)
end sub