Simple Copy Paste with VBA Problem - Please Help!

[rootdown42]

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:

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.

 

[Andrew Poulsom]

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]

Even with multiple column your code seems to work for me... how are you assigning the values to shtData and shtDisplay?

 

[rootdown42]

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

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]

The variables might be correct but since you've not used a worksheet reference for Cells VBA could be looking at the wrong sheet.

With shtData
   .Range(.Cells(2, 1), .Cells(rowDataEnd, colDataEnd)).Copy
End With

 

[VoG]

I think that you need to qualify the Cells with the sheet:

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

 

[rootdown42]

Yes! That was it! Thank you so much everyone for your quick and excellent help. This board is a life saver.