I have an excel problem related to running the same macro multiple times for data with one of the references changing each time.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o> </o>
I need the macro to take a series of values from one spreadsheet (references G2, H2 and F2-F8), paste them into a second spreadsheet and also enter a couple of values into the second spreadsheet, then copy data from the second spreadsheet and paste it into the first (references E2, I2). The macro I have built successfully does this, but I then need the macro to be repeated, but with the references from the first spreadsheet changed to 7 rows further down (G9, H9, F9-15 then pasting into E9, I9) with the second spreadsheet references staying the same. This then needs to be repeated around 150 times, though each time it is run it will need to be run a different number of times. Is there a way of getting a macro to loop so that it changes one set of references, keeps the second set the same and keeps looping until it reaches the final row? (i.e. the row it reaches is blank)
Here's the code I have for the macro that currently works for the first set of data:
Sub Macro1()<o></o>
'<o></o>
Range("C16").Select<o></o>
ActiveCell.FormulaR1C1 = "N"<o></o>
Range("C17").Select<o></o>
Windows("Remodeling.xls").Activate<o></o>
Range("G2").Select<o></o>
Selection.Copy<o></o>
Windows("New Housing Model Transportation only.xls").Activate<o></o>
Range("C29").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Windows("Remodeling.xls").Activate<o></o>
Range("F2:F8").Select<o></o>
Application.CutCopyMode = False<o></o>
Selection.Copy<o></o>
Windows("New Housing Model Transportation only.xls").Activate<o></o>
Range("C34").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Sheets("Base").Select<o></o>
Application.CutCopyMode = False<o></o>
Selection.Copy<o></o>
Windows("Remodeling.xls").Activate<o></o>
Range("E2").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Selection.End(xlToRight).Select<o></o>
Application.CutCopyMode = False<o></o>
Selection.Copy<o></o>
Windows("New Housing Model Transportation only.xls").Activate<o></o>
Sheets("Summary").Select<o></o>
Range("H48").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Range("C16").Select<o></o>
Application.CutCopyMode = False<o></o>
ActiveCell.FormulaR1C1 = "Y"<o></o>
With Application<o></o>
.Iteration = True<o></o>
.MaxChange = 0.00001<o></o>
Range("c48").Goalseek Goal:=Range("H48"), ChangingCell:=Range("c47")<o></o>
Range("C54").Select<o></o>
Selection.Copy<o></o>
Windows("Remodeling.xls").Activate<o></o>
Range("I2").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Windows("New Housing Model Transportation only.xls").Activate<o></o>
End With<o></o>
End Sub<o></o>
<o></o>
<o></o>
<o>Thanks in advance for taking the time to look at this.</o>
fficeffice" /><o></o><o
> </o>I need the macro to take a series of values from one spreadsheet (references G2, H2 and F2-F8), paste them into a second spreadsheet and also enter a couple of values into the second spreadsheet, then copy data from the second spreadsheet and paste it into the first (references E2, I2). The macro I have built successfully does this, but I then need the macro to be repeated, but with the references from the first spreadsheet changed to 7 rows further down (G9, H9, F9-15 then pasting into E9, I9) with the second spreadsheet references staying the same. This then needs to be repeated around 150 times, though each time it is run it will need to be run a different number of times. Is there a way of getting a macro to loop so that it changes one set of references, keeps the second set the same and keeps looping until it reaches the final row? (i.e. the row it reaches is blank)
Here's the code I have for the macro that currently works for the first set of data:
Sub Macro1()<o
></o>'<o
></o>Range("C16").Select<o
></o>ActiveCell.FormulaR1C1 = "N"<o
></o>Range("C17").Select<o
></o>Windows("Remodeling.xls").Activate<o
></o>Range("G2").Select<o
></o>Selection.Copy<o
></o>Windows("New Housing Model Transportation only.xls").Activate<o
></o>Range("C29").Select<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Windows("Remodeling.xls").Activate<o
></o>Range("F2:F8").Select<o
></o>Application.CutCopyMode = False<o
></o>Selection.Copy<o
></o>Windows("New Housing Model Transportation only.xls").Activate<o
></o>Range("C34").Select<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Sheets("Base").Select<o
></o>Application.CutCopyMode = False<o
></o>Selection.Copy<o
></o>Windows("Remodeling.xls").Activate<o
></o>Range("E2").Select<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Selection.End(xlToRight).Select<o
></o>Application.CutCopyMode = False<o
></o>Selection.Copy<o
></o>Windows("New Housing Model Transportation only.xls").Activate<o
></o>Sheets("Summary").Select<o
></o>Range("H48").Select<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Range("C16").Select<o
></o>Application.CutCopyMode = False<o
></o>ActiveCell.FormulaR1C1 = "Y"<o
></o>With Application<o
></o>.Iteration = True<o
></o>.MaxChange = 0.00001<o
></o>Range("c48").Goalseek Goal:=Range("H48"), ChangingCell:=Range("c47")<o
></o>Range("C54").Select<o
></o>Selection.Copy<o
></o>Windows("Remodeling.xls").Activate<o
></o>Range("I2").Select<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Windows("New Housing Model Transportation only.xls").Activate<o
></o>End With<o
></o>End Sub<o
></o><o
></o> <o
></o> <o
>Thanks in advance for taking the time to look at this.</o>
