ListBox From Macro

[eckert1961]

Hello,

I recorded the following macro that filters the active worksheet copies the visible data only and pastes this into a new workbook.

Range("G7").Select
Selection.AutoFilter Field:=1, Criteria1:="SWD Nearline"
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select

In G7 I have listbox with the following criteria.

SWD Blades
SWD NAS
SWD Nearline
SWD SAN
SWD Software

What I would like to do with this macro is to have it present a listbox with the noted criteria and wait for the users selection.

Would anyone have any ideas on how this can be accomplished?

Thanks in advance.

Regards,
Chris

 

[eckert1961]

I had another idea that someone maybe able to assist me with. I already have a listbox created in column G7. When I make a selection it filters it appropriately. What I would like to do is when a user makes a selection a macro would run that would prompt them if they want to just display the filtered output as it does currently or copy the visible cells to a new workbook. Any ideas?

Chris

 

[eckert1961]

Problem Solved!!!

I realized that I'm making this far too difficult as I already have the listbox created for Range("G7"). I thought a more practical approach was to have the user filter the data using the listbox and then run a macro that will copy the filtered data to another workbook. To make it easier for the user to run the macro I assigned it to a toolbar command button. Here is the macro.

Dim varResponse As Variant
varResponse = MsgBox("Do you want to copy data to new workbook?", vbYesNo)
If varResponse = vbNo Then
Exit Sub
End If
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("A1").Select
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Name = Range("G8").Value
Application.CutCopyMode = False
End Sub

I hope that someone else finds this useful. I would also appreciate any feedback from some of you vba wizards on any suggestions that could improve this macro.

Regards,
Chris