Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Macro to Increase Cell Reference | Excel Discussion (Misc queries) | |||
How to Create Macro to Increase Cell Value to one and Print Worksheet | Excel Worksheet Functions | |||
How do I write a macro to increase the value of any cell by 1? | Excel Programming | |||
Number in cell increase with increase in font size. | Excel Discussion (Misc queries) | |||
Macro to increase cell number by 1 at a time | Excel Programming |