Macro must read changing cell content

  • Firstly I am something of a novice at Macros – I have to use the recorder to create them as writing VBA is a bit of a mystery. Please bear this in mind if you choose to reply to my query.
    My macro operates a series of steps which start by applying a filter to a data input sheet.
    It takes a number from a cell on a different sheet and uses that in the filter.
    My problem is that the ‘recorder’ has recorded the cell (E2) as the one to copy. It currently (at the time of recording the macro) contained “003”. The following week’s data means that ‘E2’ may contain a different number eg “010”. But when the macro runs it still ‘sees’ “003”.
    It then goes onto read cell “F2” on the same sheet and has the same problem with the contents “003Wk17”
    I need the macro to recognise the contents of the cell at the time the macro runs rather than staying with what was in the cell at the time of the recording the Macro.
    I have included my coding to help
    Any help will be gratefully received.

  • Re: Macro must read changing cell content

    Your problem is that when recording the macros you've recorded the hardcoded values rather than the reference cell for those values.

    See all the bits that say "003WK17" or "003" in your code, those are the values that were in the cell at the time of recording, so it's not that it's not seeing that you've changed the values in the cell, it's more that it's not even looking as you've told it the values are "003WK17" or "003".

    The Macro recording feature doesn't always record in the way someone would code it, but it's useful for giving you a pointer to the right commands.

    If you want to post the actual workbook it may be easier for someone to take a stab at it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!