VBA- Save 2 worksheets as separate files. File name and path based on cell values.

[ExceLoki]

hey all, i'm trying to save the worksheets MOM and SLN each as separate workbook with file name and file path based on separate cell values on the parameters sheet.
thanks in advance!
-----------

Sub CopyItOver()
Path = B15
Filename = B14
  Set NewBook = Workbooks.Add
  Workbooks("Reporting.xlsm").Worksheets("MOM").Range("A1:AO1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial
  
  NewBook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlNormal
  
End Sub

-----------

Reporting.xlsm
	A	B
1	Parameter	Value
2	EmployeeID
3
4		1/1/2019
5		*1st day of the previous month
6	DatabaseServer
7	Database
8
9		1/1/2020
10
11
12
13
14	MOMTermFileName	MOMTermFileName_YYYYMMDD.xlsx
15	MOMTermPath	C:\Temp\
Parameters

-----------

 

[ExceLoki]

i'm currently trying this to include both sheets. before i only had one sheet in the vba.

Sub CopyItOver2()

Set Path = Sheets("Parameters").Range("B15")
Set Filename = Sheets("Parameters").Range ("B14")

  Set NewBook = Workbooks.Add
  Workbooks("Reporting.xlsm").Worksheets("MOM").Range("A1:AO1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial
  
  NewBook.SaveAs FileLocation:=Path.Value & _
Filename.Value & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
Application.Display Alerts = True

Set Path = Sheets("Parameters").Range("B13")
Set Filename = Sheets("Parameters").Range ("B12")

  Set NewBook2 = Workbooks.Add
  Workbooks("Reporting.xlsm").Worksheets("SLN").Range("A1:AO1000").Copy
  NewBook2.Worksheets("Sheet1").Range("A1").PasteSpecial
  
  NewBook2.SaveAs FileLocation:=Path.Value & _
Filename.Value & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
Application.Display Alerts = True

End Sub

 

[ExceLoki]

coworker helped me with this:

Sub CopyItOver2()

Dim Path As String
Dim filename As String
Dim Path2 As String
Dim filename2 As String

Path = Sheets("Parameters").Range("B15")
filename = Sheets("Parameters").Range("B14")
Path2 = Sheets("Parameters").Range("B17")
filename2 = Sheets("Parameters").Range("B16")

  Set NewBook = Workbooks.Add
  Workbooks("ReportingPDS.xlsm").Worksheets("MOM").Range("A1:AO1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial
  
ActiveWorkbook.SaveAs filename:=Path & _
filename & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False

  Set NewBook2 = Workbooks.Add
  Workbooks("ReportingPDS.xlsm").Worksheets("SLN").Range("A1:AO1000").Copy
  NewBook2.Worksheets("Sheet1").Range("A1").PasteSpecial
  
ActiveWorkbook.SaveAs filename:=Path2 & _
filename2 & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False

End Sub