1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel macro, moving target cell

Discussion in 'Business Applications' started by JBPHAN, May 3, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. JBPHAN

    JBPHAN Thread Starter

    Joined:
    Apr 27, 2010
    Messages:
    4
    hi

    my macro tells cell A2 on sheet1 to equate to cell D10 on sheet2. D10 on sheet2 is a total from the same sheet (sheet2) and sometimes can move to D8, D9 or D11. how do i incorporate the moving target cell into my macro?

    tim
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    How about a sample to illustrate your question? (no sensitive data)
    What are the 'move' conditions?
    What are the criteria for this 'moving target'?
     
  3. JBPHAN

    JBPHAN Thread Starter

    Joined:
    Apr 27, 2010
    Messages:
    4
    let me see if i can explain further before posting a sample

    there is a macro that basically deletes old data and recreates the worksheets everytime it's run. the new worksheets that are created contain line item data that is totalled at the bottom of each column. but because the data is cleared and the worksheets are recreated every time the report is run, the totalled numbers move from cell D10 one day to D9 the next and D12 the next. so that target cell moves and messess up the macro i have created that links the sheet1 cell to the moving target cell
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi I undestand exactly what you mean.
    I suggest the following.
    I don't know if you are familiar with the LIST option, it works like AutoFilter with that difference that you can choose to add totals, averages, counts, etc.
    Once you see what it does, I suggest to make sure you a new workseeht with all the data filled and then record a new macro to add the LIST and it's settings.
    Stop Recording and the look a the code.
    In this way I think you can use the created code, you'll have to edit it and you can add it to your macro so every time it starts on a new sheet, the list is cerated etc and if you chose the range the totals will always be al the bottom

    It's always simple to locate the las filled row:

    If you know that Column A is always the longest:

    The last filled row is Range("A" & Range("A65536").end(xlUp).row)).Select this for Office 2003
    In 2007 the number of rows is over 1.000.000 so you can choose the 2003 without a problem.

    I hope this helps to put you on the right track.
    What you can do it you can't get it doen is just post a sample with only the worksheet that is created to try an solve that part for you
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Was the tip of any use?
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/920903

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice