Access 2007 on Vista

The Excel macro recorder provided the three sample blocks of code (almost
identical you can see, but pattern flow should be noted) which I need to code
into a single block to repeat "n" times based on cell "D17". I know where I
am, I know where I want to get to, I just don't know the way to get there.
Many thanks in advance.


i = 1
Filler = Range("D17").Value

Do While i < Filler

Sheets("Sample Data").Select
Range("B3:E3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C21").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B4:E4").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C22").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B5:E5").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C23").Select
ActiveSheet.Paste

i = i + 1
Loop

The syntax to have this puppy perform is what I an needing.

Thanks ....TheGrapeHunter

i = 1
Filler = Range("D17").Value
Do While i < Filler

Sheets("Sample Data").Select
Range("B" & (i + 2) & ":E" & (i + 2)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("C" & (i + 20)).Select
ActiveSheet.Paste

i = i + 1
Loop

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


"The Grape Hunter" wrote:

Access 2007 on Vista

The Excel macro recorder provided the three sample blocks of code (almost
identical you can see, but pattern flow should be noted) which I need to code
into a single block to repeat "n" times based on cell "D17". I know where I
am, I know where I want to get to, I just don't know the way to get there.
Many thanks in advance.


i = 1
Filler = Range("D17").Value

Do While i < Filler

Sheets("Sample Data").Select
Range("B3:E3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C21").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B4:E4").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C22").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B5:E5").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C23").Select
ActiveSheet.Paste

i = i + 1
Loop

The syntax to have this puppy perform is what I an needing.

Thanks ....TheGrapeHunter

Shorter:

i = 1
Filler = Range("D17").Value
Do While i < Filler
Sheets("Sample Data").Range("B" & (i + 2) & ":E" & (i + 2)).Copy _
Sheets("Sheet1"). Range("C" & (i + 20))
i = i + 1
Loop

Typically it's best to avoid select/activate unless you really need it.

Tim



"Jacob Skaria" wrote in message
...
i = 1
Filler = Range("D17").Value
Do While i < Filler

Sheets("Sample Data").Select
Range("B" & (i + 2) & ":E" & (i + 2)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("C" & (i + 20)).Select
ActiveSheet.Paste

i = i + 1
Loop

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


"The Grape Hunter" wrote:

Access 2007 on Vista

The Excel macro recorder provided the three sample blocks of code (almost
identical you can see, but pattern flow should be noted) which I need to
code
into a single block to repeat "n" times based on cell "D17". I know where
I
am, I know where I want to get to, I just don't know the way to get
there.
Many thanks in advance.


i = 1
Filler = Range("D17").Value

Do While i < Filler

Sheets("Sample Data").Select
Range("B3:E3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C21").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B4:E4").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C22").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B5:E5").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C23").Select
ActiveSheet.Paste

i = i + 1
Loop

The syntax to have this puppy perform is what I an needing.

Thanks ....TheGrapeHunter

And, without the loop...

With Worksheets("Sample Data")
Filler = .Range("D17").Value
.Range("B3").Resize(Filler, 4).Copy Worksheets("Sheet1").Range("C21")
End With

It wasn't clear from the OP's post which worksheet D17 was on, so I assumed
it was on the "Sample Data" sheet... that can be corrected as necessary.

--
Rick (MVP - Excel)


"Tim Williams" wrote in message
...
Shorter:

i = 1
Filler = Range("D17").Value
Do While i < Filler
Sheets("Sample Data").Range("B" & (i + 2) & ":E" & (i + 2)).Copy _
Sheets("Sheet1"). Range("C" & (i + 20))
i = i + 1
Loop

Typically it's best to avoid select/activate unless you really need it.

Tim



"Jacob Skaria" wrote in message
...
i = 1
Filler = Range("D17").Value
Do While i < Filler

Sheets("Sample Data").Select
Range("B" & (i + 2) & ":E" & (i + 2)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("C" & (i + 20)).Select
ActiveSheet.Paste

i = i + 1
Loop

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


"The Grape Hunter" wrote:

Access 2007 on Vista

The Excel macro recorder provided the three sample blocks of code
(almost
identical you can see, but pattern flow should be noted) which I need to
code
into a single block to repeat "n" times based on cell "D17". I know
where I
am, I know where I want to get to, I just don't know the way to get
there.
Many thanks in advance.


i = 1
Filler = Range("D17").Value

Do While i < Filler

Sheets("Sample Data").Select
Range("B3:E3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C21").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B4:E4").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C22").Select
ActiveSheet.Paste

Sheets("Sample Data").Select
Range("B5:E5").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C23").Select
ActiveSheet.Paste

i = i + 1
Loop

The syntax to have this puppy perform is what I an needing.

Thanks ....TheGrapeHunter