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("Sheet2")  
Set ws_Source_04 = wb_Source_01.Sheets("Sheet1")    

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
alam747Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DrTribosConnect With a Mentor Commented:
I'm not sure what you code is actually doing... but here is something that is running from within MS Word and creates a new XL Workbook and names the sheet.

Sub tests()

Dim wb_Source_01 As Excel.Workbook
Dim ws_Source_01 As Excel.Worksheet
Dim filename As String

filename = ThisDocument.Path & Application.PathSeparator & "test.xls"
    
    Set wb_Source_01 = Workbooks.Add
    
    wb_Source_01.SaveAs filename
    wb_Source_01.Close False
    
    Set wb_Source_01 = Excel.Workbooks.Open(filename)
    
    Set ws_Source_01 = wb_Source_01.Sheets("Sheet2")
    wb_Source_01.Sheets(1).Name = "myName"
    wb_Source_01.SaveAs filename
    wb_Source_01.Close True
End Sub

Open in new window


You can expand to add the additional sheet.

I don't understand why you create, close then reopen the file...
0
 
DrTribosCommented:
thisworkbook.Sheets(1).name = "myName" <-- if you want to add name directly OR


dim myName as String
myName = "someName"
thisworkbook.Sheets(1).name = myName   <-- if you assign your name to a variable
0
 
alam747Author Commented:
if I want to use the code I mentioned where I can add how to rename the sheet names.

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
wb_Source_01.Sheets("Sheet2").Name="Anything you want"
wb_Source_01.Sheets("Sheet1").Name="Something else you want"
0
 
DrTribosCommented:
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
0
 
alam747Author Commented:
I reopen the file to copy containts from other workbook.

Thanks
0
 
DrTribosCommented:
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.
0
 
alam747Author Commented:
while I do as
wb_Source_01.Sheets("Sheet2").Name="Anything you want"
wb_Source_01.Sheets("Sheet1").Name="Something else you want"

getting runtime error '424' Object required

Thanks
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
 
DrTribosCommented:
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?
0
 
alam747Author Commented:
Thanks a lot to both of you for your help.
0
 
DrTribosCommented:
No worries, glad it helped.
0
All Courses

From novice to tech pro — start learning today.