Using variables with ROWs
 

Is it possible to use varibles in a function such as "ROWS
(2:5).Select" (see line 70 & 80 of code below) to allow
for looping such as:

For example if SAMPLE_NUMBER=4 I want to insert the copied
cells in 3 different places.

10 For x = 1 To (Sample_Number - 1)
20 'Copy the original 4 rows
30 Rows("2:5").Select
40 Application.CutCopyMode = False
50 Selection.Copy
60 'Insert copied cells
70 y = 2 + (x * 4) 'move down four rows
80 Rows(y:y).Select 'THIS DOES NOT WORK
90 Selection.Insert Shift:=xlDown
100 'repeat this copy/paste moving 4 additional rows
each time.
110 Next x

Using variables with ROWs
 

Michael,

Just use the row number in the Rows object. E.g.,

Rows(y).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Michael" wrote in message
...
Is it possible to use varibles in a function such as "ROWS
(2:5).Select" (see line 70 & 80 of code below) to allow
for looping such as:

For example if SAMPLE_NUMBER=4 I want to insert the copied
cells in 3 different places.

10 For x = 1 To (Sample_Number - 1)
20 'Copy the original 4 rows
30 Rows("2:5").Select
40 Application.CutCopyMode = False
50 Selection.Copy
60 'Insert copied cells
70 y = 2 + (x * 4) 'move down four rows
80 Rows(y:y).Select 'THIS DOES NOT WORK
90 Selection.Insert Shift:=xlDown
100 'repeat this copy/paste moving 4 additional rows
each time.
110 Next x

Using variables with ROWs
 

Michael,

When you copy a range and want to paste it (or insert it) you need only
designate a single cell for the paste or insert. Note that selection is not
required.

Selection.Copy
Cells(y,1).Insert

Rows("2:5").Copy
Range("A" & y).Insert Shift:=xlDown

--
sb
"Michael" wrote in message
...
Is it possible to use varibles in a function such as "ROWS
(2:5).Select" (see line 70 & 80 of code below) to allow
for looping such as:

For example if SAMPLE_NUMBER=4 I want to insert the copied
cells in 3 different places.

10 For x = 1 To (Sample_Number - 1)
20 'Copy the original 4 rows
30 Rows("2:5").Select
40 Application.CutCopyMode = False
50 Selection.Copy
60 'Insert copied cells
70 y = 2 + (x * 4) 'move down four rows
80 Rows(y:y).Select 'THIS DOES NOT WORK
90 Selection.Insert Shift:=xlDown
100 'repeat this copy/paste moving 4 additional rows
each time.
110 Next x