Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding to an array variable (Excel 2000)

    I have the following code...

    Sub PutSheetsOut()
    Sheets(Array("1", "3", "4")).Select
    Sheets(Array("1", "3", "4")).Copy
    ActiveWorkbook.SaveAs Filename:="D:My DocumentsTestSheetCatcher.xls"
    Windows("startbook.xls").Activate
    Sheets("1").Select
    End Sub

    My question is, how do I declare a variable and feed that variable with parts of the array. For example,

    Dim HoldMySet as (and here I am not sure what to declare it as).

    I then have a set of cells that may have 1,3,5,6 in (the range can grow and shrink). These entries equate to sheet names. I wish to highlight this range of entries and then have the macro add each entry to my declared variable so that I can put in a line something like...

    Sheets(Array(HoldMySet).Select
    Sheets(Array(HoldMySet)).Copy

    Any takers?
    Neil Eustice
    Woody Worshipper

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding to an array variable (Excel 2000)

    Hi Neil,
    You need something like:
    Dim arrSheets()
    arrSheets() = Application.transpose(selection)
    sheets(arrsheets()).copy
    This assumes that:
    1. If those numbers equate to sheet names, you'll need to make sure that the cells they are entered in are formatted as text or that they are specifically entered as text.
    2. The sheet names are entered in a column - if they're in one row, you don't need the transpose function.
    3. The sheet name cells are selected when you run the macro - you can change it to use a specific range, named or otherwise.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding to an array variable (Excel 2000)

    Cool, I'll try in now.
    Neil Eustice
    Woody Worshipper

Posting Permissions

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