Macro to Save worksheet to new document

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

This worked like a charm!! Thank you Ron!


....Linda