Macro to save worksheet to a particular location

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
148
Office Version
  1. 2010
Platform
  1. Windows
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:

1614075456822.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Are you trying to save the file to a folder on a network drive?

Is that drive mapped?
 

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
148
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
hi saraapple,

try the following
VBA Code:
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

Board Regular
Joined
Feb 3, 2020
Messages
148
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
Did it work for you??
 

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
148
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,161
Messages
5,722,838
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top