Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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.

Home

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

Template

  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

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

Download Link: Multi Sheets

 

Also Read:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: