Paste special error (method of range class failed)

[kezwick]

Hi All,

Having a head scratcher have the below macro and it works every other time! fill in the table press button and fails (debug points to Paste special line). after which i change nothing in the code and press button again runs fine! its almost like its losing the data from the clipbord? any help would be brilliant thanks again all!

Sub Macro1()
'
' Macro1 Macro
'
    Sheets("Input").Select
    lastCol = ActiveSheet.Range("a2").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a2", ActiveSheet.Cells(lastRow, lastCol)).Select
    Selection.Copy
    Sheets("Data").Select
    ActiveSheet.Unprotect
    Range("G1").Select
    Range("G1").End(xlDown).Select
    ActiveCell.Offset(1, -6).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Input").Select
    Range("A2:F20").Select
    Selection.ClearContents
    Range("h2:j20").Select
    Selection.ClearContents
    ActiveWorkbook.Save
'
End Sub

 

[saltkev]

Hi

Try this, looks like it was mostly to do with your sheet protection and the positioning of the protect & unprotect.

Sheets("Data").Unprotect
Sheets("Input").Activate
    lastCol = ActiveSheet.Range("a2").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a2", ActiveSheet.Cells(lastRow, lastCol)).Select
    Selection.Copy
    Sheets("Data").Activate
    
    ActiveSheet.Range("G1").Select
    ActiveSheet.Range("G1").End(xlDown).Select
    ActiveCell.Offset(1, -6).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Sheets("Input").Activate
    ActiveSheet.Range("A2:F20").Select
    Selection.ClearContents
    ActiveSheet.Range("h2:j20").Select
    Selection.ClearContents
    
    Sheets("Data").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    'ActiveWorkbook.Save

Kev

 

[Teeroy]

The macro recorder is a good way to learn VBA and can be useful in getting correct syntax even after you get more practice, but it records a lot of useless stuff as well.
You'll find as you get more expreience that it's better to be clearer exactly what objects you are dealing with.
This is a cleaner way of doing what your recorded code did.

Sub CleanerCode()
With Sheets("Input")
    Set Rng = Range(.Range("A2"), .UsedRange.Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count))
End With
With Sheets("Data")
    .Unprotect
    .Range("G1").End(xlDown).Offset(1, -6).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
Rng.ClearContents
End Sub

Hope this helps.

Teeroy.

 

[kezwick]

Brilliant that works Teeroy! many thanks for all the help still trying to get my head around it but will get it one day (he says!!!)
Thanks again to both for the help much appreciated!

 

[Teeroy]

You're welcome.

Just keep practising; that's what we all do .

 

[kezwick]

(Solved) Re: Paste special error (method of range class failed)

many thanks all