Creating New Workbooks from A Range

ludlow

Board Regular
Joined
Mar 12, 2013
Messages
87
Greetings,
I found myself needing assistance once again. I have a workbook where the user is able to create multiple copies of one worksheet (master), which is based from a range (Emp_Names). The range is located on the Homepage worksheet. The issue I am having has to do with creating individual workbooks for each newly created worksheet (Emp_Names) and saving them as based off the employee name that was entered in the range (Emp_Names). Any assistance would be greatly appreciated.

Here is the first macro used to create the new worksheets...
Code:
Sub CopySheets()
Dim FinalRow As Long
Dim WsEmp As Worksheet


Set WsEmp = Worksheets("Homepage")
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row


    Application.ScreenUpdating = False
        '-----------------------------------------------------------
        'This section add the number of EMPL worksheets as needed
        '-----------------------------------------------------------
        
        Dim sh As Worksheet
        Dim tabname As Range
            
        For Each tabname In Sheets("Homepage").Range("Emp_Names")
        
            If tabname.Value <> "" Then
            
            Set sh = Nothing
            On Error Resume Next
            Set sh = Worksheets(tabname.Value)
            On Error GoTo 0
            If sh Is Nothing Then
            
            Worksheets("MASTER").Copy After:=Worksheets("master")
            ActiveSheet.Name = tabname.Value
            End If
        End If
        Next tabname
        Worksheets("Homepage").Activate
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

ludlow

Board Regular
Joined
Mar 12, 2013
Messages
87
I was able to figure it out. The code is below in case someone needs it in the future.

Code:
Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String


    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        If ws.Name <> ActiveSheet.Name And ws.Name <> "Master" Then
           strFilename = wbThis.Path & "/" & ws.Name
           ws.Copy
           Set wbNew = ActiveWorkbook
           wbNew.SaveAs strFilename
           wbNew.Close
         End If
    Next ws
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,256
Messages
5,600,556
Members
414,388
Latest member
Pkmep4

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
Top