Hi all,
I'm looking for some help with this macro...it has me really confused
because it was working earlier, but now it hangs on this line of code (it is
asterisked below)

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

This macro runs when the workbook opens and copies the contents of one
sheet and pastes only the values to another sheet. The range that is being
copied from and pasted to are both the same size. I have also tried this by
selecting the upper-leftmost cell in the area I want to paste to but it has
the same effect. Please help!

The exact error message is;
Runtime Error '1004': PasteSpecial method Range of class failed

__________________________________________________ ______________
Private Sub Workbook_Open()
Sheets("Permit Records").Select
Range("A5").Select
ActiveWindow.ScrollRow = 493
Range("A5:M504").Select
ActiveWindow.ScrollRow = 6
Selection.Copy
Range("A1").Select
Sheets("Final Inspection").Select
ActiveSheet.Unprotect Password:="XXXX"
Range("A5").Select
ActiveWindow.ScrollRow = 492
Range("A5:M504").Select
ActiveWindow.ScrollRow = 5
***Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
ActiveSheet.Protect Password:="XXXX", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Permit Records").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

You do a lot of selecting in your code. For the most part, you don't have to
select something to work with it.

Option Explicit
Private Sub Workbook_Open()

Worksheets("final inspection").Unprotect Password:="XXXX"

Worksheets("Permit Records").Range("A5:M504").Copy

With Worksheets("final inspection")
.Range("A5").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect Password:="XXXX", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.Goto .Range("a1"), Scroll:=True
End With

With Worksheets("Permit Records")
Application.Goto .Range("a1"), Scroll:=True
End With
Application.CutCopyMode = False

End Sub

(Did I protect/unprotect the correct worksheet? I got kind of confused.)

setoFairfax wrote:

Hi all,
I'm looking for some help with this macro...it has me really confused
because it was working earlier, but now it hangs on this line of code (it is
asterisked below)

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

This macro runs when the workbook opens and copies the contents of one
sheet and pastes only the values to another sheet. The range that is being
copied from and pasted to are both the same size. I have also tried this by
selecting the upper-leftmost cell in the area I want to paste to but it has
the same effect. Please help!

The exact error message is;
Runtime Error '1004': PasteSpecial method Range of class failed

__________________________________________________ ______________
Private Sub Workbook_Open()
Sheets("Permit Records").Select
Range("A5").Select
ActiveWindow.ScrollRow = 493
Range("A5:M504").Select
ActiveWindow.ScrollRow = 6
Selection.Copy
Range("A1").Select
Sheets("Final Inspection").Select
ActiveSheet.Unprotect Password:="XXXX"
Range("A5").Select
ActiveWindow.ScrollRow = 492
Range("A5:M504").Select
ActiveWindow.ScrollRow = 5
***Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
ActiveSheet.Protect Password:="XXXX", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Permit Records").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

--

Dave Peterson

Dave, thank you so much - it works perfectly now! I'm going to try this
method with my other macros - it looks much more efficient than what I had
before.

"Dave Peterson" wrote:

You do a lot of selecting in your code. For the most part, you don't have to
select something to work with it.

Option Explicit
Private Sub Workbook_Open()

Worksheets("final inspection").Unprotect Password:="XXXX"

Worksheets("Permit Records").Range("A5:M504").Copy

With Worksheets("final inspection")
.Range("A5").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect Password:="XXXX", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Application.Goto .Range("a1"), Scroll:=True
End With

With Worksheets("Permit Records")
Application.Goto .Range("a1"), Scroll:=True
End With
Application.CutCopyMode = False

End Sub

(Did I protect/unprotect the correct worksheet? I got kind of confused.)

setoFairfax wrote:

Hi all,
I'm looking for some help with this macro...it has me really confused
because it was working earlier, but now it hangs on this line of code (it is
asterisked below)

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

This macro runs when the workbook opens and copies the contents of one
sheet and pastes only the values to another sheet. The range that is being
copied from and pasted to are both the same size. I have also tried this by
selecting the upper-leftmost cell in the area I want to paste to but it has
the same effect. Please help!

The exact error message is;
Runtime Error '1004': PasteSpecial method Range of class failed

__________________________________________________ ______________
Private Sub Workbook_Open()
Sheets("Permit Records").Select
Range("A5").Select
ActiveWindow.ScrollRow = 493
Range("A5:M504").Select
ActiveWindow.ScrollRow = 6
Selection.Copy
Range("A1").Select
Sheets("Final Inspection").Select
ActiveSheet.Unprotect Password:="XXXX"
Range("A5").Select
ActiveWindow.ScrollRow = 492
Range("A5:M504").Select
ActiveWindow.ScrollRow = 5
***Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
ActiveSheet.Protect Password:="XXXX", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Permit Records").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

--

Dave Peterson