Copy Paste to last row

[thedeadzeds]

Sorry to post this, I have searched on this site but cant' find the easiest solution.

I just want to copy all data from the Master sheet columns A to AL based on the last row being AL of the Master sheet (excluding the headers) to the last row of the All Data sheet based on the last row being AL of the All Data Sheet.

Thanks

 

[Wookiee]

How's this work?

Sub CopyOver()

Dim shtMaster As Worksheet
Dim shtAllData As Worksheet
Dim lngLastMasterRow As Long
Dim lngLastADRow As Long
Dim rngPaste As Range

With shtAllData

  lngLastADRow = .Range("A1").End(xlDown).Row
  Set rngPaste = .Range("A" & lngLastADRow)

End Sub

With shtMaster

  lngLastMasterRow = .Range("A1").End(xlDown).Row
  .Range("A2:AL" & lngLastMasterRow).Copy rngPaste

End With

End With

 

[Wookiee]

How's this work?

I posted that too hastily. Here's a better version:

Sub CopyOver()

Dim shtMaster As Worksheet
Dim shtAllData As Worksheet
Dim lngLastMasterRow As Long
Dim lngLastADRow As Long
Dim rngPaste As Range

Set shtMaster = ThisWorkbook.Sheets("Master")
Set shtAllData = ThisWorkbook.Sheets("All Data")

With shtAllData

  lngLastADRow = .Range("A1").End(xlDown).Row + 1
  Set rngPaste = .Range("A" & lngLastADRow)

End Sub

With shtMaster

  lngLastMasterRow = .Range("A1").End(xlDown).Row
  .Range("A2:AL" & lngLastMasterRow).Copy rngPaste

End With

Set shtMaster = Nothing
Set shtAllData = Nothing

End Sub

 

[thedeadzeds]

Thanks for this but it doesn't seem to be doing anything? I've changed end sub in the middle of the code to end with but I cant' see that this actually copying and pasting the data when I run it.

 

[Fluff]

How about

Sub thedeadzeds()
   With Sheets("Master")
      .Range("A2:AL" & .Range("AL" & Rows.Count).End(xlUp).Row).Copy Sheets("All Data").Range("AL" & Rows.Count).End(xlUp).Offset(1, -37)
   End With
End Sub

 

[thedeadzeds]

Thank you fluff this works perfectly. Just out of interest what does -37 do and also I have managed to write some code below with works but doesnt find the last row from the Master sheet correctly. It seems to select more than the last row even thought there is no data below that.

Thanks

Sub Copy()

Dim LR1 As Long
Dim LR2 As Long

LR1 = Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Cells(Rows.Count, 1).End(xlUp).Row


Sheets("Master").Select
range("A2:AL" & LR1).Copy

Sheets("All Data").Select
range("A" & LR2 + 1).PasteSpecial


End Sub

 

[Fluff]

Te -37 is offsetting the range by 37 columns to the left taking it back to column A