+ Reply to Thread
Results 1 to 8 of 8

Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?

    Dear Forum,

    I have a file in which the Sheet Name Can be Dynamically be Changed by typing any name in a Cell, but this works only for three sheets.This file was found this in the forum i dont have the source anymore

    Firstly, since I am not VBA expert am not able to tweak it..
    But My requirement is to have a file with a Single Sheet-> Main Sheet where I have the following details
    Col A ---Col B-------- Col C
    Sr.No----Sheet No----Sheet Name <----Row 1
    1 ------ Sheet 2 ---- A

    Lets say I type Sheet 2 in cell B2 and type the name in cell C2, then a New Sheet with this name should be added and if I just change the name in cell C2then still this name should be changed..

    I know this is possible but only thru VBA?
    Can someone please help me on the same...
    Attached Files Attached Files
    Last edited by e4excel; 10-23-2009 at 05:58 AM. Reason: More appropriate Title

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Can Any No of Sheets be Added and Renamed via VBA?

    This can be done, but I do not understand that you would like to type a afterwards sheetname and then the sheetname is changed. Why not do it directly.

    What you could do is:
    Please Login or Register  to view this content.
    where SheetsNames refers to =Sheet1!$C$6:$C$8 (Named range)
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Can Any No of Sheets be Added and Renamed via VBA?

    Thanks for such a quick repsonse, but as mentioned earlier I am no good in VBA at all and do not understand how to add this code to be used I just pasted the code in your earlier thread then nothing happens can you add a file for my refernce and the file which I have uploaded is just to give an idea and I would prefer that the Range Strats from C2 till Cn so on so forth and not in between as C6 as mentioned in the file..

    CAn you please add an attachment as I am not able to do anything further.

  4. #4
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Can Any No of Sheets be Added and Renamed via VBA?

    Look into the attached WB. Press Alt-F11 to see the code. Double click Module1. Set the cursor somewhere between "Sub" and "End Sub" and press F5 (short-cut for run)
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Can Any No of Sheets be Added and Renamed via VBA?

    Maybe better to use this
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?

    Thanks for the attachment , I liked the idea of having a command button do the needful...

    Quote Originally Posted by e4excel View Post
    I would prefer that the Range Strats from C2 till Cn so on so forth and not in between as C6 as mentioned in the file..

    One more thing How did you get the Labels in Column B6 Sheet 2 Name is this automated via VBA as well? If not can that be done ...

    I would appreciate if you could help explain how to change the location to C2 to Cn and also have the labels appear dynamically?

    I am no good in VBA to understand the code much?

    Please Login or Register  to view this content.
    I understand in this code you have called the below module

    Please Login or Register  to view this content.
    What is Worksheets Count?
    The purple portion if not mistaken is a For Loop?

    In the whole exercise we ar just renaming the Sheet Names dynamically via VBA but can we also Add them including the above?

    If yes then can you please help me on the same?
    Last edited by e4excel; 10-23-2009 at 06:05 AM. Reason: Proper Tags

  7. #7
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?

    I did some research on ozgrid and found some useful Files , however just need to customise them, I would appreciate if someone could help me on this..

    I want to Add and Dynamically name the Sheets while adding them thru a Main Sheet.Can this be possible ? If yes then please add an attachment...
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?

    Please open a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1