Here is the code that works:

Private Sub cmdUpdate_Click()
Dim x As Integer
x = Sheets("Cycle Data").Range("CR2").Value
Sheets("MCs").Select
Sheets("MCs").Range("A2:G2", Range("A2:G2").End(xlDown)).ClearContents
Sheets("Cycle Data").Select
Sheets("Cycle Data").Range("CR3:CX3").Resize(x).Copy
Sheets("MCs").Range("A2").PasteSpecial Paste:=xlPasteValues

End Sub

By using a variable as suggested by Jacob to store cell CR2, the above code
from Mike works. Do not understand why this statement:
Sheets("Cycle Data").Range("CR3:CX3").Resize(Range("CR2")).Copy works in
immediate window but not in the program. Always thought that immediate window
is my Excel formula evaluator. Thanks for input from Mike and Jacob and Sam.

"Jacob Skaria" wrote:

John

The reason for error is that when you ClearContents the Copy will be
lost..Rearrange the code to clear contents before OR you can try out a better
way without selecting any sheets as below..

Sub Macro2()
Dim intResize As Integer, varRange As Variant
intResize = Sheets("Cycle Data").Range("CR2").Value
varRange = Sheets("Cycle Data").Range("CR3:CX3").Resize(intResize)
Sheets("MCs").Range("A2:G2").Resize(intResize).Cle arContents
Sheets("MCs").Range("A2:G2").Resize(intResize) = varRange
End Sub

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


"John P" wrote:

Hi Jacob. Used your range statement but still same runtime error on the range
statement.

"Jacob Skaria" wrote:

Apologies.. I didnt notice "CR2 contains an integer"

With your code make sure CR2 of Sheets("Cycle Data") contains a value...If
it is blank or 0 then this will return error...

OR

Sheets("Cycle Data").Activate
Range("CR3:CX3").Resize(Sheets("<sheetname").Rang e("CR2")).Select


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


"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