 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2007Create and label worksheets
I have a form on a single worksheet. I would like to run a macro that replicates the sheet for each day of a month and uses each day's date as a sheet tab label (format: Monday 05/21/07) and puts that date on the sheet. This procedure will do it. Note that the sheet name format uses "-" instead of "/", because slashes are not allowed in a sheet name. Call it like this: AddSheets Now or if cell A1 in the active sheet contains a date AddSheets ActiveSheet.Range("A1").Value Sub AddSheets(MyDate As Date) Dim iDate As Date For iDate = DateSerial(Year(MyDate), Month(MyDate), 1) To DateSerial(Year(MyDate), Month(MyDate) + 1, 0) ActiveWorkbook.Worksheets.Add After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) With ActiveSheet With .Range("A1") .Value = iDate .NumberFormat = "dddd mm/dd/yy" End With .Name = Format(iDate, "dddd mm-dd-yy") End With Next End Sub The procedure will crash if it encounters a duplicate sheet name. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ >I have a form on a single worksheet. I would like to run a macro > that replicates the sheet for each day of a month and uses each > day's date as a sheet tab label (format: Monday 05/21/07) and puts > that date on the sheet. Just some added information: to replicate a template sheet as I believe you said you wished to do, change ActiveWorkbook.Worksheets.Add After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) to With ActiveWorkbook .Worksheets("Template").copy After:= _ ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) End With Change template to reflect the name of your "form" sheet.  SignatureRegards, Tom Ogilvy > This procedure will do it. Note that the sheet name format uses "-" instead > of "/", because slashes are not allowed in a sheet name. Call it like this: [quoted text clipped - 35 lines] > > day's date as a sheet tab label (format: Monday 05/21/07) and puts > > that date on the sheet. Oh yeah, I missed that. Thanks, Tom. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > Just some added information: > to replicate a template sheet as I believe you said you wished to do, [quoted text clipped - 53 lines] >> > day's date as a sheet tab label (format: Monday 05/21/07) and puts >> > that date on the sheet. On May 26, 8:22 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> wrote: > Oh yeah, I missed that. Thanks, Tom. > [quoted text clipped - 68 lines] > > - Show quoted text - Thanks for the code. I have created the procedure but for some reason I cannot see it from the worksheet. It won't even run from the code module when I press F5. The Macro dialog box appears and when I enter the name of the macro it starts a module to create it. What gives? The macro starts as Sub AddSheets(MyDate As Date) which means you must enter a date. This gives you the flexibility of creating next months sheets ahead of time, or backtracking and creating a previous month's sheets. As I said in my initial post Call it like this: AddSheets Now or if cell A1 in the active sheet contains a date AddSheets ActiveSheet.Range("A1").Value Either of these lines that call the sub can be inserted into an existing VBA procedure or run from the Immediate Window. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > On May 26, 8:22 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> > wrote: [quoted text clipped - 75 lines] > module when I press F5. The Macro dialog box appears and when I enter > the name of the macro it starts a module to create it. What gives? On May 28, 9:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> wrote: > The macro starts as > [quoted text clipped - 111 lines] > > - Show quoted text - Thanks very much to Jon and Tom. Problem solved. I learned something very important here. I had been told before that a procedure that takes an argument won't show up in the Macro list but could be run by typing its name in the Macro dialog box or by assigning it to a command button (how if it doesn't show?) - neither of which worked for me. It seems that such a procedure has to be called from another procedure (which can be assigned to a button). I couldn't run it from the Immediate window either, though. Thanks again for the help. What error came up when you tried it from the Immediate Window? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > On May 28, 9:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> > wrote: [quoted text clipped - 127 lines] > procedure (which can be assigned to a button). I couldn't run it from > the Immediate window either, though. Thanks again for the help. Sub mynewsheets() For i = 30 To 1 Step -1 On Error Resume Next Sheets.Add.Name = i Next i End Sub SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I have a form on a single worksheet. I would like to run a macro > that replicates the sheet for each day of a month and uses each > day's date as a sheet tab label (format: Monday 05/21/07) and puts > that date on the sheet. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.