Save Sheet As New Work Book

[bloodmilksky]

​Hi Guys,
I have found the below code to save sheet as new workbook but what I wanted to know if it is possible to change the save name
each time the macro is run to the values on sheet 1 in cells B2 & B3




Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Sheet2").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\temp\test1.xlsx"
End Sub

[mana]

Option Explicit

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim myPath As String
Dim myFileName As String

myPath = ThisWorkbook.Sheets("Sheet1").Range("b2").Value
myFileName = ThisWorkbook.Sheets("Sheet1").Range("b3").Value

ThisWorkbook.Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs myPath & myFileName

End Sub

[bloodmilksky]

Can i ask how you set the save location?

[mana]

I guess.

cell B2: C:\temp\
cell B3: test.xlsx

[bloodmilksky]

My appologies I explained that really badly. Ideally if possible could the Filename be the values in B2 & B3 and the File location would be P:\customer service back order info

[bloodmilksky]

I have tried amending it myself but for some reason the file just isnt saving in the requested location

[mana]

you should modify these lines

> myPath = ThisWorkbook.Sheets("Sheet1").Range("b2").Value
> myFileName = ThisWorkbook.Sheets("Sheet1").Range("b3").Value