Thread: Macro to save data in sheet to file on hard drive.

Hello

I was wondering if anybody could help please.

I have a workbook in which i would like to save one sheet from my workbook into a file on the harddrive.

Unfortunately the data is confidential so i cannot get my existing workbook off my computer .

How i would like it to function is for each person to save their work according to who their boss is. So i need a drop down menu that displays all the bosses name.
Mike, dave and Julie
The person will do their work which will generate data on sheet2

i would like them to then select who their boss is with a dropdown menu and then their data to be sent to a file C:/WSA Project/ - name of boss -

So the choice of name automatically changes the folder destination.

If possible i would also like it to flash up a message if they have not chosen their bosses name.

I intend to add all this already to a button that activates other commands as well.

Is all this possible?

Hopefully somebody could help, and if anybody requires any further questions please ask, and once again sorry for not being able to attach my workbook.

Kind regards

Joe

Hello sandbach,

1. Is this drop down a Data Validation type?
2. If you want the drop down on Sheet1 in cell A4, do you have a range somewhere that holds the names?
3. Does this range have a name?

Hello Joe,

This macro will check that the Boss' name has been selected before the file can be saved. If a name has not been chosen then a message notifies the user and automatically selects cell A4 on Sheet1.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  Dim Boss As Range
  
    Set Boss = Worksheets("Sheet1").Range("A4")
    If Boss.Value = "" Then
       MsgBox "Please Select your Boss' Name."
       Cancel = True
       Boss.Parent.Activate: Boss.Select
       Exit Sub
    End If
  
    Worksheets("Sheet2").Copy
    ActiveWorkbook.SaveAs Filename:="C:\WSA Project\" & Boss.Value, FileFormat:=xlWorkbookNormal

End Sub

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S