RunTime 1004

[dsainz]

We are moving from Excel 2003 to 2007 and we have a report that reads from another spreadsheet. It still runs fine in 03 but in 07 we get the following error

Run-Time Error '1004' The information cannot be pasted because the Copy area and the paste are are not the same size and shape.

Enclosed is the code.

Dim AICount As Integer
Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="AISCN"
Application.Columns("L:L").Select
Selection.Copy
Windows("AutoProdRpt.xls").Activate
Sheets("AISCN").Select
Application.Range("A1").Select
ActiveSheet.Paste
Application.Range("C2").Select
Application.CutCopyMode = False
Application.Range("C1").Value = "=COUNTA(A:A) - 1"
Windows(Right(strFileName, 12)).Activate
Selection.AutoFilter
Application.Range("A1").Select
Windows("AutoProdRpt.xls").Activate
End Sub

It has a yellow highlight over the ActiveSheet.Paste

Any assistance will be appreciated. Thank you.

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the Active Workbook and AutoProdRpt.xls both the same version of Excel? That is, do they both have 65,536 rows or both 1,048,576 rows? Untested, but instead of 'Selection.Copy', try:
Range("L1",Range("L" & Rows.Count).End(xlUp)).Copy

 

[Norie]

I really don't think the reason it isn't working is a version issue.

You really need to lose all the Activate/Select/ActiveSheet etc.

I think I know what the purpose of the code but because of the above it's hard to tell exactly and/or post alternative code.

 

[dsainz]

Welcome to the MrExcel board!

Are the Active Workbook and AutoProdRpt.xls both the same version of Excel? That is, do they both have 65,536 rows or both 1,048,576 rows? Untested, but instead of 'Selection.Copy', try:
Range("L1",Range("L" & Rows.Count).End(xlUp)).Copy

Thank you for your help Peter. That did clear the error, but unfortunalty the data did not paste. I am by no means a VBA expert, I am just a helpdesk guy that got draft .

 

[dsainz]

I really don't think the reason it isn't working is a version issue.

You really need to lose all the Activate/Select/ActiveSheet etc.

I think I know what the purpose of the code but because of the above it's hard to tell exactly and/or post alternative code.

It is a script to copy and paste data out of an automated csv file into a production report excel document. As I mentioned in the above post I am just a help desk guy and not an expert in VB or excel. If you need anythign else from the code I will do my best to get that for you.

 

[dsainz]

Just an addon, it appears when I try and manually copy and paste just selected criteria, if I attempt to copy and paste the whole column there is no problem.

 

[dsainz]

I got it fixed:

I changed this line Application.Columns("L:L").Select to read

Application.Range("L1:L65536").Select

That has cleared up my problems. Thank you for all your help.