Macro to copy value in field and insert it into filter


Joined
Mar 8, 2018
Messages
2
Reaction score
0
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.
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
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
 
Last edited:
Ad

Advertisements

Joined
Mar 8, 2018
Messages
2
Reaction score
0
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top