Macro to copy value in field and insert it into filter

[corbin brown]

I am not VBA knowledgeable but I did figure out how to record macros. What I have built is a demand analysis workbook based on demographics and spending. I have three sheets that have data on them based on CBSAs. What I am trying to do is after looking up the CBSA for an address and typing it into an input sheet, I am trying to copy that 5 digit integer into the filter for the CBSA column in the 3 sheets and filter the results, then copy those results to other sheets with formulas to extract the info and ultimately copy the latitude-longitude coordinates into new excel workbooks. I got the new workbooks to thing working just fine. My problem is, every time I change the CBSA and run the macro the filters filter out the original CBSA integer I used when recording the macro.

What I need is a static way to get this to work where when there is a new CBSA entered, It filters on that integer and spits out the sheets with the filtered Coordinates. In excel, it would be called a wildcard, but my vba knowledge goes only as far as recording a macro, which apparently is almost useless.

here is the portion of the code where I select the CBSA number and go through my steps of filtering and copying the data to new sheets.

Sheets("Inputs").Select
Range("C16").Select
Selection.Copy
Sheets("Hospital").Select
ActiveSheet.Range("$A$1:$AJ$8213").AutoFilter Field:=15, Criteria1:= _
"12060 - Atlanta-Sandy Springs-Roswell GA"
Range("A204:AJ204").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hospital Input - Market").Select
Range("A5").Select
ActiveSheet.Paste
Range("K5:L5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Map Data Hospital").Select
Range("A2").Select
ActiveSheet.Paste

This is something I do about 6 times a week and the whole process takes about 2 hours. I am trying to get as much of it automated as possible. I didnt think it would be so hard to get a macro to copy paste filter copy paste select copy new sheet paste and repeat 4 times but I have been trying to figure it out for 2 months now. Somebody help me please.

 

[AmjiBhai]

Hi,

Although you have explained your issue very well, Would you please let me see your worksheet so that I will be able to come up with an effective solution.

But still I can make certain modifications in your recorded-macro.
Please try this and let me have your feed back.


Sheets("Inputs").Select

Range("C16").Select
Selection.Copy

Sheets("Hospital").AutoFilterMode = False
crit1=inputbox("Paste your criteria in this box",,"12060 - Atlanta-Sandy Springs-Roswell GA")

Sheets("Hospital").Select
lr = ActiveCell.SpecialCells(xlLastCell).Row
ActiveSheet.Range(ActiveSheet.UsedRange).AutoFilter , Criteria1:= _
Crit1

Range("A2:AJ" & lr).Select

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Sheets("Hospital Input - Market").Select
Range("A5").Select
ActiveSheet.Paste
Range("K5:L5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Map Data Hospital").Select
Range("A2").Select
ActiveSheet.Paste

 

[corbin brown]

Update. I figured it out. I recorded a macro today I’m using advanced filter and it works perfectly. Even got it to open new excel books and copy the data to them and save them with labels in a folder on my desktop.