Make code interactive according to month 5

[netrusher]

Below is the code I have obtained that will copy and save the January Sheet in a workbook. I have 12 sheets
in this workbook, one for every month. Is there a way to automate this code so it will send the sheet based
on what month it currently is? This would be a big help. If the month is March, I would like the code, when ran
to automatically select the correct month (sheet) based on the date.

Sub SaveSingleSheet()
Sheets("January").Copy
ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJJanuary.xls"
ActiveWorkbook.Close
End Sub

 

[SkipVought]

Hi,

Sub SaveSingleSheet()
Sheets("January").Copy
ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You might want to do something like this...

dim ws as worksheet
for each ws in worksheets
  ws.Copy
  ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & ws.Name & ".xls"
  ActiveWorkbook.Close
next

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip,
Thanks for your help. Will this allow me to select by month?
I mean if it is February 3 when the January report is ready
how can I get this to go to the January worksheet? Is this
possible?

 

[SkipVought]

Sorry, I assumed you were saving all months.

1. Make a cell for LastSaved and NAME THE RANGE. In it store the YEAR and Month like 200812

2. Use code like this...

if [LastSaved] then
  SaveSingleSheet Format(DateSerial(year(date),month(date)-1,1),"mmmm")
  [LastSaved] = Format(DateSerial(year(date),month(date)-1,1),"yyyymm")
end if

modify your macro...

Sub SaveSingleSheet(sName as string)
  Sheets(sName).Copy
  ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & ActiveSheet.Name & ".xls"
  ActiveWorkbook.Close
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip,

Thanks again. I need to somehow have the code automatically
know the month or last month and then pick the sheet with
the same name as the month. Can this be done?

 

[SkipVought]

Did you try my last post?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip,

I got this suggestion from another location.

First of all, create a hidden tab (call it "Parameters" or whatever you want) with a field that contains the following formula:

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmmm")

What this formula does is get you the name of the previous month.

Give this field a name: MonthName.

I have the formula in a cell named MonthName on a sheet that I have named CODE.

Now I am getting the following error with the code below. I am stumped!

Would I be asking too much for you to take a look at this.
This gives me the last month in the field created.

Run-time error '1004'
Method 'Range' of object '_Global' failed

Sub SaveSingleSheet()
Sheets(Range("MonthName")).Copy
ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & Range("MonthName") & ".xls"
ActiveWorkbook.Close
End Sub

 

[SkipVought]

My LastSaved is EXACTLY what your formula does and this suggestion give exactly the same result.

I did make a mistake by not finishing the first statement...

if [LastSaved] < Format(DateSerial(year(date),month(date)-1,1),"yyyymm") then
  SaveSingleSheet Format(DateSerial(year(date),month(date)-1,1),"mmmm")
  [LastSaved] = Format(DateSerial(year(date),month(date)-1,1),"yyyymm")
end if

Sub SaveSingleSheet(sName as string)
  Sheets(sName).Copy
  ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & ActiveSheet.Name & ".xls"
  ActiveWorkbook.Close
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip,

Thanks again,

Where do I put this code

if [LastSaved] < Format(DateSerial(year(date),month(date)-1,1),"yyyymm") then
  SaveSingleSheet Format(DateSerial(year(date),month(date)-1,1),"mmmm")
  [LastSaved] = Format(DateSerial(year(date),month(date)-1,1),"yyyymm")
end if

Does this go in a cell?? I am confuses concerning this but
I appreciate your patience and help.

Also, I am not sure what you mean by the statement below.
What is the purpose of storing the year & month?
How does this tie in with the code.
1. Make a cell for LastSaved and NAME THE RANGE. In it store the YEAR and Month like 200812.

 

[netrusher]

Skip,

This is the code I am currently using and it works.
Now my boss tells me he wants all 12 months to get saved automatically. I can do that with the code below
but my issue is he only wants the sheets for the months
Jan-Dec. Some of the workbooks have other sheets. How can I
limit what is saved to only the sheets with the Months?

Sub SaveSingleSheet()
Dim MyPath As String
MyPath = "s:\eng\Attendance_Vacation\2009" ' Change this to meet your needs
'Sheets(Range("MonthName").Value).Copy
'Cells.Copy
ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & "TimeSheet" & ".xls"
'ActiveWorkbook.SaveAs Filename:="s:\eng\Attendance_Vacation\2009\AJ" & Range("MonthName").Value & ".xls"
ActiveWorkbook.Close
End Sub

 

[SkipVought]

select case Range(MonthName).Value
  case "January","February","March"  'include all 12 month
    SaveSingleSheet(Range(MonthName).Value)
end select

.........

Sub SaveSingleSheet(sMon as string)
Dim MyPath As String
MyPath = "s:\eng\Attendance_Vacation\2009" ' Change this to meet your needs
Sheets(sMon).Copy
'Cells.Copy
ActiveWorkbook.SaveAs Filename:=MyPath & "\AJTimeSheet.xls"
ActiveWorkbook.SaveAs Filename:=MyPath & "\AJ" & sMon  & ".xls"
ActiveWorkbook.Close
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Thanks Skip,

I am totally new with VBA for Excel.
Where does this code go?

select case Range(MonthName).Value
  case "January","February","March"  'include all 12 month
    SaveSingleSheet(Range(MonthName).Value)
end select

 

[SkipVought]

In the event that you select that will trigger the saveas logic. It could be when you hit a button. It could be when you close the workbook. When do you want this to happen?

Once you know that, then the code will reside in that event structure.

Tell me what.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip,

I want to have a button that runs the macro. So should I
combine the two codes for the one macro??

 

[SkipVought]

sub button_click()
  select case Range(MonthName).Value
    case "January","February","March"  'include all 12 month
      SaveSingleSheet(Range(MonthName).Value)
  end select
end sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Ok Skip,

As you can probably tell I am totally lost on this. Am
I dealing with two separate pieces of code. If so How do
I get them both to run and when? Also, what does sMon
As String mean? I have tried different combinations but
I cannot figure this out. Could you get real basic for me.

I have a Range Called "MonthNames" setup in cells A2-A13.
I have the names of each month in these cells. I have 14
sheets in the workbook and 12 of them are one for each
month of the year. When I run a macro, preferably from a
Button I want to save only the Monthly sheets to this
location S:\eng\Attendance_Vacation\2009 with the
filename of AJTimeSheet. I certainly am sorry to be
such a nuisance and I am trying to look up and learn about
Case etc. but I sure could use some specifics as to how
I need to accomplish running the code you provided.

Thanks for everything.

 

[SkipVought]

Have your SaveSingleSheet subroutine in a module. Modify it to accept an single argument, sMon is the argument variable.

The button_click code calls the SaveSingleSheet procedure, passing it the value of Range(MonthName).Value. I removed the parentheses, BTW. When you click the button, this code runs passing whatever value you have in Range(MonthName).Value, where I assume that MonthName is a Named Range on one of your sheets, where you have placed a Sheet Name. Tha's what this code does.

sub button_click()
  select case Range(MonthName).Value
    case "January","February","March"  'include all 12 month
      SaveSingleSheet Range(MonthName).Value 
  end select
end sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

This is the code I have in a Module named SaveSingleSheet:

Sub SaveMonthSheets(sMon As String)
Dim MyPath As String
MyPath = "s:\eng\Attendance_Vacation\2009" ' Change this to meet your needs
Sheets(sMon).Copy
'Cells.Copy
ActiveWorkbook.SaveAs Filename:=MyPath & "\AJTimeSheet.xls"
ActiveWorkbook.SaveAs Filename:=MyPath & "\AJ" & sMon & ".xls"
ActiveWorkbook.Close
End Sub

This is the code for the Macro SaveMonths

Sub SaveMonths()
  Select Case Range(MonthNames).Value
    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" 'include all 12 month
      SaveMonthSheets (Range(MonthNames).Value)
  End Select
End Sub

I get an error:

Combile error:
Expected Variable or procedure, not module

This hangs up on
SaveMonthSheets under the Save Months Code.

Please advise. I have to be doing something I shouldn't.

 

[SkipVought]

Your MODULE has the same name as your PROCEDURE. The must be different names.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}