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

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



"Rick S." wrote:

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

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

--
HTH...

Jim Thomlinson


"Rick S." wrote:

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



"Rick S." wrote:

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