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

by

 

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:

[sourcecode language=”VB”] 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

[/sourcecode]

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:

Leave a Comment