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

 

[netrusher]

Skip,

I changed the name of the Procedure to SaveMonths
and the Module Name is SaveMonthSheets. below is the
code and I am getting the same error:

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

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

 

[netrusher]

Skip,

By the way the Sub Save Months() is located in
ThisWorkbook.

 

[SkipVought]

By the way the Sub Save Months() is located in ThisWorkbook."

Best, for you, to ALSO have that code in the same module, rather than the ThisWorkbook Object code window.

"I changed the name of the Procedure to SaveMonths"

Sub SaveMonths()
  Select Case Range(MonthNames).Value
    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
      [b]SaveMonths[/b] Range(MonthNames).Value
  End Select
End Sub

Sub [b]SaveMonths[/b](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 again Skip,

I have all of this code in the Module SaveMonthSheets
I get the following error on Sub SaveMonths(sMon As String)

Compile error:
ambiguous Name detected: SaveMonths

This is the code:

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


Sub SaveMonths(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

 

[SkipVought]

Sorry, I was not paying attention to all your names and I made the wron assumption, when you stated,

I changed the name of the Procedure to SaveMonths
and the Module Name is SaveMonthSheets
[/code]
BOTTOM LINE:
No MODULE or PROCEDURE can have the same name.

Go figure it out and make your changes.




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

 

[netrusher]

Thanks Skip,

I can go no further. I have researched for hours and have no better understanding that before. Sorry but this is hard for me. I have all the code you provided in a module. How to I fire the Module. I have no Procedures unless what is in the module counts as a procedure. You said to put both pieces of code in the module and I am still getting the error I quoted above. If you have time or if you don't I understand, but I hope someone else out there does so I can get this finished. I do not know where else to turn to or where else to go concerning this. Thanks again.

 

[PHV]

netrusher, FYI, a procedure is a Sub or a Function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Further to Skip's massive efforts and PHV's reply.

In Excel VBA, No Sub, Function or Module can have the same name as another Sub, Function or Module. If you apply that logic to your code then at least the ambiguous name problems should disappear.

Hope this helps



HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[netrusher]

Thanks Harley,

I did not think I have any duplicate names in the code
posted above. What do you mean?

 

[SkipVought]

did not think I have any duplicate names in the code
posted above. What do you mean?"

You have TWO procedures named SaveMonths

Sub [b]SaveMonths[/b]()
  Select Case Range(MonthNames).Value
    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
      SaveMonths Range(MonthNames).Value
  End Select
End Sub


Sub [b]SaveMonths[/b](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]

I want to thank everyone for their input. The below code is
what is working for me.

Sub MoveSheets()

Worksheets(Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")).Copy

Set wbNew = ActiveWorkbook
With wbNew
    ActiveWorkbook.SaveAs Filename:="S:\Eng\Attendance_Vacation\2009" & "\AJTimesheet.xls"
    .Close
End With
End Sub