This might be an easy fix, but I don't know how to add an input box to collect a variable word for VBA to search with.
My main page, "CAA" has variable locations, in which a new sheet needs to be created to export the filtered data to... My crude macro so far below..
The goal is to extract specific information from the master data (CAA), which in this case is by state. Once the State has been filtered, I need to copy this data to another worksheet, so the new worksheet (Macro Test) will have only data for that particular state.
This needs to be done for all States. Short of writing a macro for each state, I would like an option where the macro will prompt for a filter requirement, where I can enter the state (eg VIC, NSW, WA, etc)... After hitting enter, the Macro continues along until the new worksheet is created with the states details.
If the below macro can be improved upon (which I am certain it can be), I'd also be very thankful .. however my main goal is incorporating an input field or drop down selection box to dynamically run the macro based on the selection/input. if entered data doesn't exist (eg CIV is entered instead of VIC), then the macro should ideally fail and return an error.
The data I am working with is quite sensitive, so I am unable to attach an example. Hope someone can help me out
Sub Macro1()
Sheets("CAA").Select
Columns("A:D").Select
Selection.Columns.Group
Columns("F:N").Select
Selection.Columns.Group
Columns("Q:AS").Select
Selection.Columns.Group
Columns("AU:BH").Select
Selection.Columns.Group
Columns("BK:BS").Select
Selection.Columns.Group
Columns("BU:CB").Select
Selection.Columns.Group
Sheets("CAA").Select
Selection.AutoFilter Field:=62, Criteria1:="VIC"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Macro Test").Select
Range("A1").Select
ActiveSheet.Paste
Selection.AutoFilter
Range("B2").Select
ActiveWindow.FreezePanes = True
Rows("1:2").Insert Shift:=xlDown
Sheets("Macro Test").Select
Columns("A:D").Select
Selection.Columns.Group
Columns("F:N").Select
Selection.Columns.Group
Columns("Q:AS").Select
Selection.Columns.Group
Columns("AU:BH").Select
Selection.Columns.Group
Columns("BK:BS").Select
Selection.Columns.Group
Columns("BU:CB").Select
Selection.Columns.Group
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
End Sub
Bookmarks