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

 

Down­load Link: Multi Sheets

Objec­tive: This tool is very use­ful. Many times we encounter a sit­u­a­tion where we need to main­tain a report on the daily basis (For week­days). We man­u­ally cre­ate a work­sheet for each day and copy the tem­plate from pre­vi­ous sheets and edit it.

This tool will auto­mate all the man­ual work I have described. Code is very sim­ple, you can mod­ify the code as per your needs.

How to use it:

Down­load 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 con­tin­u­ous dates.

Home

Put the tem­plate you want to be pasted in each of the work­sheet. Leave it blank if you want blank sheets to be created.

Template

  1. That’s it, You are good to go. Just press the gen­er­ate but­ton and your sheets will be cre­ated with tem­plate in each sheet.

How it is working:

Read “Cre­ate or Add Work­sheets at the Run time” and “Copy the Entire data from one sheet to another

Com­plete 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

Out­put:

 

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

Add Work­sheets For All The Given Dates Except Week­ends and Copy The Com­mon Tem­plate In Each Worksheet

Down­load 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: