+ Reply to Thread
Results 1 to 14 of 14

Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO

  1. 11-17-2013, 05:32 AM #1
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO

    Dear Forum,

    I need to create an Array which contains Sheet Names dynamically as I need to exclude a few Sheet Names from this array and include the rest...

    This Sheet Array is to be used in a Macro..

    I am not able to actually create an Array dynamically which can hold the Sheet Names.

    So can someone guide me on this please..

    Thanks in advance.
    e4excel
    Reply With QuoteRegister To Reply
  2. 11-17-2013, 06:38 AM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Click on the button.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 11-17-2013, 06:57 AM #3
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Hi, e4excel,

    could you please make clear how the array should be created? Do you know which sheets will not be part of the array (always the same) or is the user free in her/his choice to do so? If so I would go with a ListBox but allow MultiSelect and then build the array from the selected items.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  4. 11-17-2013, 07:02 AM #4
    xlbiznes
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Hi @e4excel,

    Try this :

    Please Login or Register  to view this content.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *
    Reply With QuoteRegister To Reply
  5. 11-23-2013, 05:09 AM #5
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Thanks all of you for extending help in this project..

    But what I need is to automatically fill the Array variable with all the Sheet Names except a few sheets which I can provide..

    Ex: There are seven Sheets and 2 Template Sheets Template 1 and Template 2.

    So Five Sheet names need to go in the array or better is I may have more number of sheets but just need to exclude 2-3 sheets which I can mention using the LIKE operator.

    But I want the remaining sheet names to get in the SHeet Array variable.

    @ mehmetcik : Liked your approach though..

    @ xlbiznes : I want the array to self fill with the names

    e4excel
    Reply With QuoteRegister To Reply
  6. 11-23-2013, 05:22 AM #6
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Hi, e4excel,

    maybe something like this - you would need to adapt the sheets not to include to suit:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Reply With QuoteRegister To Reply
  7. 11-23-2013, 07:48 AM #7
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  8. 11-23-2013, 08:00 AM #8
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Dear Hahobe,

    Thanks for the code but please can you please explain it to me...Mybe I might want to use the LIKE operator to exlcude sheets which start with a particular name like "Temp*"

    I would be using this array to store the names of all these sheets and then use in some other code to copy only these sheets..

    Regards
    e4excel
    Reply With QuoteRegister To Reply
  9. 11-23-2013, 08:23 AM #9
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Hi, e4excel,

    I think this has been answered with Norie´s code.

    Ciao,
    Holger
    Reply With QuoteRegister To Reply
  10. 11-23-2013, 09:57 AM #10
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Abolsutely...

    Thanks a lot everyone for the wonderful solution ..This is very helpful.



    Regards
    e4excel
    Reply With QuoteRegister To Reply
  11. 11-28-2013, 02:05 PM #11
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Dear Norie,

    Please explain what Data tyoe is this "Dim arrsheets()"

    and what does the statement do ?

    Please Login or Register  to view this content.
    Thanks in advance
    Regards
    e4excel
    Reply With QuoteRegister To Reply
  12. 11-28-2013, 02:15 PM #12
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Hi, e4excel,

    Please Login or Register  to view this content.
    is not specified as DataType and thus is handled as a Variant (like all other variables which are not exclusively being of a certain DataType are also).

    Please Login or Register  to view this content.
    ReDim marks the scope of the array (usually extend it). Keyword Preserve is for holding the data which is present in the array, not using it would create a new array in the given measures. I being the counter for the array, when starting the value for I is 0 so the array will hold only one value. After each loop the value for I is increased by one to repeat the process.
    Please Login or Register  to view this content.
    Assign the worksheet name to the postion in the array.

    HTH
    Holger
    Reply With QuoteRegister To Reply
  13. 11-28-2013, 02:50 PM #13
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Holger

    Couldn't have put it better myself.
    Reply With QuoteRegister To Reply
  14. 11-29-2013, 03:24 PM #14
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACR

    Thanks a lot for the explanatio Hahohobe
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Simple macro to create list of sheet names and the contents of cells A1 & N1 of each sheet
    By atcsmh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2013, 11:11 AM
  2. Using Array for Sheet Names
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-22-2013, 07:59 PM
  3. Macro in sheet one to create a search for names in sheet 2
    By CamillaKb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2012, 08:17 AM
  4. Getting a list of Sheet Names by excluding certain Sheet Names instantenously..
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-09-2012, 12:16 PM
  5. Create array of file names/sheet names
    By BVHis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-06-2008, 11:30 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1