auto creating worksheets

bestie429

New Member
Joined
Aug 30, 2009
Messages
12
Hi

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.

Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

bestie429

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

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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.

Code:
'Filter and Copy Data to Separate Worksheets
'Submitted by Dennis Wallentin
'www.xldennis.com
'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
    rnData.SpecialCells(xlCellTypeVisible).Copy
    '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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,675
Messages
5,833,073
Members
430,188
Latest member
EG93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top