VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

Download Link: Multi Sheets

Objective: This tool is very useful. Many times we encounter a situation where we need to maintain a report on the daily basis (For weekdays). We manually create a worksheet for each day and copy the template from previous sheets and edit it.

This tool will automate all the manual work I have described. Code is very simple, you can modify the code as per your needs.

How to use it:

Download from the link given at the start and end of the article.

Put all the days in the “Main” Sheet. You can drag for putting the continuous dates.

Put the template you want to be pasted in each of the worksheet. Leave it blank if you want blank sheets to be created.

1. That’s it, You are good to go. Just press the generate button and your sheets will be created with template in each sheet.

How it is working:

Read “Create or Add Worksheets at the Run time” and “Copy the Entire data from one sheet to another”

Complete Code:

Sub Sumit()

Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook

For i = 1 To 100
    strVal = mainWorkBook.Sheets("Main").Range("A" & i)
    strDay = Format(strVal, "dddd")
    If strVal <> "" And strDay <> "Saturday" And strDay <> "Sunday" Then

        mainWorkBook.Worksheets.Add().Name = Format(strVal, "DD-MMM-YYYY")
    End If
Next

For i = 1 To mainWorkBook.Sheets.Count
        If mainWorkBook.Sheets(i).Name <> "Main" And mainWorkBook.Sheets(i).Name <> "Data" Then
            mainWorkBook.Sheets("Data").UsedRange.Copy
            mainWorkBook.Sheets(i).Paste
        End If
Next i

End Sub

Output:

Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

Download Link: Multi Sheets

Also Read:

• Create worksheets with Names in Specific Format/Pattern.
• Create or Add Worksheets at the Run time.
• Get the names of all WorkSheets in a Excel (WorkBook)