Hi John,

If you change

Range("CR3:CX3").Resize(Range("CR2")).Select

to

Range("CR3:CX3").Resize(Range("CR2")).interior.col orindex = 15

the macro will fail (obviously) but you should see a grey patch on the area
you want to copy. Can you try that and make sure it's selecting what you
think it ought to?

Sam
"John P" wrote:

Thanks Sam. Mike has given the same suggestion but it is the selecting the
range to copy that is giving the problem even before the copy and paste.

"Sam Wilson" wrote:

Might be because you're trying to paste 7 columns by 78 rows into a different
sized space.

Rather than

Selection.PasteSpecial Paste:=xlPasteValues

Try

Range("A2").PasteSpecial Paste:=xlPasteValues

Sam

"John P" wrote:

Yes. Same runtime error on your Range statement. I now suspect problem may
not be range syntax but other coding errors. Pasting my codes for you to
comment.

Private Sub cmdUpdate_Click()
'Copy and Paste Data
Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Selection.Copy
Sheets("MCs").Select
Range("A2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.PasteSpecial Paste:=xlPasteValues

End Sub


Probably other errors down the code but I am stuck on the second statement.
Note that code is in commandbutton in Sheet("MCs") and trying to copy a
range in Sheet("Cycle Data"). Thanks

"Mike H" wrote:

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))


Did you try my suggestion?

Mike

"John P" wrote:

No. I want to increase the number of rows in Range("CR3:CX3") by an integer
located in Range("CR2"). Hence, Range("CR3: CX3").Resize(Range("CR2"))

Reference book says for resizing rows only, can omit column parameter.
Program is to copy a range in Sheet1 and paste to sheet2. Code is in a
commandbutton embedded in Sheet2. Thanks.

"Jacob Skaria" wrote:

Do you mean

Range("CR3:CX3","CR2").Select

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Activate the sheet and try the second line alone in your immediate window;
which should return an error. Please refer the help on Resize. Resize expects
a the rowsize and columnsize to be passed.

expression.Resize(RowSize, ColumnSize)

If this post helps click Yes
---------------
Jacob Skaria


"John P" wrote:

Hi. Appreciate if anyone can point out the error in the follwing statements
which work in the immediate window but keep turning up runtime error 1004 on
the second statement when I run the program:

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Range("CR2")).Select

Note that cell CR2 contains an integer. Thanks