New Workbook and Assign References

ChaosPup

New Member
Joined
Sep 27, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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 -

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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,214,830
Messages
6,121,834
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top