Run-time error 1004 "The information cannot be pasted because the copy

[arbo80]

Hi all,

I have a VBA macro which had been working fine until recently. Now I'm getting the following error message: "Run time error 1004: "The information cannot be pasted because the copy area and paste area are not the same size or shape...". Can you help me? Below is my code:

Sub GetSummitData()
Dim strSQL As String
Dim rs As Recordset
Dim rsDesk As Recordset
Dim I As Integer
Dim intYear As Integer
Dim intCurYear

Sheets("Sheet1").Select
Range("AA:AD").Select
Selection.Copy

Sheets("Summit").Select
lngRow = 3
intYear = Year(Now)
' intYear = 2008

intCurYear = Year(Now)
datCurWeek = Now - Weekday(Now) + 2

strSQL = "SELECT DISTINCT Summit.Desk FROM Summit"
Set rsDesk = db.OpenRecordset(strSQL)
rsDesk.MoveFirst
I = 2
While Not rsDesk.EOF
Sheets("Summit").Cells(1, I).Select
Sheets("Summit").Paste -> 'Failing at this line
Sheets("Summit").Cells(1, I) = rsDesk(0)
I = I + 4
rsDesk.MoveNext
Wend
'more code below

End Sub

Thanks,
B.

 

[SkipVought]

Hi,

I frankly question your process.

You have COPIED 2 COLUMNS.

Then you loop thru ROWS (I) doing a PASTE to a cell????

If you copy columns, you must paste columns

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[arbo80]

Thanks Skip! This VBA macro was actually created by someone who no longer works for my company. I have some basic knowledge but I'm not really an expert on VBA; I'm just trying to find a way to make it work.

 

[SkipVought]

It would seem to me that you want to "paste" or assign cells in a ROW of data in your loop.

What's the intent of the macro?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[arbo80]

The macro is collecting feeds from some systems. The extracts are file-based and the macro reads the files, parses them and collects statistics about modifications made to trades in these systems. To summarize, it pretty much copies data and pastes it to a template.


Apparently, the macro used to work fine until recently and as I mentioned earlier the guy who designed it no longer works for my company.

Note: I didn't post the entire code; just the part that is giving me an error.

Thanks for your help,

B.

 

[arbo80]

Hi,

The macro also extracts data from an Access DB and copy the data to an excel file. The problem is that there are 69 distinct records (strSQL = "SELECT DISTINCT Summit.Desk FROM Summit"); but the macro only copies 63 (instead of 69) and then it returns the error message above. Any suggestions?

Thanks,
B.

 

[SkipVought]

Try changing the sequence of statements as follows...

[s]
    Sheets("Sheet1").Select
    Range("AA:AD").Select
    Selection.Copy
    
    Sheets("Summit").Select[/s]
    lngRow = 3
    intYear = Year(Now)
'    intYear = 2008

    intCurYear = Year(Now)
    datCurWeek = Now - Weekday(Now) + 2
    
    strSQL = "SELECT DISTINCT Summit.Desk FROM Summit"
    Set rsDesk = db.OpenRecordset(strSQL)
    rsDesk.MoveFirst
[b][red]
    Sheets("Sheet1").Range("AA:AD").Copy
    
    Sheets("Summit").Select[/red][/b]

    I = 2
    While Not rsDesk.EOF
        Sheets("Summit").Cells(1, I).Select
        Sheets("Summit").Paste -> 'Failing at this line
        Sheets("Summit").Cells(1, I) = rsDesk(0)
        I = I + 4
        rsDesk.MoveNext
    Wend

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[arbo80]

Hi Skip,

I finally identified the issue. My macro is trying to return 274 columns while Excel 2003's limit is 256 Columns. To fix this issue, I must use Excel 2007.

Thanks for your help. I really do appreciate the time and effort.

B.