I sure this is elementary for most of you guys but I am having a hard time trying to create a new sheet with a predesignated sheet number. I want to create a new sheet coded as (Sheet50) . I will be giving it a different name on different files but I would need to reference (Sheet50) via VBA when I re-open the file. Any suggestions? My fear is Excel will only create sequential sheet numbers.
Create Sheet with designated sheet number
- Thread starter patsfan
- Start date
Debraj
Excel Ninja
Hi patsfan,
check this post.. an dlet us know if you can adapt the same in your code..
http://chandoo.org/forum/threads/how-to-edit-my-sheet-name-in-macro.12652/#post-74691
or please provide some more detail..
check this post.. an dlet us know if you can adapt the same in your code..
http://chandoo.org/forum/threads/how-to-edit-my-sheet-name-in-macro.12652/#post-74691
or please provide some more detail..
Thanks Debraj, but I was unable to find a solution from the link you provided.
What I am trying to do is create a worksheet and force the “code name” for that sheet. Example, If I created the worksheet today, I would name it “12-10-13” but Excel would provide it with a sequential built in code name like "Sheet21(Sheet21)”.
However, if I want to reference that sheet via VBA at a later time, I can only reference it by using the “12-10-13” name or the “(Sheet21)” code name. However, both of those names can change from workbook to workbook.
I want to create a worksheet (via vba code) with an out-of-sequence code name like “Sheet50(Sheet50)” which will remain consistent in all the workbooks I create. That way, I can reference the same sheet in any of the files via VBA.
What I am trying to do is create a worksheet and force the “code name” for that sheet. Example, If I created the worksheet today, I would name it “12-10-13” but Excel would provide it with a sequential built in code name like "Sheet21(Sheet21)”.
However, if I want to reference that sheet via VBA at a later time, I can only reference it by using the “12-10-13” name or the “(Sheet21)” code name. However, both of those names can change from workbook to workbook.
I want to create a worksheet (via vba code) with an out-of-sequence code name like “Sheet50(Sheet50)” which will remain consistent in all the workbooks I create. That way, I can reference the same sheet in any of the files via VBA.
SirJB7
Excel Rōnin
Hi, patsfan!
Design a workbook as a master file, create a worksheet there and build a table in the 2 1st columns:
Column A: an internal identifier that will be used within the code for pointing at that sheet, e.g., "SheetForDoingNothing"
Column B: the code name you want to assign to that type of sheet, e.g., "SheetForDoingNothingAtAll"
Then in all the workbooks call use the name in column A to retrieve the name in column B.
Regards!
Design a workbook as a master file, create a worksheet there and build a table in the 2 1st columns:
Column A: an internal identifier that will be used within the code for pointing at that sheet, e.g., "SheetForDoingNothing"
Column B: the code name you want to assign to that type of sheet, e.g., "SheetForDoingNothingAtAll"
Then in all the workbooks call use the name in column A to retrieve the name in column B.
Regards!
Jake Collins
Active Member
Why not add to the workbook macro file
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "Sheet50"
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "Sheet50"
End Sub
Thanks for the suggestions guys but my inability to properly communicate my thoughts, or use the correct terminology, made this more difficult than it needed to be.
I was able to create a new worksheet in an existing workbook and change the new sheet's codename using the code below.
So now using VBA, I can identify and access the "DataSheet" in any of the data files I created regardless of the visible sheet name.
I was able to create a new worksheet in an existing workbook and change the new sheet's codename using the code below.
So now using VBA, I can identify and access the "DataSheet" in any of the data files I created regardless of the visible sheet name.
Code:
Public newsheetcodename, tempsheet
Sub create_sheet_force_code_name()
tempsheet = "12-18-2013" 'this is a user-entered date for the filename and visible name of the worksheet.
Sheets.Add.Name = tempsheet
newsheetcodename = ActiveSheet.CodeName
ChangeSheetCodeName
End Sub
Private Sub ChangeSheetCodeName()
'''this code must reside in a module all by itself
ThisWorkbook.VBProject.VBComponents(newsheetcodename).Name = "DataSheet"
End Sub