Macro to Copy/Paste Data

Question

Here is the code I have. I am trying to find the last populated row in a range. Then copy that data as formulas to the next blank row in the range. Finally copy the last populated row again and paste it to the second to last populated row as values. In a nutshell, move the formulas in the last row/cells down one row and retain that data where it was copied as numbers. So when new data is brought into the file, the formulas recalculate and I do not lose the previous calculated data. Hopefully that makes sense. The below code runs but does not appear to do anything other than to paste the data as values in the existing rows. What am I doing wrong?

Sub Data Copy()

Dim myLastCell As Range
Set myLastCell = LastCell(Worksheets("YTD Metric Calculator").Range("E7:J76"))

Sheets("YTD Metric Calculator").Range("E7:J" & myLastCell.Row).Copy
Sheets("YTD Metric Calculator").Range("E7:J" & myLastCell.Row + 1).PasteSpecial Paste:=xlPasteFormulas
Sheets("YTD Metric Calculator").Range("E7:J" & myLastCell.Row).Copy
Sheets("YTD Metric Calculator").Range("E7:J" & myLastCell.Row + 1).PasteSpecial Paste:=xlPasteValues
'Retain Yesterday's YTD Metric Data

Function LastCell(r As Range) As Range

Dim LastRow&, lastCol%

On Error Resume Next

With r

LastRow& = .Cells.Find(What:="*", _
  SearchDirection:=xlPrevious, _
  SearchOrder:=xlByRows).Row

lastCol% = .Cells.Find(What:="*", _
  SearchDirection:=xlPrevious, _
  SearchOrder:=xlByColumns).Column

End With

Set LastCell = r.Cells(LastRow&, lastCol%)

End Function

VBA Goal

@DavidG. If you are referring to the & and %, they are type identifiers - & is Long, % is Integer. Hangover from the really, really, old days when As Integer etc didn't exist. — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-11-13T21:26:02

Your LastCell function isn't doing what you think it is doing, because it is determining the row and column within the worksheet of the last used cell in the range, and then using that as an offset from the start of the range.

So if the last used row in E7:J76 was row 10, and the last used column was column I, the cell being returned from your function is cell M16 (i.e. 9 rows below E7, and 8 columns to the right).

Change

Set LastCell = r.Cells(LastRow&, lastCol%)

to be

Set LastCell = r.Cells(LastRow& - r.Row + 1, lastCol% - r.Column + 1)

or to

Set LastCell = r.Worksheet.Cells(LastRow&, lastCol%)

The parts where you are copying the formulas, and then replacing with values, should be something like:

Sheets("YTD Metric Calculator").Range("E" & myLastCell.Row).Resize(1, 6).Copy
Sheets("YTD Metric Calculator").Range("E" & myLastCell.Row + 1).Resize(1, 6).PasteSpecial Paste:=xlPasteFormulas
Sheets("YTD Metric Calculator").Range("E" & myLastCell.Row).Resize(1, 6).Value = _
          Sheets("YTD Metric Calculator").Range("E" & myLastCell.Row).Resize(1, 6).Value

That can be rewritten using a With block to save a few keystrokes:

With Sheets("YTD Metric Calculator").Range("E" & myLastCell.Row).Resize(1, 6)
    .Copy
    .Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
    .Value = .Value
End With

Macro to Copy/Paste Data

1 Answers