Copy Used Range to first blank row on another sheet in same workbook

[blbat]

I'm getting a: "Method 'Range' of object_'Worksheet' failed" error message on the following code. it works up to the .Copy portion but I'm definitely doing something wrong with the Destination. Any help would be appreciated!

Private Sub CommandButton1_Click()

Dim mySheetW As Worksheet, mySheetT As Worksheet, LastRow As Long, LastCol As Long, LastRowT As Long, LastColT As Long

Set mySheetT = ActiveWorkbook.Worksheets("working")                              'Destination Sheet
Set mySheetW = ActiveWorkbook.ActiveSheet                                             'Macro called from this Sheet
LastRow = mySheetW.Cells(Rows.Count, 1).End(xlUp).Row                           'Last Row of Active Sheet
LastCol = mySheetW.Cells(1, Columns.Count).End(xlToLeft).Column              'Last Column of Active Sheet
LastRowT = mySheetT.Cells(Rows.Count, 1).End(xlUp).Row                          'Last Row of Destination Sheet
LastColT = mySheetT.Cells(1, Columns.Count).End(xlToLeft).Column             'Last Column of Destination Sheet


mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Copy Destination:=mySheetT.Range(LastRowT, LastColT).Paste

'No Cigar!

 

[Fluff]

Simply remove the .Paste from the end of the line

 

[blbat]

Thx for reply Fluff....removed the .Paste and same error persists.

 

[blbat]

Fluff- (or anyone)
if you're still willing to help on this, I just got back to working on this problem and am having difficulty.
Still receiving Run-time error '1004'
Method 'Range' of object '_WorkSheet' failed

The message box correctly reports the destination sheet last row, but it fail when I execute the rest of the code.

what I am doing wrong here?

Private Sub CommandButton1_Click()

Dim mySheetW As Worksheet, mySheetT As Worksheet, LastRow As Long, LastCol As Long, LastRowT As Long, LastColT As Long

Set mySheetT = ActiveWorkbook.Worksheets("working") 'Destination Sheet
Set mySheetW = ActiveWorkbook.ActiveSheet 'Macro called from this Sheet
LastRow = mySheetW.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row of Active Sheet
LastCol = mySheetW.Cells(1, Columns.Count).End(xlToLeft).Column 'Last Column of Active Sheet
LastRowT = mySheetT.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row of Destination Sheet
LastColT = mySheetT.Cells(1, Columns.Count).End(xlToLeft).Column 'Last Column of Destination Sheet

'Next is select working Range WITHOUT Title Row!
'****************************
'mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Select


MsgBox (LastRowT) 'reports correct Last Row of Destination Sheet

'mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Copy Destination:=mySheetT.Range(LastRowT, LastColT)

mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Copy Destination:=mySheetT.Range(LastRowT + 1, LastColT)
'+1 intended to make destination row one down from last used Row

'Still No Cigar!

End Sub

 

[Fluff]

It should be

mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Copy Destination:=mySheetT.[COLOR=#ff0000]Cells[/COLOR](LastRowT + 1, LastColT)

But do you really want to paste the used range into sheetT starting at the last used column, or starting in col A?

 

[blbat]

Thanks...that stopped the error, in fact there is no error at all, but the data is not getting copied into the destination sheet.

I don't need to have some sort of "Paste" method in there?

 

[Fluff]

Could you answer my question in post#5?

 

[blbat]

****! sorry Fluff, I totally missed your question and it's an extremely good one!! LOL, it was indeed pasting it after the last column. I meant for it to paste starting in column A

Thanks so much for all your patience.
this works:

mySheetW.UsedRange.Offset(1).Resize(LastRow - 1, LastCol - 1).Copy Destination:=mySheetT.Cells(LastRowT + 1, LastColT - 35)

 

[Fluff]

Glad to help & thanks for the feedback.

I would recommend changing it to

mySheetT.Cells(LastRowT + 1, 1)

 

[blbat]

will do. that's a much cleaner way to do it.