Macro to increase cell range by 1

[InventoryQueryGuy]

I need to have 125 copies of a work sheet:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
Next
End Sub

However, within each worksheet, certain cells must make reference to a cell
on another worksheet which for example is A2 from MASTER DATA sheet on sheet
MD1, A3 from MASTER DATA sheet on sheet MD2, etc. The formula below simply
copies the same data for each new sheet.

Range("A4:B4").Select
ActiveCell.FormulaR1C1 = "='MASTER DATA'!R[4]C"
End Sub

Any ideas as to how I can incorporate the sheet copy function and the
incremental cell reference formula are much appreciated!

Cheers.

 

[JLGWhiz]

Is this what you mean?

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
ActiveSheet.Range("A4:B4").FormulaR1C1 = "='Master Data'!R[4]C"
Next
End Sub

 

[InventoryQueryGuy]

So we got half of it!
It generated 125 copies of the worksheet MD and named t hem MD1 - MD125.

Now what I need to do is for MD1, cell A4 needs to be equal to cell A6 on
the MASTER DATA sheet. On sheet MD2, cell A4 needs to equal cell A7 on the
MASTER DATA sheet. And so on and so forth for 125 records.

I think this is the line which needs amending:
ActiveSheet.Range("A4:B4").FormulaR1C1 = "='Master Data'!R[4]C

FYI: MD sheets have a A4:B4 merged cell.

Thanks.

 

[JLGWhiz]

I am not too sure about this, but it gave the results you described. It also
makes B4 equal to B6, etc on MASTER DATA, so you might get some #REF! errors
in B4 on the MD sheets. If you don't want that to happen then just Delete
the B4 from the Range("A4:B4") reference so it only puts the formula in A4
on tje MD sheets.

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
ActiveSheet.Range("A4:B4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C"
Next
End Sub

 

[Jim Cone]

Range("A4").Formula = "=''MASTER DATA'!A" & i + 1
--
Jim Cone
Portland, Oregon USA


"InventoryQueryGuy"
<[email protected]>
wrote in message
I need to have 125 copies of a work sheet:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
Next
End Sub

However, within each worksheet, certain cells must make reference to a cell
on another worksheet which for example is A2 from MASTER DATA sheet on sheet
MD1, A3 from MASTER DATA sheet on sheet MD2, etc. The formula below simply
copies the same data for each new sheet.

Range("A4:B4").Select
ActiveCell.FormulaR1C1 = "='MASTER DATA'!R[4]C"
End Sub

Any ideas as to how I can incorporate the sheet copy function and the
incremental cell reference formula are much appreciated!
Cheers.

 

[InventoryQueryGuy]

Thanks a lot guys.
I actually used a combination of both of your code contributions:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
ActiveSheet.Range("A4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C"
ActiveSheet.Range("C4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C[-1]"
ActiveSheet.Range("E4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C[-2]"
ActiveSheet.Range("G4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C[-3]"
ActiveSheet.Range("I4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C[-4]"
ActiveSheet.Range("K4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C[-5]"
Next
End Sub


Wicked
Cheers.

 

[mdmackillop]

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
ActiveSheet.Name = "MD" & i
Range("A4:B4").FormulaR1C1 = "='MASTER DATA'!R[" & 3 + i & "]C"
Next
End Su

 

[mdmackillop]

Try an inner loop
Sub Macro1()
Dim i As Long, j As Long, col As Long
Application.ScreenUpdating = False
For i = 1 To 125
Sheets("MD").Copy Before:=Sheets(1)
With ActiveSheet
.Name = "MD" & i
For j = 1 To 11 Step 2
col = col + 1
.Cells(4, j).FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C" & col
Next
col = 0
End With
Next
Application.ScreenUpdating = True
End Su