Destination:=.Cells(.Rows.Count, "A").End(xlUp).Row + 1

[J.W. Aldridge]

Need to duplicate code but change destination.


' THIS WORKS
With Worksheets("Ind Templates")
.Range("A1:f13").Copy _
Destination:=Sh.Range("a1")

End With

I need destination to be the row below the last row used in A.
I tried this, but didnt work.
Destination:=.Cells(.Rows.Count, "A").End(xlUp).Row + 1

 

[Jacob Skaria]

Try this

Dim lngLastRow As Long

With Worksheets("Ind Templates")
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
..Range("A1:f13").Copy Destination:=.Range("a" & lngLastRow)
End With


If this post helps click Yes

 

[J.W. Aldridge]

Mr. Jacob,

unfortunately, this didn't work.
Let me give you the full code that i am using...


Private Sub Workbook_newsheet(ByVal Sh As Object)
Dim LastRow As Long

With Worksheets("Ind Templates")
.Range("A1:f13").Copy _
Destination:=Sh.Range("a1")

With Worksheets("Ind Templates")
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
..Range("A39:f50").Copy Destination:=.Range("a" & lngLastRow)

End With

End With

End Sub

 

[Rick Rothstein]

I think your code is mixing sheet references. See if this does what you
want...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim lngLastRow As Long
With Worksheets("Ind Templates")
.Range("A1:F13").Copy Destination:=Sh.Range("a1")
lngLastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row + 1
.Range("A39:F50").Copy Destination:=Sh.Range("A" & lngLastRow)
End With
End Sub

 

[OssieMac]

Just another option and in explanation to the OP. If using With then all
references preceded by the dot refer to the With. If referring to another
sheet for the Destination then its specific reference must be included.

With Worksheets("Ind Templates")
.Range("A1:F13").Copy _
Destination:= _
sh.Cells(sh.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

 

[Rick Rothstein]

Good point about using the cell reference from the last cell calculation
directly. However, I think there are two separate range copies being
performed with the second one dependent on the first. I think this is what
it should be...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Worksheets("Ind Templates")
.Range("A1:F13").Copy Destination:=Sh.Range("A1")
.Range("A39:F50").Copy Destination:=Sh.Cells( _
Sh.Rows.Count, "A").End(xlUp).Offset(1)
End With
End Sub