How to save with a name in a particular cell?

N

nk

I am trying to save a sheet into a new file with a name which include a text
in a cell (Cell B1 in the sheet I am going to save).

I have created the following so far, but the cell B1 comes from another
sheet where the cursor stays before the codes below. I also tried to set the
codes so that the cursor starts on the sheet, but it does not work.
I'd like to take a value in the B1 in the "Non JGB Coupon Template" sheet.
Please help.


Const fPath As String = "K:\Private\COLLATERAL\Coupon Payment Redemption\"
Dim fName As String
Dim myFileName As String

myFileName = "Non JGB Couopon" & Range("B1") & ".xls"
fName = fPath & myFileName

ThisWorkbook.Worksheets("Non JGB Coupon Template").Copy
ActiveWorkbook.SaveCopyAs Filename:=fName
ActiveSheet.Parent.Close savechanges:=False
MsgBox "File Saved to " & fName


Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top