Excel VBA to copy, find last empty row, paste

[techgirl]

I need the vba code to copy multiple worksheets into another worksheet (Main) without overwriting the first copy/paste.

I have this started, it works, to copy from sheet 1 into MAIN. But I need to find the last empty cell in "Main"; then go to sheet2, sheet3, sheet4, copy without having to change the code each time.
worksheets("Sheet1").Range("A2:F50").Copy Worksheets("Main").Range("A2")

I have this for the Find last empty cell; but don't know how to place it inside of the first code.
Sub findnextblankrow()
Range("A2").End(xlDown).Offset(1, 0).Select

End Sub

 

[jondavis1987]

Is the last empty cell in a specific row or column each time?

 

[jondavis1987]

This is the code i use to copy something and paste in the row after the last one that is filled. The value of 12 is not something you'd probably want. I have one cell value pasting into 12 different locations

Sub Open_Workbook()


    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
    
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook


'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Misc\Yearly HMA Charts.xlsx"
    Set destWB = ActiveWorkbook
    


'   Find last row of Sieve data in destination workbook
    lastRow = destWB.Sheets("Sieves").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy Sieve data from source workbook to destination workbook
    srcWB.Sheets("A").Range("G20").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).Resize(12, 1).PasteSpecial xlPasteValues

 

[DanteAmor]

Try this

Sub Copy_data()
    
    Worksheets("Sheet1").Range("A2:F50").Copy Worksheets("Main").Range("A" & Rows.Count).End(xlUp)(2)
    Worksheets("Sheet2").Range("A2:F50").Copy Worksheets("Main").Range("A" & Rows.Count).End(xlUp)(2)
    Worksheets("Sheet3").Range("A2:F50").Copy Worksheets("Main").Range("A" & Rows.Count).End(xlUp)(2)


End Sub

Note:
The above is possible if in column A of each sheet you always have data.

 

[techgirl]

That worked. Thank you. Can you explain the last part..."& Rows.Count).end(xlUP) (2)?

 

[DanteAmor]

That worked. Thank you. Can you explain the last part..."& Rows.Count).end(xlUP) (2)?

I explain it with an example:

The following selects the last cell with data

Worksheets("Main").Range("A" & Rows.Count).End(xlUp).Select

The same does the following

Worksheets("Main").Range("A" & Rows.Count).End(xlUp)(1).Select

Then to find out which is the next empty cell after the last cell with data:

Worksheets("Main").Range("A" & Rows.Count).End(xlUp)(2).Select

Then to paste in the next cell:

[COLOR=#333333]Worksheets("Sheet1").Range("A2:F50").Copy [/COLOR][COLOR=#0000ff]Worksheets("Main").Range("A" & Rows.Count).End(xlUp)[/COLOR][COLOR=#ff0000](2)[/COLOR][COLOR=#0000ff][/COLOR][COLOR=#333333][/COLOR]