ERROR:Copy Paste Areas are different

[SBF12345]

Greetings,

Range("A4:A3").Select        Range(Selection, Selection.End(xlToRight)).Copy
        COM.Worksheets(G).Activate
        Range("A2").Select
        Selection.End(xlToRight).Offset(-1, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues

I am receiving an error message that is saying the copy range is not able to paste. The area to be copied is a rectangle. I would like the paste range to be a single cell. How to grab only the cell at the offset point?

Thanks!

 

[MARK858]

Paste it to the single top left cell.

Range("A3:A4").Select
Range(Selection, Selection.End(xlToRight)).Copy
COM.Worksheets(G).Range("A2").PasteSpecial Paste:=xlPasteValues

I have put it as A2 although looking at your code it looks like you are trying to start it in A1 so change it if necessary or use the below (although I can't see a good reason to not refer directly to the cell with what you have posted)...

Range("A3:A4").Select
Range(Selection, Selection.End(xlToRight)).Copy
COM.Worksheets(G).Range("A2").offset(-1).PasteSpecial Paste:=xlPasteValues

 

[SBF12345]

Hmmmm...

The error message I am receiving says

"Runtime Error 1004: The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following: click a single cell and then paste select a rectangle thats the same size and shape, and then paste. "

could the copied rectangle be creating problems for the paste area due to the way I selected the range for copy? I used A4:A3 instead of the other way around because I want the xltoRight function to run along the A4 row instead of the 3rd row.

I would still like the pasted material to be pasted from the top left corner however, I don't think I've seen any other paste method from a different reference point.

 

[MARK858]

Are you pasting to a single cell as I posted? what is your variable G in Worksheets(G) and what is your variable COM?

Post your actual code where you are defining G and COM.

 

[SBF12345]

Not sure why my response is not updating the thread

 

[SBF12345]

well that one worked....ill try once more

I would like to set the target cell for pasting using the End method so that in case the number of columns changes in the future I won't have to rewrite it.

here are the lines that define the variables

Set COM = Workbooks("COMM_COMBINED.xls")

For Each Worksheet In PA.Worksheets    
    
    G = ActiveSheet.Name

lets see if this works...Thanks!

 

[SBF12345]

ERROR: PasteSpecial Method of Range Class Failed

I am receiving the "PasteSpecial Method of Range Class failed" but I can't figure out why. The lower of the two lines in the code is recording the error.

COM.Worksheets(G).Activate
        Range("A2").End(xlToRight).Offset(-1, 1).PasteSpecial Paste:=xlPasteValues

Thanks!

 

[MARK858]

This

For Each Worksheet In PA.Worksheets    
    
    G = ActiveSheet.Name

Should probably be

For Each Worksheet In PA.Worksheets    
    
    G = Worksheet.Name

Although you shouldn't (whether a reserved word or not) use Worksheet as a name for a variable as it used by VBA and so is liable to confuse it (and if it is in the list of reserved words it will throw an error), better to use something like...

Dim ws as Worksheet
' other code if needed
For Each ws In PA.Worksheets    
    
    G = ws.Name

Personally I would leave the variable as a worksheet object rather than use a string conversion.

I would like to set the target cell for pasting using the End method so that in case the number of columns changes in the future I won't have to rewrite it.

If you are talking about the number of columns in the copy range changing then that doesn't affect the paste destination if you are using a single cell as the destination and you wouldn't need to re-write it for that.

If you are talking about the destination cell then (guessing a bit here as you haven't described it) then I would use.

COM.Worksheets(G).Cells(2, Columns.count).End(xlToLeft).Offset(-1, 1).PasteSpecial Paste:=xlPasteValues

This assumes that your Offset and row number originally was correct.

 

[AlphaFrog]

Re: ERROR: PasteSpecial Method of Range Class Failed

What did you copy? If you copied an entire column, you could get that error. Can you show all the code?

 

[SBF12345]

Re: ERROR: PasteSpecial Method of Range Class Failed

Range("A4").Select
        Range(Selection, Selection.End(xlToRight).Offset(-1, 0)).Copy

these are the copy lines...just to clarify I have the offset here too. my intention is to copy a rectangle two rows by the selection end(xltoRight) wide