Instead of:
Range("B1")
use:
Sheets("Non JGB Coupon Template").Range("B1")
--
Gary''s Student - gsnu200733


"nk" wrote:

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.