alam747
asked on
Create and workbook and rename sheets name
Hi Experts,
I have VBA code to create workbook:
Set wb_Source_01 = Workbooks.Add
wb_Source_01.SaveAs FileName
wb_Source_01.Close False
Set wb_Source_01 = xl.Workbooks.Open(FileName )
Set ws_Source_01 = wb_Source_01.Sheets("Sheet 2")
Set ws_Source_04 = wb_Source_01.Sheets("Sheet 1")
I want to rename the " Sheet2" as "Completed" and "Sheet1" as "InComplete"
Please advise me the VBA code to do that.
Your prompt response would be highly appreciated.
Thanks
I have VBA code to create workbook:
Set wb_Source_01 = Workbooks.Add
wb_Source_01.SaveAs FileName
wb_Source_01.Close False
Set wb_Source_01 = xl.Workbooks.Open(FileName
Set ws_Source_01 = wb_Source_01.Sheets("Sheet
Set ws_Source_04 = wb_Source_01.Sheets("Sheet
I want to rename the " Sheet2" as "Completed" and "Sheet1" as "InComplete"
Please advise me the VBA code to do that.
Your prompt response would be highly appreciated.
Thanks
ASKER
if I want to use the code I mentioned where I can add how to rename the sheet names.
Thanks
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess to answer your question, you can rename the sheets immediately after creating the workbook.
You may also need to create a reference to MS Excel, VBA Environment > Tools > References > scroll down to Microsoft Excel #.# Object Library
You may also need to create a reference to MS Excel, VBA Environment > Tools > References > scroll down to Microsoft Excel #.# Object Library
ASKER
I reopen the file to copy containts from other workbook.
Thanks
Thanks
In fact - I think you are best setting your source by the sheet index as that will (as I understand it) remain persistent independent of the sheet name.
ASKER
while I do as
wb_Source_01.Sheets("Sheet 2").Name=" Anything you want"
wb_Source_01.Sheets("Sheet 1").Name=" Something else you want"
getting runtime error '424' Object required
Thanks
wb_Source_01.Sheets("Sheet
wb_Source_01.Sheets("Sheet
getting runtime error '424' Object required
Thanks
Is wb_Source_01 initialized before you run these 2 lines?
Did you Close wb_Source_01 before running these 2 lines?
Do you have 2 sheets named "Sheet2" and "Sheet1" in that Workbook?
There are the 3 reasons I see that could cause that error on these 2 lines. Please post the complete code, you might have put the lines in the wrong place.
Did you Close wb_Source_01 before running these 2 lines?
Do you have 2 sheets named "Sheet2" and "Sheet1" in that Workbook?
There are the 3 reasons I see that could cause that error on these 2 lines. Please post the complete code, you might have put the lines in the wrong place.
Does the code that I posted above work, ie. does it create a new xls and rename the sheet?
Did you set up the reference to the XL Library?
Did you set up the reference to the XL Library?
ASKER
Thanks a lot to both of you for your help.
No worries, glad it helped.
dim myName as String
myName = "someName"
thisworkbook.Sheets(1).nam