Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-time error '1004': PasteSpecial method of range class failed

Status
Not open for further replies.
Jan 24, 2005
38
0
0
US
I have a macro someone else created and has since left the company, and when I try to run it I get this, any suggestions?

Run-time error '1004':

PasteSpecial method of range class failed


Here is a copy of the code where it is erroring at.

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



IF you need more of the code let me know.

Thanks,
Michael
 


Hi,

Is there code executing between the COPY and PasteSpecial statements? Could be that the Clipboard has lost it's contents -- there's NOTHING to paste.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 


Taking it offline, defeats the purpose of Tek-Tips. Let's try to address it here. If all else fails, we may try that.

Just paste in the statements from the copy to the paste special.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
erm Skip - unless I have missed something, the OP did not request this to go offline

I would say that this is either because the SELECTION is not a range or that the range is filtered so that it cannot paste as there are too many seperate range objects

either way, the code from copy all the way through to paste would be helpful

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Skip,
Here is the entire code for the module the error is in. I have put 3 # in from of the line that is having trouble.

Sub ASRManExcReport()
' Project: HSS500BPRT.PRT Report Manipulation
' Macro: ASRManExcReport
' Company: Fleetwood Homes Central Region Service Center
' Date: Macro recorded 9/30/2004 by Fleetwood Associate
' Programmer: Jason Leidig
' Description: Creates management exception report
'Keyboard Shortcut: N/A
'
Dim intManCellsCol As Integer
Dim intManCellsRow As Integer
Dim strWkBkName As String
Dim strWkBkLocation As String
Dim intStrNumber As Integer
Dim strNewName As String
Dim datDayOldDate As Date
Dim strMEROpenWorkbook As String

'Determine if the "ASR78.xls" is open, if it is open will notify user
'via a message box and will quit the macro
strMEROpenWorkbook = "ManExcepReport78.xls"

For Each Workbook In Application.Workbooks
If Workbook.Name = strMEROpenWorkbook Then
Workbook.Activate
ActiveWindow.Close
Exit Sub
End If
Next

'Add sheets and name each sheet
'----------------------------------------------------
Workbooks.Add
strWkBkName = ActiveWorkbook.Name
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets("Sheet16").Select
Sheets("Sheet16").Name = "RO St 1-4"
Sheets("Sheet15").Select
Sheets("Sheet15").Name = "RA St 1-4"
Sheets("Sheet14").Select
Sheets("Sheet14").Name = "RP St 1-4"
Sheets("Sheet13").Select
Sheets("Sheet13").Name = "St 8 No Date"
Sheets("Sheet12").Select
Sheets("Sheet12").Name = "St 8 Schedule Date >1 Day"
Sheets("Sheet11").Select
Sheets("Sheet11").Name = "St 8-1111 Code"
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "St 8-2222 Code"
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "3333 Codes"
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "8888 Codes"
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "9998 Codes"
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "6666 Codes"
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "7777 Codes"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Over 50's"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "St 5 > 6"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "St 6 > 1"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "St 7 > 7"

'Save new workbook as ManExcepReport78.xls
Windows("ASR78.xls").Activate
strWkBkLocation = ActiveWorkbook.FullName

Windows(strWkBkName).Activate
intStrNumber = Len(strWkBkLocation)
intStrNumber = intStrNumber - 9
strNewName = Left(strWkBkLocation, intStrNumber) & "ManExcepReport78.xls"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
strNewName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True

'------Copy data from the ASR78.xls to the RO St 1-4 tab------
'-----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=9, Criteria1:="O"
Selection.AutoFilter Field:=8, Criteria1:="1"
Selection.AutoFilter Field:=6, Criteria1:=">=4"
Selection.AutoFilter Field:=2, Criteria1:="=078-*"

'Find last cell and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

'Paste data to new workbook tab RO St 1-4
Windows("ManExcepReport78.xls").Activate
Sheets("RO St 1-4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Finish copying rest of data to RO St 1-4
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=8, Criteria1:=">=2", Operator:=xlAnd, _
Criteria2:="<=4"
Selection.AutoFilter Field:=6, Criteria1:=">=3"

'Find last cell and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select
intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

If intManCellsRow > 2 Then
Range(Cells(3, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

'Paste data to new workbook tab RO St 1-4
Windows("ManExcepReport78.xls").Activate
Sheets("RO St 1-4").Select

ActiveCell.SpecialCells(xlLastCell).Select
intManCellsRow = ActiveCell.Row
intManCellsRow = intManCellsRow + 1

Range(Cells(intManCellsRow, 1), Cells(intManCellsRow, 1)).Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

'------Copy data from ASR78.xls and paste to RA St 1-4------
'---------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=9, Criteria1:="A"
Selection.AutoFilter Field:=8, Criteria1:="<=4"
Selection.AutoFilter Field:=6, Criteria1:=">=3"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

'Paste data into ManExcepReport78.xls
Windows("ManExcepReport78.xls").Activate
Sheets("RA St 1-4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to RP St 1-4------
'---------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=9, Criteria1:="P"
Selection.AutoFilter Field:=8, Criteria1:="<=4"
Selection.AutoFilter Field:=6, Criteria1:=">=3"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

'Paste data into ManExcepReport78.xls
Windows("ManExcepReport78.xls").Activate
Sheets("RP St 1-4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 8 No Date------
'------------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=9, Criteria1:="O"
Selection.AutoFilter Field:=8, Criteria1:="8"
Selection.AutoFilter Field:=6, Criteria1:=">=15"
Selection.AutoFilter Field:=5, Criteria1:="="

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 8 No Date").Select
Range("A1").Select
### Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 8 Schedule Date >1 Day------
'-------------------------------------------------------------------------
datDayOldDate = Date
datDayOldDate = datDayOldDate - 2

Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=5, Criteria1:="<=" & datDayOldDate
Selection.AutoFilter Field:=4, Criteria1:="="

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 8 Schedule Date >1 Day").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 8-1111 Code------
'--------------------------------------------------------------
datDayOldDate = 0
datDayOldDate = Date
datDayOldDate = datDayOldDate - 15

Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=8, Criteria1:="<=8"
Selection.AutoFilter Field:=4, Criteria1:="1111", Operator:=xlOr _
, Criteria2:="3311"
Selection.AutoFilter Field:=5, Criteria1:="<=" & datDayOldDate

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 8-1111 Code").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 8-2222 Code------
'--------------------------------------------------------------
datDayOldDate = 0
datDayOldDate = Date
datDayOldDate = datDayOldDate - 8

Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="2222", Operator:=xlOr _
, Criteria2:="3322"
Selection.AutoFilter Field:=5, Criteria1:="<=" & datDayOldDate

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 8-2222 Code").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to 3333 Codes------
'----------------------------------------------------------
datDayOldDate = 0
datDayOldDate = Date
datDayOldDate = datDayOldDate - 14

Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=4, Criteria1:=">=3300", Operator:=xlAnd, _
Criteria2:="<=3399"
Selection.AutoFilter Field:=5, Criteria1:="<=" & datDayOldDate

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("3333 Codes").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to 8888 Codes------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="8888", Operator:=xlOr _
, Criteria2:="3388"
Selection.AutoFilter Field:=5, Criteria1:="<=" & datDayOldDate

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("8888 Codes").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to 9998 Codes------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="9998", Operator:=xlOr _
, Criteria2:="3398"
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6, Criteria1:=">=2"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("9998 Codes").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to 6666 Codes------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="6666"
Selection.AutoFilter Field:=6

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("6666 Codes").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to 7777 Codes------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="7777"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("7777 Codes").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'-----Copy data from ASR78.xls and paste to Over 50's------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7, Criteria1:=">50"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("Over 50's").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 5 > 6------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=6, Criteria1:=">=7"
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8, Criteria1:="5"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 5 > 6").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 6 > 1------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=6, Criteria1:=">1"
Selection.AutoFilter Field:=8, Criteria1:="6"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 6 > 1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'------Copy data from ASR78.xls and paste to St 7 > 7------
'----------------------------------------------------------
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=6, Criteria1:=">=8"
Selection.AutoFilter Field:=8, Criteria1:="7"

'Find last cell of data and copy data in filtered spreadsheet
ActiveCell.SpecialCells(xlLastCell).Select

intManCellsRow = ActiveCell.Row
intManCellsCol = ActiveCell.Column

Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Select
Selection.Copy

Windows("ManExcepReport78.xls").Activate
Sheets("St 7 > 7").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Remove criteria from autofilters and close ASR.xls
Windows("ASR78.xls").Activate
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=2
Range("A1").Select

End Sub
 
Is this sheet:

Sheets("St 8 No Date")

filtered when you are attempting to Pastespecial ?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 



When you get the error, hit the DEBUG button.

Then answer Geoff's question.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Please describe the sheet you are attempting to paste into - does it have any merged cells?
does it have any hidden columns?
is there any data on it already?


enter this line just before the line that errors

msgbox Selection.Address

and let us know what it says

Also, may be worth trying this:

Sheets("St 8 No Date").Range("A1").PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

instead of:

Sheets("St 8 No Date").Select
Range("A1").Select
### Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
This is a blank sheet that the macro creates. It has no merged cells or hidden columns. Your line of code returns a msg of $A$2:$S$390
 



A Haaaaaaa!

Replace your PasteSpecial with
Code:
workbooks("ASR78.xls").worksheets("Whateverthersheetnameis").Range(Cells(2, 1), Cells(intManCellsRow, intManCellsCol)).Copy

Sheets("St 8 No Date").Range("A1").PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False
BTW, I recommend NOT using the Activate & select methods. Rather reference the sheet and range objects explicitly as in the example.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
yup - that's the issue - you have range A2:S390 selected - that is almost certainly the wrong size of range to paste into. Do as Skip suggests (btw - that was what my final idea on my last post was to get around) and it should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top