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]

Please test on a copy. Unexpected results may occur.

thanks for the help but that is not what I am looking for. - I going to close this out and resubmit my need.

 

[Fluff]

Please do not start a new thread. You need to continue in this one. Thanks

 

[mumps]

When creating next month's workbook, do you want to keep all the sheets from the previous month? Would it not be best to just keep the four basic sheets(Ablank, Zdata, Znotes and ZShortCuts) and delete all the previous month's sheets?

 

[ShawnSPS]

When creating next month's workbook, do you want to keep all the sheets from the previous month? Would it not be best to just keep the four basic sheets(Ablank, Zdata, Znotes and ZShortCuts) and delete all the previous month's sheeet

no because the company (client name and additional info in under it may not change .. just the data in the grid to the right or be yet the counts . that is why when the user delete a client it also deletes from the drop down list. - because some of that data will be static . so I dont want them to have recreate the sheet and have them re-enter the jobs that needs to be fill for that company only the count on how many they need and how many temps we have available to provide them. I hope I am explaining it so you can grasp what the sheet is doing.

 

[ShawnSPS]

Ok – I have another set of circumstances that I need to address with this workbook. I have three individual marco’s that I would like to combine into one and elimate – the unnecessary Marco’s. we had talked about the other issue I had with the sheet and was resolved with your help. I had place this info in the fourm but the person I was talking with did not get what I was looking for and so I trust your expertise so I came back to you for the help. I will understand if you decide to decline. just rather go with someone who has working knowledge of what i was attempting to do.

The three macro titles are :

• CycleMonthThruWbs() which it uses the system clock to pull the month and place into A! on all sheets in the workbook except on “Ablank”, “zdata” and “ZShortcuts”
• ClearContents() which clears data in the range ("D3:AH31,D34:AH41") for all shees
• I have yet found a vba code that makes since : I need vba code that wil allow the user to enter the file name they want and save as into a location such as documents folder.

What I want from combing the code. Is

When I press the Month button it will first want to do a save as the current workbook with all the data that was enter for the previous month. It would then give a message warming that the current month will change to whatever the next month is and all data from the previous month will be delete are you sure you want to change the month and clear all data Yes/no. if yes and the month changes to such as apirl and the data is clear again it will as to ask save as “file” what the user enters . basically the end result should be . I have save book for last month and saved new workbook for this month with no data where the use can now start enter data for the new month. I am not sure how any clearer I can get . but I will include excel work book again. Just in case you deleted it.workbook file

 

[mumps]

Click here to download your file. Please note that you will have to re-assign each "Month" button in each sheet to the macro named "SaveNextMonth". This is the code:

Sub SaveNextMonth()
    Application.ScreenUpdating = False
    Dim MyFolder As String, 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
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Please select a save folder"
            .Show
            .AllowMultiSelect = False
            If .SelectedItems.Count = 0 Then
                MsgBox "You did not select a folder."
                Exit Sub
            End If
            MyFolder = .SelectedItems(1) & "\"
        End With
        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:=MyFolder & fName & ".xlsm"
    Application.ScreenUpdating = True
End Sub

 

[ShawnSPS]

Click here to download your file. Please note that you will have to re-assign each "Month" button in each sheet to the macro named "SaveNextMonth". This is the code:

Sub SaveNextMonth()
    Application.ScreenUpdating = False
    Dim MyFolder As String, 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
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Please select a save folder"
            .Show
            .AllowMultiSelect = False
            If .SelectedItems.Count = 0 Then
                MsgBox "You did not select a folder."
                Exit Sub
            End If
            MyFolder = .SelectedItems(1) & "\"
        End With
        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:=MyFolder & fName & ".xlsm"
    Application.ScreenUpdating = True
End Sub

Ok at first glance I thought it was working correctly. However I found a couple anomalies, one when using the month button march goes into the dropdown list in znotes which it shouldnt. the other issues is when the user enters file name and goes to the save as that new name doesn't populate in the folder name hmmm thinking as I am typing is there a way when the user types in the name they want save as . is save right to the my documents folder ??

 

[mumps]

the month button march goes into the dropdown list in znotes

I could not reproduce this problem. It worked properly for me. What is the full path to your documents folder?

 

[ShawnSPS]

I could not reproduce this problem. It worked properly for me. What is the full path to your documents folder?

well each user will have its own path but I can update that on my own. but my path is C:\Documents and Settings\it0180s\My Documents

 

[mumps]

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

well each user will have its own path

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.