Common Error 1004 Copy & Paste

[VinnyVBA]

I'm trying to understand the problem with the following code:

If x = y Then
    Sheets("Sheet1").Select
    Cells(row, 1).Select
    Selection.EntireRow.Copy
    Sheets("Sheet3").Select
    Cells(row, 1).Select
    ActiveCell.PasteSpecial
    Sheets("Sheet2").Select
    Cells(RO, 2).Select
    Selection.EntireRow.Copy
    Sheets("Sheet3").Select
    Cells(row, 23).Select
    ActiveCell.PasteSpecial
    End If

So in summary, if a certain value matches between sheet1 and sheet2, copy the row that holds that value in sheet1 and paste it in sheet3. Then copy the row holding that value in sheet2 and paste it in sheet3 in the same row as the matching data.

I know the row and RO look funny but it works. What doesn't work and what I've been trying to understand is part where I try to paste the data from sheet2 into sheet3. I have it set to paste into empty cells (hence the cells(RO,23).select) but it still says run-time error 1004 cant copy and paste because the size and shape isnt the same.

But I don't understand the issue. The cells aren't even occupied. Why wont it just paste?

 

[Joe4]

The problem is with this part of code right here:

    Sheets("Sheet2").Select
    Cells(RO, 2).Select
    Selection.EntireRow.Copy
    Sheets("Sheet3").Select
    Cells(row, 23).Select
    ActiveCell.PasteSpecial

Note that you are copy the EntireRow (regardless of whether or not there is data in each column). Excel 2007 and higher has 16384 columns. So that code would copy 16384 columns in Excel 2007 or higher (earlier versions have 256 columns, so you would be copying 256 columns in those versions).

However, you are trying to paste it starting in the 23rd column (your line Cells(row, 23).Select). It is impossible to start in anything other than the first column and paste the entire row (you don't have enough columns if you don't start in the first one!).

So you either need to start in the first column like this:
Cells(row, 1).Select
or change your Copy command so you are NOT copying the entire row.

 

[VinnyVBA]

However, you are trying to paste it starting in the 23rd column (your line Cells(row, 23).Select). It is impossible to start in anything other than the first column and paste the entire row (you don't have enough columns if you don't start in the first one!).

So you either need to start in the first column like this:
Cells(row, 1).Select
or change your Copy command so you are NOT copying the entire row.

I have Excel 2010 now.

I know I can use Range to select some of the data instead of the entire row but I don't know how to accomplish what using cells(row,1).select accomplishes meaning how it moves down a row. Is it possible to use Range? Or do I need to research some other method of copying that doesn't use a variable method?

 

[Joe4]

Can you first explain exactly what the logic of your copy is?
Currently, as you have it written, you are trying to copy all columns from a row (columns 1 - 16384) to another row starting in column 23 (so column 23 - 16406, which is why you have an error, you cannot go past column 16384).

Is that really your intention?
Are you really trying to copy the contents from:
- column A to column W
- column B to column X
- column C to column Y
- column D to column Z
- column E to column AA
- etc, etc

 

[VinnyVBA]

I'm merging data from two sheets. There's 22 columns of data in the first sheet and 10 columns of data in the second sheet. I'm trying to match a value from both sheets and then copy the rows containing the matching values into the third sheet. The copying of the first sheet to the third sheet (22 columns) is working great of course. It's trying to paste the second sheets to the third sheet (starting at column 23) that's killing me.

I was thinking:

Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value

But I can't figure out how to get the Range to change in the same way the Cells(row,1) changes.

 

[Joe4]

Let's try to bring this down to a simple example so I can see exactly columns you are talking about.

Walk me through a single simple example.
What columns are you copying FROM Sheet2 (exact column references, please)?
Now, what columns are you trying to paste this TO on Sheet3?

 

[VinnyVBA]

I'm trying to go for

Sheet1 (R, columns 1 - 22) should go to Sheet3 (R, columns 1 - 22)
Sheet2 (R, columns 1 - 10) should go to Sheet3 (R, columns 23 - 33)

How's that? R is just the variable for row.

Here's my whole code:

Sub test4()

Dim x As String
Dim y As String
Dim row As Double
Dim ro As Double
Dim C As Double

C = 16
row = 15
ro = 15
Do While C > 1

Sheets("Sheet1").Select
x = Cells(row, 1).Value

Sheets("Sheet2").Select
y = Cells(ro, 1).Value

Sheets("Sheet3").Select
If x = y Then
    
    If x = y Then
    Sheets("Sheet1").Select
    Cells(row, 1).Select
    Selection.EntireRow.Copy
    Sheets("Sheet3").Select
    Cells(row, 1).Select
    ActiveCell.PasteSpecial
    Sheets("Sheet2").Select
    Cells(ro, 2).Select
    Selection.EntireRow.Copy
    Sheets("Sheet3").Select
    Cells(row, 23).Select
    ActiveCell.Paste
    row = row - 1
    ro = 16
    C = C - 1
    End If
    
Else
ro = ro - 1
If ro = 1 Then
ro = 16
row = row - 1
    If row = 1 Then
    Exit Sub
    End If
End If

End If
Loop

End Sub

I appreciate the help!

 

[VinnyVBA]

I apologize for double posting but I can't seem to find the edit button in my post.

I just thought I'd expand a little bit on the extra if statements at the bottom of the code. I've been using VBA in Excel for about 14 weeks now so if statements are something I'm comfortable with. I know there are much better ways to get through things but I'm still learning those ways. I'm not going into explicit detail but the If statements at the bottom of the code are used to search through the rows in the first column without skipping any values. It's a little cumbersome and unnecessary but it does work for me. All I need is to paste the values and I'll be set.

 

[Joe4]

Sheet2 (R, columns 1 - 10) should go to Sheet3 (R, columns 23 - 33)

That is the part I was looking for.

Simply replace this part of your code:

    Sheets("Sheet2").Select
    Cells(RO, 2).Select
    Selection.EntireRow.Copy

With this:

    Sheets("Sheet2").Select
    Range(Cells(RO, 1), Cells(RO, 10)).Copy

Instead of copying your entire row, this will just copy the first ten columns (which is what you really want) and avoid the problem of your copy range going paste the last available column.

 

[VinnyVBA]

It worked!!

Except it only worked with my sample data. I then noticed this:

Sample data - "1-252"
Real data - " 1-252"

One of the values in one sheet in my real data has spaces before it which is causing the values between the two sheets not to match. I could get upset about this but I decided to just sigh and shake my head. I'm going to go try to make up a macro to delete those spaces. I would hate to have to delete spaces in 10,000 + rows by hand haha