Hi everyone, I've written a macro to create a user defined number of new worksheets. What I'd like to do next is to have it copy successive entries in a column (I'm starting at F8) into the I3 cell in successive new sheets (i.e., contents of F8 to I3 in first new sheet, contents of F9 to I3 in second new sheet and so on). The number of entries in the column is variable but will always match the number of sheets, and can consist of numbers, letters and symbols. I've tried a few things, but all I can manage so far is getting it to put F8 in I3 of every sheet rather than working down the column. I've put my code below - any help greatly appreciated!!
Sub COPYFAT()
Sub COPYFAT()
VBA Code:
Application.DisplayAlerts = False 'Turns OFF error reporting overwrites file without prompting
Application.ScreenUpdating = False
'Varible declarations
Dim UnitNum As Variant 'Number of units
Dim SheetName As Variant 'Name of sheet
Dim TagNum As Variant
Dim I As Integer 'Counter
Dim Start As Integer 'First SN
Dim Increment As Integer 'SN+1
Dim xNumber As Integer
Dim xName As String
Dim xActiveSheet As Worksheet
Start = Sheets("Control").Range("B10")
UnitNum = Sheets("Control").Range("B9").Value - 1 'Number of units pulled from CELL B23 on control sheet ***-1 because index starts at ZERO***
Worksheets(Range("B8").Value).Activate 'Name of Sheet to copy from CELL B8 on control sheet
Set xActiveSheet = ActiveSheet
For I = 0 To UnitNum 'Planning to add this number to cell reference for name starts at zero so initial reference is correct
Increment = Start + I
TagNum = Sheets("Control").Range("F8")
xName = ActiveSheet.Name
xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
ActiveSheet.Name = Sheets("Control").Range("B12") & "-" & Increment
ActiveSheet.Range("A1") = Sheets("Control").Range("B12") & " Factory Acceptance Test Report"
ActiveSheet.Range("I4") = Sheets("Control").Range("B8") & "-" & Increment
ActiveSheet.Range("I2") = Sheets("Control").Range("B12")
ActiveSheet.Range("I5") = Sheets("Control").Range("B13")
ActiveSheet.Range("I6") = Sheets("Control").Range("B14")
ActiveSheet.Range("I7") = Sheets("Control").Range("B15")
ActiveSheet.Range("J10") = Sheets("Control").Range("B17")
ActiveSheet.Range("J12") = Sheets("Control").Range("B18")
ActiveSheet.Range("N14") = Sheets("Control").Range("B19")
ActiveSheet.Range("O32") = Sheets("Control").Range("B20")
ActiveSheet.Range("D41") = Sheets("Control").Range("B21")
ActiveSheet.Range("D43") = Sheets("Control").Range("B15")
ActiveSheet.Range("I3") = TagNum
Next
xActiveSheet.Activate
Sheets("CONTROL").Activate
Application.DisplayAlerts = True 'Turns ON error reporting
Application.ScreenUpdating = True
End Sub