Macro to increase cell range by 1
 

I need to have 125 copies of a work sheet:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Befo=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.

Macro to increase cell range by 1
 

Is this what you mean?

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



"InventoryQueryGuy" wrote:

I need to have 125 copies of a work sheet:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Befo=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.

Macro to increase cell range by 1
 

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.

Macro to increase cell range by 1
 

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 Befo=Sheets(1)
ActiveSheet.Name = "MD" & i
ActiveSheet.Range("A4:B4").FormulaR1C1 = _
"='Master Data'!R[" & i + 1 & "]C"
Next
End Sub





"InventoryQueryGuy" wrote:

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.

Macro to increase cell range by 1
 

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


"InventoryQueryGuy"

wrote in message
I need to have 125 copies of a work sheet:

Sub Macro1()
For i = 1 To 125
Sheets("MD").Copy Befo=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.

Macro to increase cell range by 1
 

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 Befo=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.

Macro to increase cell range by 1
 

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


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69147

Macro to increase cell range by 1
 

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 Befo=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 Sub


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69147