PasteSpecial method of Range class failed

[Dobin1s]

Hi im trying to run a bit of code thats always worked fine before and has not been recently changed however now im getting this error "PasteSpecial method of Range class failed" when getting to the line in red below

Selection.EntireRow.Hidden = False 'unhidden
Range("A" & MOVEROW).EntireRow.Select 'select whole row to move
Range("A" & MOVEROW).EntireRow.Copy 'copy whole row to move
Sheets(3).Select
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData 'show all
Range("A65536").End(xlUp).Offset(1, 0).Select 'goes to bottom of spreadsheet and then shift end up
If ActiveCell.Row = 2 Then Selection.Offset(1, 0).Select 'stop info going in row 2
TOLINE = ActiveCell.Row 'defines line pasting info to
Range("A" & TOLINE).Select 'selects bottom row on TO sheet
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats

Im a little confused because the code hasnt been changed since it was last run so im not sure if there is anything wrong at all...


Thanks in advance

 

[Joe4]

Welcome to the Board!

Do you have any merged, protected, or hidden cells in column A?

 

[Caleeco]

Try this alternate code. I have not created Sample data to test it, so please try it on a COPY of your spreadsheet.

Sub PSpecial()

Selection.EntireRow.Hidden = False 'unhidden
Range("A" & MOVEROW).EntireRow.Copy 'copy whole row to move

If Sheets(3).FilterMode Then
    Sheets(3).ShowAllData 'show all
End If

Sheets(3).Range("A65536").End(xlUp).Offset(1, 0).Select 'goes to bottom of spreadsheet and then shift end up

If ActiveCell.Row <= 2 Then
    TOLINE = 3
Else
    TOLINE = ActiveCell.Row
End If

Sheets(3).Range("A" & TOLINE).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 
End Sub

Regards
Caleeco

 

[Dobin1s]

I tried the below but it still gave me the same error for some reason

Try this alternate code. I have not created Sample data to test it, so please try it on a COPY of your spreadsheet.

Sub PSpecial()

Selection.EntireRow.Hidden = False 'unhidden
Range("A" & MOVEROW).EntireRow.Copy 'copy whole row to move

If Sheets(3).FilterMode Then
    Sheets(3).ShowAllData 'show all
End If

Sheets(3).Range("A65536").End(xlUp).Offset(1, 0).Select 'goes to bottom of spreadsheet and then shift end up

If ActiveCell.Row <= 2 Then
    TOLINE = 3
Else
    TOLINE = ActiveCell.Row
End If

Sheets(3).Range("A" & TOLINE).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 
End Sub

Regards
Caleeco

 

[Dobin1s]

No nothing hidden merged or protected...this spreadsheet just hates me

 

[Caleeco]

Hello,

Does it fail on the same line?

Sheets(3).Range("A" & TOLINE).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

 

[Joe4]

Try using the F8 to step through the code line-by-line to see what happens.
What is the value of TOLINE just before the error?

 

[Dobin1s]

No it was failing (well highlighting) on


Sheets(3).Range("A65536").End(xlUp).Offset(1, 0).Select

 

[Dobin1s]

Sorry didnt meant to put that line twice

 

[Dobin1s]

Ive tried stepping through and im none the wiser (im quite new to this and only self taught really)

The value of TOLINE is a row on another tab where i want the info pasted to...when i hover over it as its running it is showing the correct row number