Run Time error 1004 Paste Method of Worksheet Class Failed
 

Every time I run the following code I recieve the error "Run Time error
1004 Paste Method of Worksheet Class Failed". Does anyone have any idea
why? It has worked for months and now it will not. HELP!!!!

Private Sub Workbook_Open()
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "American Red Ball International"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Monthly Agent Bookings - by agent"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Agent No."
Range("C5").Select
ActiveCell.FormulaR1C1 = "No. of"
Range("C6").Select
ActiveCell.FormulaR1C1 = "Registrations"
Rows("2:6").Select
Selection.Font.Bold = True
Range("A8:C8").Select
ActiveSheet.Paste
Rows("8:8").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
ActiveWorkbook.SaveAs ("\\Gateway1\E\Shared_Folders\Agent
Bookings\AgentBookings")
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Cells.PasteSpecial xlPasteAll
Range("A1").Select
Windows("AgentBookings.xls").Activate
ActiveWorkbook.Close False

End Sub

Thanks in advance for any assistance!
Regards,
K


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Run Time error 1004 Paste Method of Worksheet Class Failed
 

It will probably work again the next time you have data on your clipboard.

You are pasting without copying anything first.


"Ken Nunn" wrote:

Every time I run the following code I recieve the error "Run Time error
1004 Paste Method of Worksheet Class Failed". Does anyone have any idea
why? It has worked for months and now it will not. HELP!!!!

Private Sub Workbook_Open()
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "American Red Ball International"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Monthly Agent Bookings - by agent"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Agent No."
Range("C5").Select
ActiveCell.FormulaR1C1 = "No. of"
Range("C6").Select
ActiveCell.FormulaR1C1 = "Registrations"
Rows("2:6").Select
Selection.Font.Bold = True
Range("A8:C8").Select
ActiveSheet.Paste
Rows("8:8").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
ActiveWorkbook.SaveAs ("\\Gateway1\E\Shared_Folders\Agent
Bookings\AgentBookings")
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Cells.PasteSpecial xlPasteAll
Range("A1").Select
Windows("AgentBookings.xls").Activate
ActiveWorkbook.Close False

End Sub

Thanks in advance for any assistance!
Regards,
K


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Run Time error 1004 Paste Method of Worksheet Class Failed
 

Hi Ken,

The line

ActiveSheet.Paste

looks fishy, as it does not have a preceding Copy, so I guess it will try to
paste whatever is on the clipboard when you open the file. Delete that line.

HTH
Anders Silven

"Ken Nunn" skrev i meddelandet
...
Every time I run the following code I recieve the error "Run Time error
1004 Paste Method of Worksheet Class Failed". Does anyone have any idea
why? It has worked for months and now it will not. HELP!!!!

Private Sub Workbook_Open()
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "American Red Ball International"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Monthly Agent Bookings - by agent"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Agent No."
Range("C5").Select
ActiveCell.FormulaR1C1 = "No. of"
Range("C6").Select
ActiveCell.FormulaR1C1 = "Registrations"
Rows("2:6").Select
Selection.Font.Bold = True
Range("A8:C8").Select
ActiveSheet.Paste
Rows("8:8").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
ActiveWorkbook.SaveAs ("\\Gateway1\E\Shared_Folders\Agent
Bookings\AgentBookings")
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Cells.PasteSpecial xlPasteAll
Range("A1").Select
Windows("AgentBookings.xls").Activate
ActiveWorkbook.Close False

End Sub

Thanks in advance for any assistance!
Regards,
K


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Run Time error 1004 Paste Method of Worksheet Class Failed
 

This spreadsheet is fed from a database which copies data to the
clipboard and then into the spreadsheet. It fails on the
"ActiveSheet.Paste" every time however.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!