"save as" particular file path

[ajm]

I have a document which will be going out to several different users in associate companies. They will be instructed to create a folder called "Performance Management" either on their own local drive or on a network drive (some of the users are very small sized companies and some are rather large). To this folder they will save my Performance Management workbook.

I want to take the idiot work out of the next step for them by building a macro (off a command button) that will pick up the file path of the original workbook, and using Operator, Year and Month data, will SAVE the next workbook AS a new file for the next performance appraisal.

so, the original workbook will remain in its original format on their (for example) "C:\Documents and Settings\ajmould\My Documents\Performance Management\Performance Management.xls".

Then, when they set up for the first appraisal, they will submit Operator Name, Month, and Year (selected from validation lists on the cover page of the original file), hit the command button, and a new file will be created for them in the same folder, using the same naming conventions, such that:
C:\Documents and Settings\ajmould\My Documents\Performance Management\Performance Management December 2006.xls

If further detail is needed, I can supply or email.

 

[TryingToLearn]

Possibly something like this:

Private Sub CommandButton1_Click()
    Dim wbpath$, mnth$, yr$

    ' Assume your month and year on the cover page are in  A1 & A2 of sheet coverpage
    With Sheets("coverpage")
        mnth$ = .Range("a1")
        yr$ = .Range("a2")
    End With
    ThisWorkbook.SaveAs Application.ActiveWorkbook.Path & "\Performance Management " & mnth$ & " " & yr$
End Sub

 

[ajm]

bob, should this be inserted into the page itself, "This workbook", or a module? I have it inthe page itself, called Cover Sheet, but am not getting a response when I click on my button.

 

[ajm]

don't mind me. i forgot to take off the design mode in the control toolbox.

will the code you provided, work for any file path? That is, the new file will be saved alongside the original each time?

 

[ajm]

am curious now though as to how I can ensure that copies of the "copied" workbook aren't made. is it possible that on creation of the new workbook using the code you gave me, that the command button and routine are removed?

 

[TryingToLearn]

will the code you provided, work for any file path? That is, the new file will be saved alongside the original each time?

Yes it will work for any file path. The Application.ActiveWorkbook.Path pulls the path of the currently opened workbook regardless of it's being "C:\Documents and Settings\ajmould\My Documents\Performance Management" or "C\Milky Way\Mars\MNM" and adds your month and year data to the file name.

am curious now though as to how I can ensure that copies of the "copied" workbook aren't made. is it possible that on creation of the new workbook using the code you gave me, that the command button and routine are removed?

Huh? Not sure what you mean in the first part, but yes you can remove the command button and routine but that is beyond my knowledge..

 

[ajm]

bob, ended up with:

Private Sub CommandButton1_Click()
    Dim wbpath$, oprtr$, mnth$, yr$
    Dim Sht As Worksheet
' name new workbook according to file path to which original PM was saved and add Operator, Month, and Year to name
        Application.ScreenUpdating = False
    With Sheets("cover sheet")
        oprtr$ = .Range("f24")
        mnth$ = .Range("f26")
        yr$ = .Range("f27")
    End With
        Sheets("Cover Sheet").Shapes("CommandButton1").Delete
    For Each Sht In ActiveWorkbook.Worksheets
            If Sht.Name <> "Cover Sheet" Then Sht.Visible = True
    Next
        ThisWorkbook.SaveAs Application.ActiveWorkbook.Path & "\Performance Measurement " & oprtr$ & " " & mnth$ & " " & yr$
        Application.ScreenUpdating = True
End Sub

do you know how i can ensure that the users input their operator, month, and year details before the save?

 

[TryingToLearn]

Try this:

Private Sub CommandButton1_Click()
    Dim wbpath$, oprtr$, mnth$, yr$
    Dim Sht As Worksheet
' check for valid naming data
  With Sheets("cover sheet")
        If (.Range("f24") = "" Or .Range("f26") = "" Or .Range("f27") = "") Then
            MsgBox "Fill out valid names before saving", vbCritical, "INPUT ERROR"
            Exit Sub
        End If
    End With

' name new workbook according to file path to which original PM was saved and add Operator, Month, and Year to name
        Application.ScreenUpdating = False
    With Sheets("cover sheet")
        oprtr$ = .Range("f24")
        mnth$ = .Range("f26")
        yr$ = .Range("f27")
    End With
        Sheets("Cover Sheet").Shapes("CommandButton1").Delete
    For Each Sht In ActiveWorkbook.Worksheets
            If Sht.Name <> "Cover Sheet" Then Sht.Visible = True
    Next
        ThisWorkbook.SaveAs Application.ActiveWorkbook.Path & "\Performance Measurement " & oprtr$ & " " & mnth$ & " " & yr$
        Application.ScreenUpdating = True
End Sub

 

[ajm]

i came up with something similar by relying on my users to input their own names (f24). If that is blank, give them a message box and then exit the sub. cheers