Problem wih Code

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have the code below which simply just copies rows 18:21 to 9 ranges below
it. My sheet is protected with the word FIELD. Everytime I run the macro I
get an error "Paste Method Worksheet Class Failed", when I debug it
highlights the line "ActiveSheet.Paste" - the first instance of this line.

Can anyone assist?

Thanks



Sub CopyCells()

Sheets("Report").Select
ActiveSheet.Unprotect Password:="FIELD"

ClearCells

ActiveSheet.Unprotect Password:="FIELD"
Sheets("Report").Select
Rows("18:21").Select
Selection.Copy
Range("A23").Select
ActiveSheet.Paste
Range("A28").Select
ActiveSheet.Paste
Range("A33").Select
ActiveSheet.Paste
Range("A38").Select
ActiveSheet.Paste
Range("A43").Select
ActiveSheet.Paste
Range("A48").Select
ActiveSheet.Paste
Range("A53").Select
ActiveSheet.Paste
Range("A58").Select
ActiveSheet.Paste
Range("A63").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Sheets("Report").Select
ActiveSheet.Protect Password:="FIELD", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub
 
Hi John,

Try this:

Sub CopyCells()
Pass="FIELD"
With Sheets("Report")
.Unprotect Pass
ClearCells
.Rows("18:21").Copy .Range("A23,A28,A33,A38,A43,A48,A53,A58,A63")
Application.CutCopyMode = False
.Protect Pass
End With
End Sub

Regards,
KL
 
Another way:

Sub CopyCells()
Pass = "FIELD"
With Sheets("Report")
.Unprotect Pass
ClearCells
rng = ""
For i = 1 To 9
If rng <> "" Then rng = rng & ","
rng = rng & "A" & 18 + 5 * i
Next i
If rng <> "" Then .Rows("18:21").Copy .Range(rng)
Application.CutCopyMode = False
.Protect Pass
End With
End Sub

Regards,
KL
 
Fixed it KL, I had it been protected again within the sub "ClearCells" so I
just removed it within that code
 
John,

It works for me. Unless there is no macro called "ClearCells" in your
project, it must be the line wrapping. Try this (make sure you copy
everything as is):

Sub CopyCells()
Pass="FIELD"
With Sheets("Report")
.Unprotect Pass
ClearCells
.Rows("18:21").Copy _
.Range("A23,A28,A33,A38,A43,A48,A53,A58,A63")
Application.CutCopyMode = False
.Protect Pass
End With
End Sub

If this gives you an error try removing 'ClearCells'

Regards,
KL
 
Thanks for your help KL

KL said:
John,

It works for me. Unless there is no macro called "ClearCells" in your
project, it must be the line wrapping. Try this (make sure you copy
everything as is):

Sub CopyCells()
Pass="FIELD"
With Sheets("Report")
.Unprotect Pass
ClearCells
.Rows("18:21").Copy _
.Range("A23,A28,A33,A38,A43,A48,A53,A58,A63")
Application.CutCopyMode = False
.Protect Pass
End With
End Sub

If this gives you an error try removing 'ClearCells'

Regards,
KL
Click to expand...
 
Back
Top