Create selection of worksheets

[Rick_Stanich]

I am stumbling on how I can create a selection of worksheets based on a value in a specified cell.
Example:
If sheet1 cell D4 value is "10" and sheet2 cell D4 value is "10" I then need to select these and copy the sheets as a group, pasting at the end of the last sheet with the cell D4 value of "10" (sheet2). Of course I wont know what sheets will have the value.

I can get the values of the cells for each sheet, I do not know how to put this in some array(?) to select and copy sheets.
(assume option explicit)

        For i = 4 To Worksheets.Count    'Ignore first three sheets
            Sheets(i).Activate
            If ActiveSheet.Name <> "ListA" Then 'ignore sheet named "ListA"
                MsgBox Range("D4").Value    'for testing
                'copy worksheets as group based on operation number
                
            Else: GoTo LastSheet
            End If
LastSheet:
        Next i

As always, any input is appreciated.

[Leith Ross]

Hello Rick_Stanich,

This macro runs in Excel 2003. Since I don't have Excel 2007, I can't test it for that platform. If if doesn't work on 2007 then hopefully it will getting you going in the right direction. Place this code in a Standard VBA Module by itself. The reason is the macro sets Option Base to 1. This affects the lower bound subscript of all arrays. Keeping this code separated can avoid indexing headaches later on.

Sub CopySelectSheets()

  Dim N As Long
  Dim ShtArray() As Variant
  Dim Wks As Worksheet

    For Each Wks In Worksheets
      If Wks.Name <> "ListA" Then
        If Wks.Cells(4, "D").Value = 10 Then
           N = N + 1
           ReDim Preserve ShtArray(N)
           ShtArray(N) = Wks.Name
        End If
      End If
    Next Wks
  
    If N > 0 Then
       Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N))
    End If

End Sub

Sincerely,
Leith Ross

[Rick_Stanich]

Leith, thanks for your input, unfortunately I am "Array" challenged thus I am not fully (or is that fooly ) following the code you supplied.

I get the following error when attempting to run in Excel 2007.

       Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N))
       'Above: Run-time error "9", subscript out of range.

[Leith Ross]

Hello Rick,

The Array function creates an array whose first element starts at 1. Other arrays' firsts elements start at 0 (zero). Setting the Option Base statement to 1 forces all arrays that are created in the module to start at 1, and not zero.

Sincerely,
Leith Ross

[Rick_Stanich]

Leith, thanks for getting me started. I had to enlist help from others and found this simple change to your code.

Changed:

ReDim Preserve ShtArray(N)

to

ReDim Preserve ShtArray(1 To N)

[Leith Ross]

Hello Rick,

Thanks for that post. I'll make a note of that for other Excel 2007 users who may have that question.

Thanks,
Leith Ross