Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.


Thanks in advance!

You would need a macro to do this.

--


Regards,


Peo Sjoblom


"BetaDocuments" wrote in message
...
Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.


Thanks in advance!

On Oct 30, 1:22*pm, "Peo Sjoblom" wrote:
You would need a macro to do this.

--

Regards,

Peo Sjoblom

"BetaDocuments" wrote in message

...



Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. *Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.

Thanks in advance!- Hide quoted text -

- Show quoted text -

Thanks! I have no idea about Macros, I guess I have to do it
manually :(

Or you may want to learn how to use macros????

If you want to try, start a new workbook.
Open the VBE (where macros live, by hitting alt-F11)
Insert|Module (on the top toolbar)
Paste this into the code window that just opened.

Option Explicit
Sub CreateAndRenameTheSheets()

Dim StartDate As Date
Dim EndDate As Date
Dim dCtr As Date
Dim wks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the correct workbook first!"
Exit Sub
End If

StartDate = Application.InputBox(Prompt:="Enter a date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=1)

'minor validity/sanity checks
If StartDate < DateSerial(2009, 1, 1) _
Or StartDate DateSerial(2011, 12, 31) Then
MsgBox "Try later"
Exit Sub
End If

'always start with the first
StartDate = DateSerial(Year(StartDate), Month(StartDate), 1)

'last day of the month with the start date
EndDate = DateSerial(Year(StartDate), Month(StartDate) + 1, 0)

With ActiveWorkbook
For dCtr = EndDate To StartDate Step -1
Select Case Weekday(dCtr)
Case vbSunday, vbSaturday
'skip it
Case Else
Set wks = Worksheets.Add
On Error Resume Next
wks.Name = Format(dCtr, "mmm dd")
If Err.Number < 0 Then
MsgBox "Rename failed with: " & wks.Name
Err.Clear
End If
On Error GoTo 0
End Select
Next dCtr
End With
End Sub


Now hit alt-f11 to get back to excel.

Save this workbook as:
WorkbookToCreateSheetsBasedOnDates.xls

Then open a test workbook--

Then hit alt-f8 and choose the macro to run.
Enter the date you want (the code will figure out the first of the month and the
last of the month and cycle through those days).

=========

Then whenever you need this to work, just open this workbook with the macro,
activate the correct workbook, hit alt-f8 and you're done!

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
BetaDocuments wrote:

On Oct 30, 1:22 pm, "Peo Sjoblom" wrote:
You would need a macro to do this.

--

Regards,

Peo Sjoblom

"BetaDocuments" wrote in message

...



Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.

Thanks in advance!- Hide quoted text -

- Show quoted text -

Thanks! I have no idea about Macros, I guess I have to do it
manually :(

--

Dave Peterson

Is there a good reason not to have all on ONE sheet and use filtering and
formulas to get reports from the data?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BetaDocuments" wrote in message
...
Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.


Thanks in advance!

Dave has given you're a great answer and Don some fine advice.
I have made a template workbook that you can use - just email me (get my
email addy from my website). It is less sophisticated than Dave's but easy
to use and alter to suit your needs.
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"BetaDocuments" wrote in message
...
Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.


Thanks in advance!

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BetaDocuments" wrote in message
...
Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.


Thanks in advance!

On Oct 31, 1:47*pm, "Don Guillett" wrote:
* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"BetaDocuments" wrote in message

...



Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. *Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.

Thanks in advance!- Hide quoted text -

- Show quoted text -

Let me try this on my own first. Thanks for such a wonderful help. I
will take this oppertunity to start learning macros now.


As always your help is much appreaciated!!

I was going to assist you in using MY suggestion of ONE worksheet. It can
and probably should be done

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BetaDocuments" wrote in message
...
On Oct 31, 1:47 pm, "Don Guillett" wrote:
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"BetaDocuments" wrote in
message

...



Hi,

I have reports that I do at work, I need one spreadsheet per month
with one work day, or 5 tabs per week. Is there any non macro way to
have this being done automatically?

So I have 1 tab each for Nov. 2, Nov. 3 .... Nov 6 then next monday,
Nov. 9 ......Nov 13.

Thanks in advance!- Hide quoted text -

- Show quoted text -

Let me try this on my own first. Thanks for such a wonderful help. I
will take this oppertunity to start learning macros now.


As always your help is much appreaciated!!