CODEWITHSUNDEEP
excelrowcopy-pastevba
Stevincent Rian Moseley
Stevincent Rian Moseley

Reputation: 3

VBA Paste Range Values into last row

What I am trying to do is copy the values in E17:BK17 into the last row. Right now with what I have all it copies is the Value of E17 into the last row, but not the remaining columns data into the last row. Any help would be appreciated since I don't really know what I'm doing.

Sub Mercy_CopyPaste_Row()
'
' CopyPaste Macro
'
'
Dim targetRng As Excel.Range
Dim destRng As Excel.Range
Set targetRng = Range("$E$17:$BK$17")
Set destRng = Excel.Range("E" & Rows.Count).End(xlUp).Offset(1, 0)

destRng.Value = targetRng.Value

End Sub

Upvotes: 0

Views: 1301

Answers (2)

Shai Rado
Shai Rado

Reputation: 33662

Your Set targetRng = Range("$E$17:$BK$17") is 59 columns, while destRng = Excel.Range("E" & Rows.Count).End(xlUp).Offset(1, 0) is a single column (actually it's a single cell). That's the reason you are only pasting a single cell "E17".

In order for your destRng to be the same size of targetRng (same number of columns), you need to Resize the range to targetRng.Columns.Count.

Try the code below:

Set destRng = Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, targetRng.Columns.Count)
destRng.Value = targetRng.Value

Upvotes: 1

Joe W
Joe W

Reputation: 111

This may do what you're looking for

Sub Mercy_CopyPaste_Row()

 Dim lRow As Long
 Dim ACell As String
 Dim Col As String
 Dim targetRng As Excel.Range
 Set targetRng = Range("$E$17:$BK$17")
 lRow = WorksheetFunction.Max(Range("E65536").End(xlUp).Row)
 ACell = Range("E17").Address(RowAbsolute:=False, ColumnAbsolute:=False)

 Range("$E$17:$BK$17").AutoFill Range(ACell & ":" & "BK" & lRow)

 End Sub

Upvotes: 0

Related Questions