Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

renaming sheets

  Asked By: Cindy    Date: Mar 06    Category: MS Office    Views: 575
  

I am trying to create a sort of database gateway (access mimic if you
will). I have a single spreadsheet that creates/opens other workbooks.

I have managed to create the additional workbook and name it with an
input from my gateway, but I cannot find a way to rename the sheets on
the second (new) workbook. If this is clear enough, can anyone give me
some advice?

To restate, I am trying to rename the sheets in a different workbook
from the one my code was originally running in.

I'm not sure that stated it any clearer. Oh, well, you guys are smart
enough to figure out what I'm trying to say.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Daimon Jones     Answered 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...

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




Tagged: