Copy Worksheet Several Times To New Workbook And Rename New Sheets According To Array List

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code:

Code:
    arrNames = Array("MASTER", "EVL", "EVE", "LWP", "WPL", "WPE", "RPL", "RPE", "HPL", "HPE", "BPL", "BPE", "CUL", "CUE2", "CUE1", "CWP", "CRP", "LSP")
    
    For i = 0 To 17
        Set sh = Nothing
        On Error Resume Next
        Set sh = ThisWorkbook.Sheets("Master")
        On Error GoTo 0
        If Not sh Is Nothing Then
            sh.Copy After:=wb_daily.Sheets(1)
            'sheets are hidden
        End If
    Next i

I'm almost where I want to be. The thing I'm struggling with, and am here to ask, is how can I name each new sheet to the new workbook according the the names in the array?

for example, copy ThisWorkbook("Master") to new workbook and keep the name, then copy ThisWorkbook("Master") to new workbook and rename it "EVL" and so on until the last sheet is copied and renamed "LSP"
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Code:
For i = 0 To 17
        shnm = arrNames(i)
        Debug.Print shnm
        Set ssh = Nothing
        On Error Resume Next
        Set ssh = ThisWorkbook.Sheets("Master")
        On Error GoTo 0
        If Not ssh Is Nothing Then
            ssh.Copy After:=wb_daily.Sheets(1)
            ActiveSheet.Name = shnm
            'sheets are hidden
        End If
    Next i
 
Upvote 0
Solution

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

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