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

 

[ShawnSPS]

Try this version.

Sub SaveNextMonth()
    Application.ScreenUpdating = False
    Dim mon As String, nextMon As String, fName As String, ws As Worksheet
    mon = MonthName(Month(Date))
    nextMon = MonthName(Month(Date) + 1)
    If MsgBox("The current month will change to " & nextMon & " and all data from the previous month will be deleted. Are you sure you want to change the month and clear all data?", vbYesNo) = vbYes Then
        fName = InputBox("Enter the file name to be used.")
        If fName = "" Then Exit Sub
        ActiveWorkbook.Save
        For Each ws In Sheets
            If ws.Name <> "Ablank" And ws.Name <> "Zdata" And ws.Name <> "“ZShortCuts”" Then
                With ws
                    .Unprotect ("Pila1DA.#")
                    .Range("A1") = nextMon
                    .Range("D3:AH31,D34:AH41").ClearContents
                    .Protect ("Pila1DA.#")
                End With
            End If
        Next ws
    End If
    ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\it0180s\My Documents\" & fName & ".xlsm"
    Application.ScreenUpdating = True
End Sub

Keep in mind that if you use the original macro, all the user has to do is select his/her own documents folder when the FileDialogue window pops up. This would save you from having to update the path in the macro for each user.

I completely understand .. how this workbook is setup is for automation . the less they have mess with the less the can screw up lol. trust not all user are tech savvy which is why this being created i the first place. I did test the above code and its working with my pc just fine. thank you for all the help with this project again I appreciate it.

 

[mumps]

My pleasure.

 

[smozgur]

I did test the above code and its working with my pc just fine. thank you for all the help with this project again I appreciate it.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[smozgur]

@ShawnSPS: As I can see, you marked my informative post as the solution.
I already changed the marked solution in this thread. No further action is required.
In your future questions, please mark the post as the solution that actually answered your question.