Simple Copy Paste with VBA Problem - Please Help!

rootdown42

Board Regular
Joined
Jun 8, 2005
Messages
93
I'm really pulling my hair out on this, cause this shouldn't be difficult.

I have two sheets, a "Data" and "Display" sheet. I'm simply trying to copy the data from "Data" to "Display". When I get to the following code:
Code:
shtData.Range("A2", Range("A2").End(xlDown).End(xlToRight).Address).Copy _
        Destination:=shtDisplay.Cells(7, 2)
I get a runtime error 1004 - information can't be pasted because copy and paste area are not the same size/shape. Doesn't VBA know that Cells(7, 2) is only the top-right cell of the range I'm trying to paste? I can't define the size of the paste range because the copy range size is going to be different every time. I swear I've done this before without issue. Is this error really caused by range-size issues, or could it have a problem with switching from one sheet to another?

This project is due to my boss tomorrow, and I'm going to look really dumb if this is why I can't deliver. Any help is most appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Yes VBA does know that Cells(7, 2) is only the top-right cell of the range you're trying to paste. But what is the size of Range("A2", Range("A2").End(xlDown).End(xlToRight).Address)? It may be more columns than you have available on the target sheet.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Even with multiple column your code seems to work for me... how are you assigning the values to shtData and shtDisplay?
 

rootdown42

Board Regular
Joined
Jun 8, 2005
Messages
93
Thank you both for your replys. Andrew was correct in that I was trying to paste more than I could on shtDisplay. This occurred because when I attempted to copy the data by using xlToRight, I ended up including every column over to column 256, which is not what I was trying to do. So that problem is solved.

But this leads me to a new problem. I'm trying to copy over all the data excluding the header row in row 1. I had previously used this code to copy the data
Code:
shtData.Range(Cells(2, 1), Cells(rowDataEnd, colDataEnd)).Copy
where rowDataEnd contains the last row of data and colDataEnd contains the last column of data - both defined as Long. When I ran this code, I get the dreaded "Run time error 1004 - application defined or object defined error", but I did a Watch on both variables and they contain the correct values. Can you help me determine why this method doesn't work?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The variables might be correct but since you've not used a worksheet reference for Cells VBA could be looking at the wrong sheet.
Code:
With shtData
   .Range(.Cells(2, 1), .Cells(rowDataEnd, colDataEnd)).Copy
End With
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I think that you need to qualify the Cells with the sheet:

Code:
shtData.Range(shtData.Cells(2, 1), shtData.Cells(rowDataEnd, colDataEnd)).Copy
 

rootdown42

Board Regular
Joined
Jun 8, 2005
Messages
93
Yes! That was it! Thank you so much everyone for your quick and excellent help. This board is a life saver.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top