VBA Create Multiple Worksheets From A List Of Cell Values

FETulsa

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 2010
I tried using the below VBA code, it is creating "nameless" worksheets, so sheet1 , 2 , 3 and so on, rather than using the value in the cell as the sheet's name. Any tips to resolve this issue?

Sub AddSheets()

Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("A1:A7")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Do those sheets already exist? Do the values in A1:A7 contain characters that are forbidden in sheet names?
 

FETulsa

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 2010
Do those sheets already exist? Do the values in A1:A7 contain characters that are forbidden in sheet names?
OMG! You are a genius JoeMo!!! What a simple fix. Yes the cell names had slashes and dashes -/. I so did not want to do the whole copy paste thing, I had (49) new sheets I wanted to create. Thank you! Thank you! Thank you!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OMG! You are a genius JoeMo!!! What a simple fix. Yes the cell names had slashes and dashes -/. I so did not want to do the whole copy paste thing, I had (49) new sheets I wanted to create. Thank you! Thank you! Thank you!
You are welcome - thanks for the reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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