Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Tabs
Is there any way I can create a new tab for every day of the month and name
that tab whatever the day of the month it is...ie Tab 1 will be named "1" (for day 1) and Tab 2 will be name "2" (for day 2) and so on and so forth. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Tabs
Is there a macro code that can automatically create the tabs based on the
number of days of the month? "David T" wrote: Is there any way I can create a new tab for every day of the month and name that tab whatever the day of the month it is...ie Tab 1 will be named "1" (for day 1) and Tab 2 will be name "2" (for day 2) and so on and so forth. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Tabs
Here is another version
I have a working workbook if you would like me to e-mail you Sub NameTheTabTodayAndNext31Days() Dim dayofmonth As Integer Dim movesheet As Integer Application.ScreenUpdating = False 'speeds macro up Application.DisplayAlerts = False 'turn alerts off for know 'Start by looking at the date we enter in Cell A1 Range("A1").Select 'Then we need to take the date in cell A1 and copy downn column Selection.AutoFill Destination:=Range("A1:A31"), Type:=xlFillDefault Range("A1:A31").Select ' Now that we have are dates we need to add a workbook ' this will keep this workbook clean Workbooks.Add On Error GoTo Errhandler 'this will be used in macro to move the sheet to the right movesheet = 4 'add a sheet to the new Workbook Sheets.Add 'now lets name the sheet based on ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e 'and move the sheet to the right 4 & movesheet = movesheet + 1 'the rest of the code is the same as this just repeat ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A1").Val ue(), "[$-409]dddd mmmm dd,yyyy;@") '1 ActiveSheet.Move After:=Sheets(movesheet) ' movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A2").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '2 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A3").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '3 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A4").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '4 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A5").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '5 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A6").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '6 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A7").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '7 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A8").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '8 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A9").Val ue(), "[$-409]dddd mmmm dd, yyyy;@") '9 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A10").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '10 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A11").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '11 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A12").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '12 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A13").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '13 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A14").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '14 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A15").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '15 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A16").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '16 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A17").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '17 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A18").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '18 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A19").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '19 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A20").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '20 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A21").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '21 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A22").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '22 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A23").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '23 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A24").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '24 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A25").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '25 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A26").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '26 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A27").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '27 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A28").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '28 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A29").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '29 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A30").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '30 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets.Add ActiveSheet.Name = Format _ (ThisWorkbook.Worksheets("Sheet1").Range("A31").Va lue(), "[$-409]dddd mmmm dd, yyyy;@") '31 ActiveSheet.Move After:=Sheets(movesheet) movesheet = movesheet + 1 Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select ActiveWindow.SelectedSheets.Delete ThisWorkbook.Activate ThisWorkbook.Worksheets("Sheet1").Range("A2:A31"). Select Selection.ClearContents Range("A1").Select Range("A2:A31").Select Selection.ClearContents Range("A1").Select ActiveWindow.WindowState = xlMinimized ActiveWindow.WindowState = xlMaximized Application.DisplayAlerts = True Application.ScreenUpdating = True Errhandler: If Err < 0 Then MsgBox "Error # " & Err & " : " & Error(Err) ActiveSheet.Delete End If End Sub "David T" wrote: Is there a macro code that can automatically create the tabs based on the number of days of the month? "David T" wrote: Is there any way I can create a new tab for every day of the month and name that tab whatever the day of the month it is...ie Tab 1 will be named "1" (for day 1) and Tab 2 will be name "2" (for day 2) and so on and so forth. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Tabs
One mo
Option Explicit Sub testme() Dim dCtr As Long Dim myDate As Date myDate = Date 'today 'or specify the month to generate 'myDate = DateSerial(2005, 12, 1) 'dec 2005 For dCtr = Day(DateSerial(Year(myDate), Month(myDate) + 1, 0)) To 1 Step -1 Worksheets.Add.Name = dCtr Next dCtr End Sub David T wrote: Is there any way I can create a new tab for every day of the month and name that tab whatever the day of the month it is...ie Tab 1 will be named "1" (for day 1) and Tab 2 will be name "2" (for day 2) and so on and so forth. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Tabs | Excel Discussion (Misc queries) | |||
Create Tabs | Excel Discussion (Misc queries) | |||
create view tabs | Excel Worksheet Functions | |||
Create tabs named after a group of cells? | Excel Discussion (Misc queries) | |||
How can Excel create a list of tabs on a worksheet? | Excel Discussion (Misc queries) |