list of worksheet and dialogbox to select

[luckyearl]

Hi
I am trying to get list of all worksheets, popup Dialog box to select a worksheet, then work with it.
I thought cud store the sheetname in array, but does not work, please suggest. Thank you

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer
Dim ArrayValues As ArrayList         ' This does not work'
Set ArrayValues = New ArrayList        ' This does not work'
 
 For i = 1 To ThisWorkbook.Worksheets.Count
         ArrayValues.Add ThisWorkbook.Worksheets(i).name
    Next sht
ListBox1.List = ArrayValues
End Sub

 

[luckyearl]

Would a data validation dropdown do?

that wud do for me, Thank you

 

[Fluff]

In which cell do you want it?

 

[luckyearl]

In which cell do you want it?

Sheet Index, A3

 

[Fluff]

Ok, how about

Sub ListSheets()
   Dim ws As Worksheet
   Dim i As Long
   Dim ArrayValues As Variant
   
   ReDim ArrayValues(1 To ThisWorkbook.Worksheets.Count)
   For i = 1 To ThisWorkbook.Worksheets.Count
      ArrayValues(i) = ThisWorkbook.Worksheets(i).Name
   Next i
   With Sheets("Index").Range("A3").Validation
      .Delete
      .Add xlValidateList, , , Join(Application.Sort(ArrayValues, , , 1), ",")
   End With
End Sub

 

[luckyearl]

Sorry Fluff

Run-time error '1004': Application-defined or object-defined error → line. .Add xlValidateList, , , Join(Application.Sort(ArrayValues, , , 1), ",")

 

[Fluff]

Try it like

      .Add xlValidateList, , , Join(ArrayValues, ",")

although the list will not be sorted.

 

[luckyearl]

Hi
Debug.Print Join(ArrayValues, ", ") → this prints the list
.Add xlValidateList, , , Join(ArrayValues, ","). → Run-time error '1004': Application-defined or object-defined error

 

[Fluff]

How many sheets do you have?

 

[luckyearl]

25

 

[luckyearl]

I want to choose the worksheet, for use in this code. Here I have to navigate to sheet & activate it to run the code. If I can select the sheet, then I can do it from Index sheet. Thank you

Sub MyDateFilter()
    'Set Variable, got to declare them in advance'
    Dim lngStart As String, lngEnd As String, ws As Workshee
   
    Set ws = ActiveSheet      'Set active sheet to a variable, so u cud switch back to it'
    'Do The Filters,
    With ActiveSheet.Cells(1, 1).CurrentRegion    'CurrentRegion is dynamic''
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
    'Still working with Active sheet, Select A1, so wud include headers'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select       'Select row end'
    Range(Selection, Selection.End(xlDown)).Select          'Select column end, last cell in Column'
    Selection.Copy                  'Copy Selection'
    'If you do not want to paste, comment out from here to end'
Sheets("Index").Select      'Select workSheet to Paste'
Range("a12").Select         'Select cell where u want to paste to start'
Selection.PasteSpecial xlPasteValues    'Paste'

ws.Select   ' reSelect the worksheet to remove the Filter'
With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1      'no Filters'
    End With
End Sub