Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Cindy Fisher   on Mar 06 In MS Office Category.

  
Question Answered By: Daimon Jones   on Mar 06

It's really very simple.
the syntax is:
WorkBooks(wkbookname).Sheets(sheetname).Name = newname

what you put here completely depends on what you "Know".
Since you were able to make the new workbook, I assume you
know the name?
You can either use a variable:
wkbookname = "NewWorkBook.xls"
or you can hard-code it like:
Workbooks("NewWorkBook.xls")

If there is only one sheet, then you can use:
Sheets(1)
Otherwise, you have to use the sheet name:
sheetname = "Sheet1"
or:
Sheets("Sheet1")

Now, if you wanted to figure  this out on your own,
RECORD A MACRO!
I recorded this:

Sub MakeNewWkbook()
Workbooks.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "This is My New SheetName"
Range("B27").Select
ChDir "C:\temp"
ActiveWorkbook.SaveAs Filename:="C:\temp\This is My New WorkbookName.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Now, since I wanted to address your concerns, I "cleaned it up":

Sub MakeNewWkbook()
Dim NewWkbookName, NewSheetName, DestFolder
'----------------------------
DestFolder = "C:\temp\"
NewWkbookName = "This is My New WorkbookName.xls"
NewSheetName = "This is My New SheetName"
'----------------------------
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=DestFolder & NewWkbookName, _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks(NewWkbookName).Sheets(1).Name = NewSheetName
Workbooks(NewWkbookName).save
End Sub


hope this helps...

Share: 

 
 
Didn't find what you were looking for? Find more on renaming sheets Or get search suggestion and latest updates.


Tagged: