three Marco’s that I would like to combine into one action when a button is pressed

[ShawnSPS]

I am currently working on a project. I have three Marco’s that I would like to combine into one action when a button is pressed.

1. Action is to have the user enter a save as file name, which would save the current workbook and Data to their documents on the computer or laptop.

1. Second it would warn them that the workbook will be renamed & updated to the new month entered and data cleared. I them to be to select the month that workbook and sheets are being created for the month and the month place in A1 on all sheets and the data would clear out the all the sheet from the previous Month In the range I provided.

1. Third I want it again the user enters as save as new file name, which would save new workbook and Data to their documents on the computer or laptop. So when they are working or adding data all the have do press save

The follow are the Marcos that I currently created but conceptually and literally being a novice in vba programming I having a hard time working through the changes I want and need in with the vba I had have written. This is a bit of a project but maybe someone can help .. see below for the Marco’s and the Spreadsheet

NewCounts2.xls

Shared with Dropbox

www.dropbox.com

macros"

Sub CycleMonthThruWbs()
Dim ws As Worksheet
Dim MonthName As String
Dim Mn As String


Mn = Format(Date, "mmmm")

For Each ws In ThisWorkbook.Sheets
' Debug.Print ws.Name
ws.Unprotect ("Pila1DA.#")

ws.Activate
Range("A1").Value = Mn
ws.Protect ("Pila1DA.#")
Next ws

Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")

End Sub
__________________________________________________________________________________

Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

End With
Next iterator
Application.DisplayAlerts = True
End Sub

______________________________________________________________________________________


Sub sbSaveExcelDialog()

Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If

End Sub

thanks for all your help

 

[Skyybot]

Do you have a specific order in which the Subs execute? Is it the same every time?

 

[ShawnSPS]

Do you have a specific order in which the Subs execute? Is it the same every time?

currently the subs are a separate functions - the only one that has a button for is to populate the month in A1 the other two are CTRL + shortcuts. there is a shortcut tab on the sheet. so the answer is no . there is no particular order in which is used currently.

 

[Skyybot]

Oh. I thought that we could call one Sub from another if there was an order to them. Let me think a bit more on this.

 

[Skyybot]

Oh. I thought that we could call one Sub from another if there was an order to them. Let me think a bit more on this.

Which Sub does the button call?

 

[ShawnSPS]

Which Sub does the button call?

Which Sub does the button call?

Sub CycleMonthThruWbs() the button is called (Month) on the sheets .

 

[Skyybot]

The Macros that you provided have been tested, and work individually?

 

[ShawnSPS]

The Macros that you provided have been tested, and work individually?

yes that is currently working it pulls what in needs from the system clock on the pc./laptop .. so when the month changes such as from March to April it its seamless. .

 

[Skyybot]

Try this small change to your original code.

Sub CycleMonthThruWbs()
Dim ws As Worksheet
Dim MonthName As String
Dim Mn As String


Mn = Format(Date, "mmmm")

For Each ws In ThisWorkbook.Sheets
' Debug.Print ws.Name
ws.Unprotect ("Pila1DA.#")

ws.Activate
Range("A1").Value = Mn
ws.Protect ("Pila1DA.#")
Next ws

Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
Call sbSaveExcelDialog
End Sub
__________________________________________________________________________________

Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

End With
Next iterator
Application.DisplayAlerts = True
End Sub

______________________________________________________________________________________


Sub sbSaveExcelDialog()

Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
Call ClearContents
End Sub

 

[Skyybot]

Try this small change to your original code.

Sub CycleMonthThruWbs()
Dim ws As Worksheet
Dim MonthName As String
Dim Mn As String


Mn = Format(Date, "mmmm")

For Each ws In ThisWorkbook.Sheets
' Debug.Print ws.Name
ws.Unprotect ("Pila1DA.#")

ws.Activate
Range("A1").Value = Mn
ws.Protect ("Pila1DA.#")
Next ws

Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
Call sbSaveExcelDialog
End Sub
__________________________________________________________________________________

Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

End With
Next iterator
Application.DisplayAlerts = True
End Sub

______________________________________________________________________________________


Sub sbSaveExcelDialog()

Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
Call ClearContents
End Sub

Please test on a copy. Unexpected results may occur.