Run-time error '1004': PasteSpecial method of

August 2, 2011 at 01:03:37
Specs: Windows 7

Need help to debug this error... =) ... i needed to clear the contents in Print Table worksheet before pasting the new data in it.. hence, i insert the statement "Worksheets("Print Table").Range("A2.L10000").Clear" but got the error:
Run-time error '1004': PasteSpecial method of Range class failed ..
Sub AssignReceiptFees()
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
'Define Variables
Dim copyRow As Integer
Dim copyColARange As String
Dim copyContinue As Boolean
Dim ReceiptNo As Integer
Dim fRow As Long
Dim eRow As Long
Dim iRow As Long
Dim pasteRow As Long

'Initialize variables
'copyContinue = True
' copyRow = 1
ReceiptNo = 10000

'retrive row number of last record
fRow = Range("m65536").End(xlUp).Row
'MsgBox fRow
'retrive row number of last record
eRow = Range("b65536").End(xlUp).Row

'Loop for start to end of empty rows
For iRow = fRow + 1 To eRow
'MsgBox fRow
'MsgBox iRow
If iRow = 2 Then
Cells(iRow, 13).Value = ReceiptNo
Cells(iRow, 1).Value = "P11/12-" & ReceiptNo
Cells(iRow, 13).Value = Application.Max(Range("m:m")) + 1
Cells(iRow, 1).Value = "P11/12-" & Cells(iRow, 13).Value
End If
Next iRow

'Copy data from columns A - L in Summary Receipt Fees
Worksheets("Summary_Receipt_Fees").Range(("A" & CStr(fRow + 1)), ("L" & CStr(iRow - 1))).Copy

'Selects Print Table Worksheet
Worksheets("Print Table").Select
'Activates cells to make them into the active cell
Worksheets("Print Table").Range("a2").Activate

Worksheets("Print Table").Range("A2.L10000").Clear

'Search for last empty row in Summary_Receipt_Fees
Worksheets("Print Table").Range("A2").PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True
End Sub

See More: Run-time error 1004: PasteSpecial method of

Report •

August 2, 2011 at 12:57:13

'Search for last empty row in Summary_Receipt_Fees
Worksheets("Print Table").Range("A2").PasteSpecial Paste:=xlPasteValues

First, that comment doesn't seem to match up with the instruction.

Second, when you use the Clear method, it apparently clears the Clip Board.

Try this in a new workbook:

1 - Copy this code into the VBA editor, but don't run it.

Sub OhNoItsGone()
 Range("A1") = "Hello"
End Sub

2 - Size the VBA editor window so that you can see the spreadsheet behind it.
3 - Place your cursor inside the code and press F8 (Single Step) 4 times.
4 - Hello should be in A1 and the Copy "bars" should be circling A1.
5 - Right click any cell(s) in the spreadsheet and you can Paste "Hello" wherever you'd like, as often as you'd like.
6 - Go back into the VBA editor and press F8 one more time to execute the .Clear line.
7 - Notice that the Copy bars are gone. Right click in any cell and you'll see that the Paste option is grayed out because the Clip Board is empty.

Annoying, isn't it?

Try this instead of .Clear:

Worksheets("Print Table").Range("A2:L10000") = ""

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question