auto creating worksheets


New Member
Aug 30, 2009

I have a table that has a series of values in three columns,

A - date
B - time1
C - time2

I want to run a script that will take each date and create a separate worksheet for each value in column A (so 365 worksheets for a year) and then paste the three items to specific cells into a blank template. Date to B3, time1 to B5 and time2 to B7, then save the worksheet using the date as the name.


Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN


New Member
Aug 30, 2009
I just found excel explosion, that seems to be what I am looking for, thanks

John Davis

Well-known Member
Sep 11, 2007
Excel Explosion doesn't seem to do it, so any help much appreciated

Here's an excerpt from Visual Basics and Macro's 2007 by Jelen that sounds like it would help you or at least get you started.

'Filter and Copy Data to Separate Worksheets
'Submitted by Dennis Wallentin
'This sample uses a specified column to filter data and copies the results to new worksheets
'in the active workbook.
Sub Filter_NewSheet()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range, rnData As Range
Dim i As Long

Set wbBook = ThisWorkbook
Set wsSheet = wb.Worksheets("Sheet1")

With wsSheet
    'make sure that the first row contains headings.
    Set rnStart = .Range("A2")
    Set rnData = .Range(.Range("A2"), Cells(.Rows.Count, 3).End(xlUp))
End With

Application.ScreenUpdating = True

For i = 1 To 5
    'here we filter the data with the first criterion
    rnStart.AutoFilter Field:=1, Criteria1:="AA" & i
    'Copy the filtered list
    'add a new worksheet to the active workbook.
    Worksheets.Add Before:=wsSheet
    'Name the added new worksheets.
    ActiveSheet.Name = "AA" & i
    'Paste the filtered list.
    Range("A2").PasteSpecial xlPasteValues
Next i

'Reset the list to its original status.
rnStart.AutoFilter Field:=1

With Application
    'Rest the clipboard.
    .CutCopyMode = False
    .ScreenUpdating = False
End With

End Sub

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...