Loop thru Rows

[Jenawade]

This form (below) has some links to row 2 of our data sheet and is saved as 2011-250.xlsm. This is our 2nd out of 4 or 5 batch runs. I manually select rows 4-11 of this form, use the find/replace to change $2 to $3 for the links, save this as the next form number (2011-251.xlsm), then do it again through the end of the data. The data runs from row 2 through row 102, form 2011-250 through 2011-350. I know a macro could whip right through these in no time. If someone wouldn't mind putting one together for me I would be grateful!

Excel Workbook
	B	C	D	E	F
1	*
3		*		DATE:	=TODAY()
4	*		='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$AJ$2	CONTROL #	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$AO$2
5	*	*	*	SITE	12
6	COMPONENT P/N	KIT P/N	CUSTOMER P/N	CM or PM	PART DESCRIPTION
7	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$B$2	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$AK$2	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$AL$2	PM	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$E$2
8	CYCLE COUNTED QUANTITY	WEIGHT (lbs) / Unit *	* *$PER LB	Scrap recovery/unit	Product Line
9	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$F$2		0.03	0	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$AM$2
10	*	UNIT COST	RESERVE	TOTAL INVENTORY COST
11	*	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$G$2	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$K$2	='[SITE 12 SPACE PHASE II.xlsm]101 FORD RFMD''s'!$H$2
RFMD Form

 

[Jerry Sullivan]

Hi Jen,

If I'm understanding you correctly, you only need to change $2 to $3 in your Active Workbook,
then make 100 copies changing the file name for each.

If that is correct, then I'd suggest 2 separate macros:
The first to change the Row Number for your next batch and
the second to make the copies using SaveAs.

Sub Next_Batch_Run()
    With Sheets("RFMD Form").Rows("4:11")
       .Replace What:="$2", Replacement:="$3", LookAt:=xlPart
    End With
End Sub

Sub Make_Forms()
    Dim lngFileNr As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    For lngFileNr = 250 To 350
        ActiveWorkbook.SaveAs Filename:="2011-" & lngFileNr & ".xlsm", _
             FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Next lngFileNr
    Application.DisplayAlerts = True
End Sub

This approach lets you double-check that your Row Numbers were successfully changed
prior to making the committment to 100 copies!

You can also make variations of Next_Batch_Run which would change $1 to $2, $3 to $4 etc.

 

[Jenawade]

Hi Jen,

If I'm understanding you correctly, you only need to change $2 to $3 in your Active Workbook,
then make 100 copies changing the file name for each.

If that is correct, then I'd suggest 2 separate macros:
.

Thanks for the reply! I need to change the row number in the formulas each time as well as save as. So row $2 is saved as 2011-250, then I need to change the instances of $2 in the formulas to $3 and save as 2011-251, change them to $4 and save as 2011-252, to $5 and 2011-253, etc, through row $102 and file name 2011-350.

 

[Jerry Sullivan]

Thanks for the clarification - I totally misunderstood that.

Try....

Sub Make_Batch_Forms()
    Dim lngRowRef As Long, lngFileNr As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    With Sheets("RFMD Form").Rows("4:11")
        For lngRowRef = 2 To 102
            lngFileNr = lngRowRef + 248
            ActiveWorkbook.SaveAs Filename:="2011-" & lngFileNr & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
           .Replace What:="$" & lngRowRef, Replacement:="$" & lngRowRef + 1, _
                LookAt:=xlPart
         Next lngRowRef
    End With
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

 

[Jenawade]

Try....

Oh thank you thank you thank!!! You have just saved me countless hours of work; it works perfectly!