+ Reply to Thread
Results 1 to 13 of 13

Macro - Filter and Copy Associated Content

  1. #1
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Macro - Filter and Copy Associated Content

    Evening all (from the UK),

    I am having a spot of bother with my spreadsheet, when trying to automate some functionality. As a heads up, I am proficient in Excel - but not on the programming/VGA side, so I will need a helping hand here.

    Effectively what I am trying to do is...
    - With a comprehensive Project Plan press a button that extracts the information of cells that are marked as Critical.
    - This information would pull through onto a separate Dashboard sheet, so that those critical items can get flagged to the Project Team.
    - The data cannot be copied as a complete table, as there are various columns of data that I do not require copying.
    - I have tried recording a macro with me 'filtering' the project plan for critical items and then copying that data across.
    - This however only returns the cells originally marked as Critical, it does capture any changes to cells outside of the range in the code.

    So,
    - In Column C of 'Project Plan' sheet, I have tasks marked as "Critical" or blank.
    - I want to copy data of those 'Critical' rows of data, from Columns B,D,F,I
    - This data is then to go into the 'Dashboard' sheet, in Columns B,C,E,F.

    I embed the code below, from my feeble attempt:


    Please Login or Register  to view this content.


    THANK YOU VERY MUCH IN ADVANCE. You are the best guys, really appreciate help of any kind. Once the urgency disappears, I think I'll be looking to start some VGA training. Any advice on a free place to start (I've done HTML coding before)?

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Macro - Filter and Copy Associated Content

    Try this...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    One other thought, if the destination columns have prior data in them, you might want your macro to clear those columns in preparation for the data being copied in.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Re: Macro - Filter and Copy Associated Content

    Thanks guys!
    Works almost perfectly, just two issues:

    - the header row (without the "Critical" reference) gets copied in, regardless of the cell references used.
    - the contents do not clear, as mentioned.

    Can this please be tweaked to accommodate this?
    Thank you ever so much!

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    My suggested tweaks:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Re: Macro - Filter and Copy Associated Content

    Thanks JB...here is the code I have now. Changing the cell references makes no difference, I should have said earlier. It still pulls in Row13 regardless of whether it is listed as a "Critical" item or not. I cannot for the life of me figure out why, despite having spent 20 minutes editing with trial and error.


    Please Login or Register  to view this content.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    Row 13 is where the FILTER is applied. Your COPY range should thus start at row 14. Notice in my original suggestion the Autofilter is one row above the copy range?


  8. #8
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Re: Macro - Filter and Copy Associated Content

    Haha I thought that was a typo - thank you for clearing that up.
    *crawls under a hard rock and hides*

    You're amazing - job done!

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    We are a village. (hat-tip)

  10. #10
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Re: Macro - Filter and Copy Associated Content

    Last thing (I hope).

    When clearing the cells, I am trying to reinsert a formula (which can be overridden with a RAG status), for specific cell references within the dashboard (Cells G12:G200).

    I've had a go at coding this based on another application but VBA Is struggling to recognise this:

    Please Login or Register  to view this content.

    Full code is here:


    Please Login or Register  to view this content.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    Inside a WITH..... END WITH section, you start the range statements with a period to connect them to the WITH statement. Look back at my tweaks and you'll spot that syntax.

    You also don't need to autofill if you already know the range of cells you want to enter.

    So:

    Please Login or Register  to view this content.
    ...inside the WITH statements.
    Last edited by JBeaucaire; 06-20-2014 at 10:21 AM.

  12. #12
    Registered User
    Join Date
    03-04-2004
    Posts
    12

    Re: Macro - Filter and Copy Associated Content

    You beauty JB, think we can claim this one is solved now - although I will start a new & final request separately. Thanks for your help!

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro - Filter and Copy Associated Content

    As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
    FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop through an existing filter and copy and paste content into separate workbook
    By moyay4 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-21-2014, 11:39 AM
  2. Copy Content from IE Macro
    By frank35 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2013, 10:33 AM
  3. [SOLVED] Help with Autofiltered content copy macro
    By vpnvipin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2012, 07:08 AM
  4. Macro to filter content and show not found info
    By Nachol in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2012, 12:49 PM
  5. Need macro to filter, create tab on filter and copy/paste
    By Jen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2006, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1