Macro to copy value in field and insert it into filter

Discussion in 'Microsoft Office' started by corbin brown, Mar 8, 2018.

  1. corbin brown

    corbin brown

    Joined:
    Mar 8, 2018
    Likes Received:
    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.
     
    corbin brown, Mar 8, 2018
    #1
    1. Advertisements

  2. corbin brown

    AmjiBhai

    Joined:
    Feb 21, 2018
    Likes Received:
    37
    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: Mar 9, 2018
    AmjiBhai, Mar 9, 2018
    #2
    Becky likes this.
    1. Advertisements

  3. corbin brown

    corbin brown

    Joined:
    Mar 8, 2018
    Likes Received:
    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.
     
    corbin brown, Mar 10, 2018
    #3
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.