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

**sandbach** · 10-25-2010, 11:04 AM

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

**sandbach** · 10-25-2010, 11:17 AM

Further to that the drop down menu with bosses names is located in cell a4 on sheet1, data is on sheet2

**Leith Ross** · 10-25-2010, 11:55 AM

Hello sandbach,

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

**sandbach** · 10-25-2010, 12:07 PM

Hello Leith

I have actually just managed to solve the drop down bit so i just need the file saving macro bit dependant on what value is held in a4 for the last part of the file path.

I had tried for ages trying to work it out msyelf the drop down bit and finally had a break through, i just cant crack the macroy bit!

Kind regards

Joe

**Leith Ross** · 10-25-2010, 12:30 PM

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.

Please Login or Register  to view this content.

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