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

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top