How d I save a workbook from a path and file name listed in cell from a second workbook?

skidarmy

New Member
Joined
Aug 17, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have a control (Dashboard) workbook/sheet that runs a filtering macro on one report and splits it out to the required fields based on the sort options I have put in. To open this I use

Sheets("DASH").Select
PathName = Range("K4").Value
filename = Range("L4").Value
ControlFile = ActiveWorkbook.name
Application.AskToUpdateLinks = False
Workbooks.Open filename:=PathName & filename

Which will open the data and then it does it's thing. I want to be able to save the file in the same way automatically from the information in cells (PathName) "K5" and filename "O5" as they are automatically set to the user and what part of the server they are connected to.

I can find examples of saving a file from a cell reference within the same active sheet but for this to work, i need the active sheet to save using the locations in the "dashboard" workbook.

So - Workbook1 starts the opening of the Data (Workbook2), this also run's the macro and will have the SaveAs path and name
Workbook 2 opens from the macro in WB1 and once it's completed I'd like it save based on the information in the 2 cells from WB1

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Lee J

New Member
Joined
Jun 30, 2020
Messages
45
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Skidarmy,

This code creates a new workbook from a workbook and then saves to the location and filename in K5 and O5:

VBA Code:
Sub Macro2()

SavePath = Range("K5").Value
SaveFileName = Range("O5").Value

    Workbooks.Add
ActiveCell.FormulaR1C1 = "Testing"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Worked"
Range("A2").Select

ActiveWorkbook.SaveAs SavePath & SaveFileName & ".xlsx"


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,180
Messages
5,623,216
Members
415,957
Latest member
Newguy1924

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