Code to create new workbook with sheets named after range

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
Hi all

I have been given this code and it seemed to work fine but on testing my workbook its now giving an error

In Workbook Template I have a sheet named info and the user would input a given month and then through range O2:O32 the number of days for that month would be shown.

For example is the user inputs Jul-15, which has 31 days
O2 would be populated with 1
O3 would be populated with 2
O4 would be populated with 3
and so on until ..
O32 would be populated with 31

The code below would then create a new workbook and copy the template sheet the same amount of times as the number of days for the month input by the user - so on the example if Jul-15 a new workbook would be created with 31 sheets

Then each sheet in the new workbook should be renamed to show the list of days in the range O2:O32 from the template workbook info sheet.
So again, if the month input is Jul-15 each of the 31 sheets created in the new workbook would be renamed 1 through 31.

However the code below seems to fail if the month input has a total number of days less than 31.

Code:
Dim wb As Workbook, sh1 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Info")
    Set wb = ActiveWorkbook
    Set sh2 = wb.Sheets(i - 1)
    ThisWorkbook.Sheets("Template").Copy
    For i = 2 To 32
        sh2.Name = sh1.Range("O" & i).Value
        sh2.Range("F4") = sh1.Range("K" & i).Value
        sh2.Range("E31") = sh1.Range("L" & i).Value
        sh2.Range("J31") = sh1.Range("L" & i).Value
        If wb.Sheets.Count < 32 Then
           ThisWorkbook.Sheets("Template").Copy After:=wb.Sheets(wb.Sheets.Count)
        End If
    Next

So to recap, all I would like is for the code to ...

> create a new workbook
> add to it the same number of sheets that equal the number of days for the given month (shown in cell H12 of the info) sheet
> re-name each new sheet to days of the month , ie 1,2,3,4,....31 or 1,2,3,4.....28

hope this makes sense and many thanks
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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