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.
![Home](/static/media/excel/2015/09/Home.jpg)
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](/static/media/excel/2015/09/Template.jpg)
- 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](/static/media/excel/2015/09/Add-Worksheets-For-All-The-Given-Dates-Except-Weekends-and-Copy-The-Common-Template-In-Each-Worksheet.jpg)
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: Open an Existing Word Document
- VBA-Excel: Create worksheets with Names in Specific Format/Pattern.
- VBA-Excel: Get the Instance of already opened word document
- VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order