Macro to Save worksheet to new document
 

Hi all,

I need create a macro that will:
- copy a Tab from one workbook into a new workbook
- Save to a specific path to a shared server, with a unique name (dept)

What I've done:
I can get the macro to copy to a new workbook, and the path to the right
server, but I don't know how to input the unique name (dept).

Other info:
- The unique name (dept) is available in the document (from a drop down box
the user selects), but I can't figure out how to copy it into the "save"
function in the macro. I've tried the control keys, but that didn't work.
- Could I have the user type it in (use a sub-macro or some kind of input
form?)

Any help would be appreciated.
Linda

Macro to Save worksheet to new document
 

Hi Linda

Try this

It use the value of A1

..SaveAs "\\Judith\Ron\ " & wb.Sheets(1).Range("A1").Value & ".xls"


Sub Copy_ActiveSheet()
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "\\Judith\Ron\ " & wb.Sheets(1).Range("A1").Value & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Linda@LLBean" wrote in message ...
Hi all,

I need create a macro that will:
- copy a Tab from one workbook into a new workbook
- Save to a specific path to a shared server, with a unique name (dept)

What I've done:
I can get the macro to copy to a new workbook, and the path to the right
server, but I don't know how to input the unique name (dept).

Other info:
- The unique name (dept) is available in the document (from a drop down box
the user selects), but I can't figure out how to copy it into the "save"
function in the macro. I've tried the control keys, but that didn't work.
- Could I have the user type it in (use a sub-macro or some kind of input
form?)

Any help would be appreciated.
Linda

Macro to Save worksheet to new document
 

This worked like a charm!! Thank you Ron!
:-)

....Linda

"Ron de Bruin" wrote:

Hi Linda

Try this

It use the value of A1

..SaveAs "\\Judith\Ron\ " & wb.Sheets(1).Range("A1").Value & ".xls"


Sub Copy_ActiveSheet()
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "\\Judith\Ron\ " & wb.Sheets(1).Range("A1").Value & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Linda@LLBean" wrote in message ...
Hi all,

I need create a macro that will:
- copy a Tab from one workbook into a new workbook
- Save to a specific path to a shared server, with a unique name (dept)

What I've done:
I can get the macro to copy to a new workbook, and the path to the right
server, but I don't know how to input the unique name (dept).

Other info:
- The unique name (dept) is available in the document (from a drop down box
the user selects), but I can't figure out how to copy it into the "save"
function in the macro. I've tried the control keys, but that didn't work.
- Could I have the user type it in (use a sub-macro or some kind of input
form?)

Any help would be appreciated.
Linda