VBA help - identify last row and copy values, then id 2nd to last row and paste special

dhaynes

New Member
Joined
Mar 13, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello all...coming to the experts for help again. I am trying to write a macro to
1.) identify the last row in a sheet
2.) copy the data in that row
3.) Paste that date in the last blank row
4.) Copy the data that is now in the 2nd to last row and paste special values

The code i have now (below) isn't working but it is not properly identifying the last blank row or the second to last row with data. Any and all suggestions are appreciated:

Sheets("Historical Pricing Source Data").Select
Range("A2787").Select
Selection.End(xlDown).Select
Rows("2797:2797").Select
Selection.Copy
Rows("2798:2798").Select
ActiveSheet.Paste
Rows("2797:2797").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Sub t()
Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy Cells(Rows.Count, 1).End(xlUp)(2)
With Cells(Rows.Count, 1).End(xlUp).Offset(-1).EntireRow
    .Value = .Value
End With
End Sub

The statement below is misleading. The last blank row could be the bottom of the sheet.
Paste that date in the last blank row
 

dhaynes

New Member
Joined
Mar 13, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub t()
Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy Cells(Rows.Count, 1).End(xlUp)(2)
With Cells(Rows.Count, 1).End(xlUp).Offset(-1).EntireRow
    .Value = .Value
End With
End Sub

The statement below is misleading. The last blank row could be the bottom of the sheet.
Very helpful and you are absolutely correct. Thanks for pointing my misleading statement out.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
We are al guilty of getting so wrapped up in what we are trying to do, that we don't really read what we write as if we were on the other end. I'll admit that I have to read my stuff back a couple of time to make sure I said what I meant to say. When I don't, it is subject to error, especially with failing eyesight and mind.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,149
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top