List sheet names in "pop-up" box for users to select

[Steve]

Hello All. I have a bit of code below (compliments of Garry S) that
takes rows that meet a certain criteria and chunks them to a different
worksheet. In this code, the specvific "target" worksheet is
explicitly names in the code.

Is there a way to execute the code, have a "pop-up" box listing all
the current sheet names in the workbook, and allow the user choose
which worksheet to chunk the data to? So in the code below, the Set
wksTarget = Sheets("East") would be dynamic based on user input?

Thank you!


Dim wksSource As Worksheet, wksTarget As Worksheet

Set wksSource = ActiveSheet: Set wksTarget = Sheets("East")
Application.ScreenUpdating = False
With wksTarget
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearContents
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearComments
.Rows("1:" & CStr(.UsedRange.Rows.Count)).Interior.ColorIndex =
xlNone
End With
With wksSource
.Columns("K:K").AutoFilter Field:=1, Criteria1:="Y"
.UsedRange.Copy wksTarget.Rows("1:1")
.Columns("K:K").AutoFilter
End With
Application.ScreenUpdating = True

 

[GS]

Steve,
This will require a userform containing a listbox that gets populated
with sheetnames in the userform's Initialize event. Clicking a name in
the list then puts the selection into a global variable and unloads the
form. Your code now uses the variable in place of the literal sheetname
string.

==========================================
Code to load the sheetnames into Listbox1:

Private Sub Userform1_Initialize()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
If wks.Visible Then ListBox1.AddItem wks.Name
Next 'wks
Me.Caption = "Select Target"
End Sub

===========================================
Code to load the sheetname into global var:

Private Sub ListBox1_Click()
gsWksTargetName = ListBox1.List(ListBox1.ListIndex)
Unload Me
End Sub

**Make the listbox fill as much of the userform as needed to mimic a
scrollable popup list. width of both should be sufficient so as not to
cause the listbox to display its horizontal scrollbar. (This will
require the listbox to have margin around left, right, and bottom)

=====================================
Replace this existing line of code...
Set wksSource = ActiveSheet: Set wksTarget = Sheets("East")

with...
Userform1.Show '//get wksTarget sheetname
Set wksSource = ActiveSheet: Set wksTarget = Sheets(gsWksTargetName)

===================================================
Put this in a standard module declarations section:

Public gsWksTargetName As String

HTH