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]

You could always so a Find and Replace on that whole range, replacing a single space with nothing.

Or use the TRIM function to remove the spaces at the beginning and end of entries.

 

[VinnyVBA]

Dim Word As String
Dim RWord As String

For row = 16 To 2 Step -1

Word = (Cells(row, 1).Value)

RWord = Trim(Word.Value)

Cells(row, 1) = RWord

Next row

That's what I came up with. It should take the value in the box and assign it to the variable "Word". Then it should trim the spaces off and store the revised value to variable "RWord" and after that, put RWord back in the cell.

It doesn't work. I keep getting a Compile error saying "Invalid Qualifier" and I don't know what that means

 

[Joe4]

With strings, it is better to use ".Text" than ".Value".
But the main part of the problem is with your function. You don't need the ".Text" or ".Value" after it, WORD is a string variable so it is not necessary.

You can combine it all down to this:

For Row = 16 To 2 Step -1
   Cells(Row, 1) = Trim(Cells(Row, 1).Text)
Next Row

 

[VinnyVBA]

I totally forgot about .text -_-

I wish it would have occurred to me sooner to use Cells inside of function like that. I've been racking my brain or doing things the long because of that.

Thank you for the help! This is some handy stuff. I really appreciate it. Have a good one!

 

[VinnyVBA]

Well things were going great until today. All of a sudden I get an error that says "Compile error: Wrong number of arguments or invalid property assignment"

For row = 3600 To 2 Step -1
   Cells(row, 1) = Trim(Cells(row, 1).Text)
Next row

Any ideas? It worked fine last night. I am using a computer that just crashed so could that perhaps have something to do with it?

 

[Joe4]

I would try re-booting. Computer crashes can cause a myriad of issues.

 

[VinnyVBA]

Alright. I wont jump the gun then. The code is probably fine. Thanks!!

 

[Joe4]

Yes. Usually if you have got that works, and then all of a sudden stops working someday and you haven't changed the code, it usually means that there is a problem with the worksheet or data.

 

[VinnyVBA]

Turns out I wasn't thinking very much. I named that sub program "Sub Trim()" and VBA doesn't like when you do that I guess. I renamed it "Sub Trimmer()" and it worked great.

 

[Joe4]

A general rule of thumb: when naming Sub Procedures, User Defined Functions, and variables in VBA, you should NEVER use reserved words (like those of existing functions, properties, or methods).

I often prefix my Sub Procedures with "My" to ensure this never happens (i.e. "MyTrim").