Be the first user to complete this post
|
Add to List |
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.

- 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:

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)
Also Read:
- VBA-Excel: Create and Save the Word document
- VBA-Excel: WorkBook.Save Method
- Excel-VBA : Send Mail with Embedded Image in message body From MS Outlook using Excel.
- VBA-Excel: Cells Ranges Offset - Active Cell