[HM]

Greetings All,
This is probably very simple and I'm probalby going to over explain it but
here goes:

I have the following code from a macro I recorded and I'm trying to find a
way to simplify. The first line pulls a date field for C1. But after that
I am hoping to repeat a pattern for C2 to C121 where values are placed in
Column C as such: I'm retreiving information to paste into worksheet
"Sheet1" from worksheet "Schedule". The information is pulled from rows 2
& 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the previous
row. And for each set of rows (2,3) for example, it pulls data from 3
adjacent columns (C,D,E). I don's see any D or E in the macro code, but it
looks like C3,C4 and C5 represent those 3 columns. Below is the pasted
macro code:


ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C3"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C4"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C5"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C5"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C3"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C3"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C5"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C5"

The macro would continue on to finish at C121

Thank you. Any help is greatly appreciated,

HM

[Dick Kusleika[_3_]]

HM

Try this

Sub PullFromSchedule()

Dim i As Long, j As Long
Dim Rng As Range
Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets("Schedule")
Set Rng = ThisWorkbook.Sheets("Sheet1").Range("C1")

Rng.Formula = "=Schedule!C1"

For i = 2 To 121 Step 6
For j = 3 To 5
Rng.Offset(i + j - 4, 0).Formula = _
"=Schedule!" & Sh.Cells(i, j).Address(0, 0)
Rng.Offset(i + j - 1, 0).Formula = _
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0)
Next j
Next i

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"HM" wrote in message
...
Greetings All,
This is probably very simple and I'm probalby going to over explain it but
here goes:

I have the following code from a macro I recorded and I'm trying to find a
way to simplify. The first line pulls a date field for C1. But after
that
I am hoping to repeat a pattern for C2 to C121 where values are placed in
Column C as such: I'm retreiving information to paste into worksheet
"Sheet1" from worksheet "Schedule". The information is pulled from rows
2
& 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the
previous
row. And for each set of rows (2,3) for example, it pulls data from 3
adjacent columns (C,D,E). I don's see any D or E in the macro code, but
it
looks like C3,C4 and C5 represent those 3 columns. Below is the pasted
macro code:


ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C3"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C4"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C5"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C5"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C3"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C3"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C5"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C5"

The macro would continue on to finish at C121

Thank you. Any help is greatly appreciated,

HM

[HM]

Dick,

That's exactly what I was looking for!

Thanks a lot!

HM


"Dick Kusleika" wrote in message
...
HM

Try this

Sub PullFromSchedule()

Dim i As Long, j As Long
Dim Rng As Range
Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets("Schedule")
Set Rng = ThisWorkbook.Sheets("Sheet1").Range("C1")

Rng.Formula = "=Schedule!C1"

For i = 2 To 121 Step 6
For j = 3 To 5
Rng.Offset(i + j - 4, 0).Formula = _
"=Schedule!" & Sh.Cells(i, j).Address(0, 0)
Rng.Offset(i + j - 1, 0).Formula = _
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0)
Next j
Next i

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"HM" wrote in message
...
Greetings All,
This is probably very simple and I'm probalby going to over explain it
but
here goes:

I have the following code from a macro I recorded and I'm trying to find
a
way to simplify. The first line pulls a date field for C1. But after
that
I am hoping to repeat a pattern for C2 to C121 where values are placed
in
Column C as such: I'm retreiving information to paste into worksheet
"Sheet1" from worksheet "Schedule". The information is pulled from
rows
2
& 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the
previous
row. And for each set of rows (2,3) for example, it pulls data from 3
adjacent columns (C,D,E). I don's see any D or E in the macro code, but
it
looks like C3,C4 and C5 represent those 3 columns. Below is the
pasted
macro code:


ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C3"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C4"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R2C5"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!R3C5"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C3"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C3"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=Schedule!R8C5"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=Schedule!R9C5"

The macro would continue on to finish at C121

Thank you. Any help is greatly appreciated,

HM