Hi everyone, I'm working on a project just now that I'm using to create a number of new worksheets named and populated from user entered data on a different sheet. I've got it working great so far with this -
There are 2 more things I'd like it to do - instead of creating the new sheets in the current workbook containing the template, I'd prefer it to create them in a new workbook that is then named and saved. I've implemented this in a different project creating word documents, but for some reason I'm struggling to do it here. The other things is, each sheet is named after a serial number, and each serial number has a corresponding reference number (which are client supplied and be any mix of numbers, letters and symbols). I'd like to list the reference numbers in the order they are fitted to serial numbers (which I'll copy/paste from a another sheet I'm given), then have the macro populate each sheet with the relevant reference number. I'm not sure where to start with this one! Any advice would be most welcome. Thanks!
VBA Code:
Sub COPYFAT()
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 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
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("K10") = Sheets("Control").Range("B17")
Next
xActiveSheet.Activate
Application.DisplayAlerts = True 'Turns ON error reporting
Application.ScreenUpdating = True
End Sub
There are 2 more things I'd like it to do - instead of creating the new sheets in the current workbook containing the template, I'd prefer it to create them in a new workbook that is then named and saved. I've implemented this in a different project creating word documents, but for some reason I'm struggling to do it here. The other things is, each sheet is named after a serial number, and each serial number has a corresponding reference number (which are client supplied and be any mix of numbers, letters and symbols). I'd like to list the reference numbers in the order they are fitted to serial numbers (which I'll copy/paste from a another sheet I'm given), then have the macro populate each sheet with the relevant reference number. I'm not sure where to start with this one! Any advice would be most welcome. Thanks!