CODEWITHSUNDEEP
excelvba
Zeek77
Zeek77

Reputation: 3

How To Copy Range of Last Row of Data and Paste into Row below It

I am trying to to create a macro to get the last row of data on my sheet & copy/paste it into the row before it. I need it to pick up the data in Columns B-N. I am able to do it for just column B using the below code but i cant figure out the syntax to get it do do it for column B-N - can someone help?

Sub copylastrow()

Worksheets("Sheet1").Activate

Range("B" & Rows.Count).End(xlUp).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial

End Sub

Upvotes: 0

Views: 5138

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Some comments in the code:

  • Define the source sheet (no need to activate it)
  • Find the last row in an specific column
  • Set the source range according to columns and last row
  • Transfer values without copying them is faster

Assumptions:

  • Column B is the reference to get the last row
  • You're just pasting values

Read the comments and adjust the code to fit your needs.

Code

Public Sub CopyLastRow()
    
    ' Define the source sheet (no need to activate it)
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row in an specific column
    Dim lastRow As Long
    lastRow = sourceSheet.Range("B" & sourceSheet.Rows.Count).End(xlUp).Row
    
    ' Set the source range according to columns and last row
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("B" & lastRow & ":N" & lastRow)
    
    ' Transfer values without copying them is faster
    sourceRange.Offset(1).Value = sourceRange.Value

End Sub

Let me know if it works

Upvotes: 1

Related Questions