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
15
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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