Macro to save worksheet to a particular location

[saraapple]

I have the following macro which works perfectly (all credit to this site )

Dim NewName As String
Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian", "Salvage", "Suppliers"))

Shts.Copy

NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
With ActiveWorkbook
Sheets(Array("Suppliers", "SALVAGE")).Select
Sheets("Suppliers").Activate
ActiveWindow.SelectedSheets.Visible = False
.SaveAs (NewName & ".xls")
.Close savechanges:=True
End With
ThisWorkbook.Close savechanges:=False

End Sub

The only problem I have is it saves the new workbook in random places - can I change this to make it save in a particular folder.
I know I need to add the path to the code, but my attempt of:

Path = file:///\\prg-dc.dhl.com\uk\DSC\Sites\Dunelm\TRANSPORT-DRIVE-DO-NOT-MOVE\DHLMER-PLANNING\Richard\NEW%20%20Paragon%20Reporting\

Does not work!

Also with my current version of the macro (which seems to now save sheets in documents) I get an error message when opening the file:

 

[Norie]

Are you trying to save the file to a folder on a network drive?

Is that drive mapped?

 

[saraapple]

Hi, thank you for taking the time.
Yes its a mapped on a network drive - is this possible?
Also the finished worksheet will only be shared with people mapped to the same shared drive so they already have access - if this is what you mean?

 

[fadee2]

hi saraapple,

try the following

Dim NewName As String
Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian", "Salvage", "Suppliers"))

Shts.Copy

NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
[B][COLOR=rgb(65, 168, 95)]newpath = "c:\"  [/COLOR][/B][COLOR=rgb(65, 168, 95)] [/COLOR]      'define your path

With ActiveWorkbook
Sheets(Array("Suppliers", "SALVAGE")).Select
Sheets("Suppliers").Activate
ActiveWindow.SelectedSheets.Visible = False
[COLOR=rgb(65, 168, 95)].SaveAs Filename:=newpath & NewName & ".xls"[/COLOR]    'save your file to new path
.Close savechanges:=True
End With
ThisWorkbook.Close savechanges:=False

End Sub

changes in your code are hilighted in green.

hth..

 

[saraapple]

Thank you for the code
I am no expert at VBA and have to admit to using the macro recorder a lot of the time!
I understand from your code that I need to define the path but all I know is:

Path = file:///\\prg-dc.dhl.com\uk\DSC\Sites\Dunelm\TRANSPORT-DRIVE-DO-NOT-MOVE\DHLMER-PLANNING\Richard\NEW%20%20Paragon%20Reporting\

How do I add this to the code?

Do I keep this bit you added:

newpath = "c:\"

Apologies but I do not understand?

Thanks again
Sara

 

[Norie]

Sara

Instead of something like 'file:///\\prg-dc.dhl.com\uk\DSC\Sites\Dunelm\TRANSPORT-DRIVE-DO-NOT-MOVE\DHLMER' all you should need is the mapped drive, e.g. Z: followed by the path to the folder.

For example it might be something like this.

Sub SaveSheets()
Dim NewName As String
Dim strPath As String

    strPath = "Z:\DSC\Sites\Dunelm\TRANSPORT-DRIVE-DO-NOT-MOVE\DHLMER\"
    Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian", "Salvage", "Suppliers"))

    Shts.Copy

    NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
    
    With ActiveWorkbook
        .SaveAs strPath & NewName, .FileFormat
        .Close savechanges:=True
    End With
    
    ThisWorkbook.Close savechanges:=False

End Sub

P.S. When posting code can you add code tags? Makes things a lot easier to read/copy.

 

[fadee2]

Well i didnt noticed that you are trying to save the file to a network drive. So saveas pieces of code ultimately saves your file in drive c, since this is the path we are providing.

Did u tried removing file:/// from your path...?

 

[fadee2]

Did it work for you??

 

[saraapple]

Not quite - I managed to get the file path recognised then ran into problems making the save:


This is where I am at:
Sub SaveSheets()
Dim NewName As String
Dim strPath As String
strPath = "\\prg-dc.dhl.com\uk\DSC\Sites\Dunelm\TRANSPORT-DRIVE-DO-NOT-MOVE\DHLMER\"
Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian", "Salvage", "Suppliers"))
Shts.Copy
NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
With ActiveWorkbook
Close True, strPath & NewName & "xls"
End With
ThisWorkbook.Close savechanges:=False
End Sub

I get an error at the yellow type?!?!

 

[Norie]

Sara

Did you try the code I posted?

P.S. Could you please use code tags when posting code? All you need to do is select the code you are posting and click the appropriate icon in the toolbar.