Run-time error '1004'

[Groves22]

Why am I getting that error? I get when the code tries to execute the bolded line.
TempR is the sheet showing on the screen when this code gets to that line... is that the issue, if so, how do I fix?

                TempR.Sheets(1).Range("E5", Range("E5").End(xlDown)).Copy
                
                k = 1
                Do Until ReportP.Sheets(1).Cells(k, 2) = ""
                    k = k + 1
                Loop
                
                ReportP.Sheets(1).Cells(k, 2).PasteSpecial xlPasteValues
                TempR.Sheets(1).Range("H5", Range("H5").End(xlDown)).Copy
                ReportP.Sheets(1).Cells(k, 3).PasteSpecial xlPasteValues
                ReportP.Sheets(1).Cells(k, 4).Formula = "=today()"
                
                w = 1
                Do Until ReportP.Sheets(1).Cells(w, 2) = ""
                    w = w + 1
                Loop
                
                ReportP.Sheets(1).Cells(k, 4).Copy
                [B]ReportP.Sheets(1).Range(Cells(k, 4), Cells(w - 1, 4)).PasteSpecial xlPasteValues[/B]
                
                ReportP.Save

 

[SkipVought]

Hi,

The Cells Property need a sheet reference...

    With ReportP.Sheets(1)
       .Cells(k, 4).Copy
       .Range([red][b].[/b][/red]Cells(k, 4), [red][b].[/b][/red]Cells(w - 1, 4)).PasteSpecial xlPasteValues
    End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I also wanted to point out that you don't need the loops to find the last cell...

    k = ReportP.Sheets(1).Cells(1, 2).End(xlDown).Row + 1

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Groves22]

Good to know!
Thanks Skip!

 

[Cordeyo]

Groves22

If w - 1 = 0 you will get the 1004 error.


HTH
Cordeyo

 

[SkipVought]

Cordeyo,

Given Groves22's code, how could variable w equal zero?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cordeyo]

Hi Skip

Just an observation that if the loop was not activated because ReportP.Sheets(1).Cells(w, 2) is empty when w = 1 then w will stay at 1, causing the code Cells(w - 1, 4) to translate to Cells(0 ,4). Which will generate the 1004 error. Help me if I'm wrong. I do not claim to be an expert.

Cordeyo