Create array of worksheets by identifying first and last worksheets in array

[Groovicles]

Hi everyone,

I've never ran into this issue before. I'm trying to create an array of worksheets to run one of my macros. The worksheet names change every now and then, but they ones I need are always in-between two specific worksheets. THe workbook is structure this way:

Worksheet(1) = "Reps Start Here"
Worksheets(2) - Worksheets(I)
Worksheet(end) = "Reps Stop Here"

So, between worksheets "Reps Start Here" and "Reps Stop Here" are the worksheet names I need to create an array from. Any idea how I can do that?

Thanks!!!

[kev_]

Replace the names with the real names for your sheets and see if this gives you what you want

Sub sheetIt()
    Dim x As Integer
        For x = Sheets("Reps Start Here").Index To Sheets("Reps Stop Here").Index
            msg = msg & vbCr & Sheets(x).Name
        Next x
    MsgBox msg
End Sub

[kev_]

and if you want them in an array

Sub sheetIt()
    Dim x As Integer, Y As Integer, Z As Integer, xArray
    Y = Sheets("Reps Start Here").Index
    Z = Sheets("Reps Stop Here").Index
    ReDim xArray(Z - Y + 1)
        
        For x = Y To Z
            xArray(x - Y) = Sheets(x).Name
            msg = msg & vbCr & xArray(x - Y)
        Next x
    MsgBox msg
End Sub

[Groovicles]

Hi Kev,

Thanks very much for getting back to me. While you're code works perfectly, I've found that not all sheets between the two I identified above are ones that can be used. So I need to take a different approach. Below is the code I'm using which currently hardcode identifies the sheets to be selected:

Sub Split_Reps()
    Dim strPath As String
    Dim myFile As String
    Dim ws As Worksheet
    Dim wbNew As Workbook
    
    Application.ScreenUpdating = False
    
    MsgBox ("Location file")
        
    myFile = Application.GetOpenFilename
    
    Workbooks.Open (myFile)
           
    ArrayOne = Sheets(Array("AAA", "BBB", "CCC", _
        "DDD", "EEE")).Select

    MsgBox ("Identify where to save")
    
    strPath = Application.GetSaveAsFilename
    
    Sheets(ArrayOne).Select
    
    For Each ws In ActiveWindow.SelectedSheets
        ws.Copy
        Set wbNew = ActiveWorkbook
        Workbooks("Model.xlsm").Sheets("Data").Copy after:=wbNew.Sheets(1)
        Worksheets(1).UsedRange.Value = Worksheets(1).UsedRange.Value
        Worksheets("Data").Range("A22").Value = Worksheets(1).Range("B44").Value
        BreakLinks wbNew
        wbNew.Close True, ws.Name & ".xlsx"
    Next
    
    Application.ScreenUpdating = True
End Sub

How can I adjust the code so that instead of hardcoding the sheet names to create ArrayOne that I can just open the workbook (myFile) and select all the sheets? The selected sheets would be stored as ArrayOne and continue on with the code.

Any ideas?

Thanks!!

[kev_]

Is this what you want VBA to do?
- open the file
- ask you to select the sheets manually
- put those selected sheets into an array
- continue with the code

[Groovicles]

Hi kev,

Yes, locate and open the file, select the worksheets manually, run the rest of the code. I'm only familiar with creating static arrays, so if its possible to create a variable array and have the rest of the code run with it that would be great. Alternatives are very welcome!

Thanks for your help!

[kev_]

I'm thinking VBA can provide you with a list of all sheet names with check box perhaps to select those you want
Q Are you usually selecting more than 50% of the sheets in the workbook?

[Groovicles]

Hi kev,

There should be no more than 45 worksheets or so. I can't guarantee if it'll be more or less than 50% of the workbook. The list with checkboxes would be a great idea if the list can be generated with check boxes in the workbook that has the macro. That way I can copy the selected worksheets into a worksheet as a list for some other stuff I need to do with it.

[Groovicles]

I worked out a solution. Thanks very much kev

[kev_]

One way
- split your macro into 2 separate macros (A & B)
- macroA ends by opening the other workbook and activating UserForm
- the UserForm contains ListBox to select sheets and a CommandButton
- clicking on CommandButton Groups the Sheets and calls macroB

This is the code for the UserForm:

Private Sub CommandButton1_Click()
    Dim i As Integer
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                Sheets(ListBox1.List(i)).Select (False)
            End If
        Next i
    Unload UserForm1
    MsgBox "Call 2nd part of macro here"
End Sub

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
        For Each sh In Worksheets
            ListBox1.AddItem sh.Name
        Next sh
End Sub

Please also post your solution for everyone who reads the thread later to benefit from.
Thanks