Run Time Error 1004 Copy and paste macro

[Mykiej23]

Hi,

I've got a macro that we use on multiple spreadsheets that works without an issue. I've tried to use the same macro for a new spreadsheet I've created which works on the same basis but I keep getting run time error 1004.

Sub PrepareReport()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationAutomatic
    UseDate = Range("A2").Value
    
    Response = MsgBox("Input report for business date - " & UseDate & vbNewLine & vbNewLine & "NB: This should be the day AFTER the last populated date.", vbYesNoCancel + vbQuestion, "Prepare Report")
        
    If Response = 2 Then
    
    
        End
    
    ElseIf Response = 7 Then
    
        NewDate = InputBox("Enter date to use...(dd/mm/yy)", "Custom Date")
            
            If IsDate(NewDate) = False Then
                Do Until IsDate(NewDate) = True
                    NewDate = InputBox("The date you entered has not been recognised - please try again.....or enter 'abort' to quit.", "Try Again")
                        If UCase(NewDate) = "ABORT" Then End
                Loop
            End If
        
        UseDate = CDate(NewDate)
    
    End If
    
        Call CheckDate(UseDate)
    
    OldDate = UseDate - 1
    NewDate = UseDate
    OldDateRow = Application.Match(CLng(OldDate), Range("A1:A50000"), 0)
    
    If Left(Range("C" & OldDateRow).Formula, 6) <> "=SUMIF" Then
    
        MsgBox "The date used should be the day AFTER the last populated date." & vbNewLine & vbNewLine & "Please check and start again...", vbOKOnly & vbCritical, "Check Date"
        End
    
    End If
    
    Sheets("Tariff V G&R").Select
    OldDateRow = Application.Match(CLng(OldDate), Range("A1:A50000"), 0)
    NewDateRow = Application.Match(CLng(NewDate), Range("A1:A50000"), 0)
    LastColumn = Range("IV6").End(xlToLeft).Column
    Range(Cells(OldDateRow, 3), Cells(OldDateRow, LastColumn - 1)).Copy
    Range(Cells(NewDateRow, 3), Cells(NewDateRow, LastColumn - 1)).PasteSpecial xlPasteFormulas
    Range(Cells(OldDateRow, 3), Cells(OldDateRow, LastColumn - 1)).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("B" & OldDateRow).Select

The line that I get the error is at

Range(Cells(OldDateRow, 3), Cells(OldDateRow, LastColumn - 1)).Copy

Cell A2 has the formula =TODAY()-1 which is the same as other spreadsheets, we always work one day behind. I'm trying to start from 01/01/2020 but I'm tearing my hair out now trying to figure out why this wont work. Can anyone help? Much appreciated if anyone can give me some advice

Thanks,
Michaela

 

[offthelip]

Have a look with debug at what values you have got in olddaterow, and lastcolumn, I suspect these could be wrong