Why does this macro fail (1004)

[Rick S.]

When I open the workbook the macro runs fine, the next time I run the macro
it fails with "'Runtime error 1004. Paste method of worksheet class failed."
I have to manually delete the data from sheet "Print Data", save the excel
file and then I can run the macro.
I should be able to run this repeatedly. Open Userform and run, open
userform and run, etc...

'======
Private Sub CommandButton1_Click()
If UserPart.Value = "" Then
MsgBox "You must enter a Value in " & """Part Number""" & " text box!"
End If
If IsDate(UserDate.Value) = False Then
MsgBox "You must enter a valid date in " & """Job Due By""" & " text box in
a Date format (mm/dd/yy)!"
End If
With Sheets("Part Number")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A2:A" & Sh1LastRow)
End With
'MsgBox Sh1LastRow 'for testing
For Each Sh1Cell In Sh1Range
If Sh1Cell.Value = sUserPart Then
x1 = Replace(Sh1Cell.Address, "$", "")
x2 = Replace(x1, "A", "")
sRowData = x1 & ":H" & x2
'MsgBox sRowData 'for testing
Range(sRowData).Select
Selection.Copy
Sheets("Print Data").Select
ActiveSheet.Unprotect "2000"
Range("A2").Select
ActiveSheet.Paste 'Paste method of worksheet class failed (1004) on
second pass?
Columns("A:H").Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveSheet.Protect "2000"
End If
Next Sh1Cell
End Sub
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Rick S.]

I think I know why, but I do not know why it happens.
When I rerun th emacro I think the sheet "unprotect" is failing.
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Jim Thomlinson]

Try this... Keeping in mind that I can not test it since you are using global
variables and are not using option explict to require variable declarations...

'MsgBox sRowData 'for testing
with Sheets("Print Data")
..Unprotect "2000"
Range(sRowData).Copy Destination:= .Range("A2")
..Columns("A:H").Entirecolumn.AutoFit
..Unprotect "2000"
end with
End If
Next Sh1Cell
End Sub

 

[Rick S.]

I have been schooled before on using "Option Explicit", it is safe to assume
it is in use.

Thanks for your help!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Rick S.]

For those who wish to see the changed code.
'======
Sheets("Print Data").Unprotect "2000"
Range(sRowData).Copy Destination:=Sheets("Print Data").Range("A2")
Sheets("Print Data").Select
Columns("A:H").Select
Selection.Columns.AutoFit
Range("A2").Select
Sheets("Print Data").Protect "2000"
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007