Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o> </o>
I am trying to teach myself VBA for Excel, but I am having some issues with the correct syntax for the loop I am try to do, or figuring out if I should do a loop at all. I have created loops before that insert formulas or do calculations on the Active worksheet until the last cell, but this case is a bit different and I would appreciate any help that anyone can offer.<o></o>
<o> </o>
Basically, what I would like to do is insert a value from Worksheet2, column A into cell A4 in Worksheet1, copy Worksheet1 to a new book, copy paste the values, save the document with the value in A4 plus the date and close the copied document. I would like to do this for every cell that has a value in Worksheet2 from column A, and then stop.<o></o>
<o> </o>
Here is the code I have so far:<o></o>
<o> </o>
Sub Macro_Test()<o></o>
'<o></o>
' Macro_Test Macro<o></o>
'<o></o>
' Keyboard Shortcut: Ctrl+Shift+Q<o></o>
'<o></o>
Sheets("Sheet1").Select<o></o>
Range("A4").Select<o></o>
ActiveCell.FormulaR1C1 = "=Worksheet2!R[-2]C" (this is the part I need to loop)<o></o>
Range("A4").Select<o></o>
Sheets("Sheet1").Select<o></o>
Sheets("Sheet1").Copy<o></o>
Cells.Select<o></o>
Selection.Copy<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Range("A4").Select<o></o>
Dim newFile As String, fName As String<o></o>
fName = Range("A4").Value<o></o>
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")<o></o>
ChDir _<o></o>
"C:\Documents and Settings\pink705\Desktop"<o></o>
ActiveWorkbook.SaveAs Filename:=newFile<o></o>
ActiveWindow.Close<o></o>
End Sub<o></o>
<o> </o>
I have managed to code everything except for the loop part; I can’t seem to figure out how to make it reference "=Worksheet2!R[-2]C" and do all of the code, then "=Worksheet2!R[-1]C" and do all of the code, etc.<o></o>
<o> </o>
Any help anyone could offer would be great.<o></o>
<o></o>
<o>I am using Excel 2007, Windows XP </o>
Thanks.<o></o>
fficeffice" /><o></o><o
> </o>I am trying to teach myself VBA for Excel, but I am having some issues with the correct syntax for the loop I am try to do, or figuring out if I should do a loop at all. I have created loops before that insert formulas or do calculations on the Active worksheet until the last cell, but this case is a bit different and I would appreciate any help that anyone can offer.<o
></o><o
> </o>Basically, what I would like to do is insert a value from Worksheet2, column A into cell A4 in Worksheet1, copy Worksheet1 to a new book, copy paste the values, save the document with the value in A4 plus the date and close the copied document. I would like to do this for every cell that has a value in Worksheet2 from column A, and then stop.<o
></o><o
> </o>Here is the code I have so far:<o
></o><o
> </o>Sub Macro_Test()<o
></o>'<o
></o>' Macro_Test Macro<o
></o>'<o
></o>' Keyboard Shortcut: Ctrl+Shift+Q<o
></o>'<o
></o>Sheets("Sheet1").Select<o
></o>Range("A4").Select<o
></o>ActiveCell.FormulaR1C1 = "=Worksheet2!R[-2]C" (this is the part I need to loop)<o
></o>Range("A4").Select<o
></o>Sheets("Sheet1").Select<o
></o>Sheets("Sheet1").Copy<o
></o>Cells.Select<o
></o>Selection.Copy<o
></o>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o
></o>:=False, Transpose:=False<o
></o>Range("A4").Select<o
></o>Dim newFile As String, fName As String<o
></o>fName = Range("A4").Value<o
></o>newFile = fName & " " & Format$(Date, "mm-dd-yyyy")<o
></o>ChDir _<o
></o>"C:\Documents and Settings\pink705\Desktop"<o
></o>ActiveWorkbook.SaveAs Filename:=newFile<o
></o>ActiveWindow.Close<o
></o>End Sub<o
></o><o
> </o>I have managed to code everything except for the loop part; I can’t seem to figure out how to make it reference "=Worksheet2!R[-2]C" and do all of the code, then "=Worksheet2!R[-1]C" and do all of the code, etc.<o
></o><o
> </o>Any help anyone could offer would be great.<o
></o><o
></o> <o
>I am using Excel 2007, Windows XP </o>Thanks.<o
></o>
