+ Reply to Thread
Results 1 to 17 of 17

delete previous months data

  1. 02-15-2009, 11:40 AM #1
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    delete previous months data

    OK essentially this is my problem:
    I have a daily spreadsheet, uou update for inputs and outputs each day and the spreadsheet gets to an overperformance figure (sorry if the spreadsheet is a lottle unclear, it is a simplified version)

    On the first day of each month, the opening target is adjusted and you start again. The probelm is you dont know what the opening target is until roughly 21st of the month.

    I created a macro that deletes the opening target and removes all the previous days before the start of the month.
    The way i done this was:
    Create a copy of "daily" tab, change b5 to equals c2 and copy and paste special as values. Then in d5-d58 and f5 - f58, vlookup values from old "daily" tab , and then copy and paste special as values. Then delete old "daily" tab and remname daily(2) as "daily"

    What i have done is essentially created a new tab on 21st March, looking back to data only as far back as 1st Mar and the new opening bal and target is found. The macro can be used on the 21st of each month.

    but...macro doesnt work, i have tried a few times and i think it is because i am vlookuping up data from a tab that i later delete...please help!! - thanks a mil to anybody that can give me a steer in the right direction.

    Sorry if i am unclear, please feel free to ask for clarification
    Attached Files Attached Files
    Last edited by karloss; 02-16-2009 at 12:21 PM.
    Reply With QuoteRegister To Reply
  2. 02-15-2009, 12:07 PM #2
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: delete previous months data

    You cannot delete a sheet that VLOOKUP requires, can you hide it instead?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  3. 02-15-2009, 12:12 PM #3
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    re: delete previous months data

    I dont think so, i would run into problems the following month because the vlookup would look at the old data. Thats why i paste special as values, delete the old tab and rename the new tab the same as the old tab. I could then repeat the same month without problems.
    Reply With QuoteRegister To Reply
  4. 02-15-2009, 12:29 PM #4
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: delete previous months data

    I've had a look at your code. Recording code is a great way to start, but remember the code can be more fficient. You can start by deleting all the lines that contain SmallScroll.

    I've tried to interpret what you are doing & tidy it up

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-15-2009, 01:10 PM #5
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    re: delete previous months data

    Thanks, i will try this in work...i am on my laptop with a different version of excel which doesnt help.

    It doesnt appear to be working, before i run the macro, the figures for 1st March are 29 and 30. When i run the macro, the first date is 1st March (taken from c2) but the input and output still appear as 1 and 2 which is incorrect?
    Last edited by karloss; 02-15-2009 at 01:30 PM.
    Reply With QuoteRegister To Reply
  6. 02-15-2009, 01:26 PM #6
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: delete previous months data

    I think this cures that problem
    Please Login or Register  to view this content.
    Version shouldn't matter with this code.
    Reply With QuoteRegister To Reply
  7. 02-15-2009, 01:34 PM #7
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    re: delete previous months data

    Thanks a mil for your help, this is looking better, i ran your new version and inputs are correct but nothing is being shown for outputs.
    Reply With QuoteRegister To Reply
  8. 02-15-2009, 01:47 PM #8
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    re: delete previous months data

    I put in another vlookup row starting ...
    .Range("f5:f54").Formula = _
    which looks to be working. My only other issue (until i run it in work!) is that where there was no figure in the old tab, it is being imported as zero which is not correct. Can we put in a rule that says if nothing is in the cell, it wont update as 0, but as nothing??
    Reply With QuoteRegister To Reply
  9. 02-15-2009, 01:47 PM #9
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: delete previous months data

    It uses your formula

    "=IF(ISERROR(VLOOKUP(RC[-4],daily!R5C2:R58C6,5,0)),"""",VLOOKUP(RC[-4],daily!R5C2:R58C6,5,0))"

    So if there's an error generated by the formula it will not show up

    Edit:I don't think your formula is correct. It initially throws a ~Ref error, but without that it would have a circular reference.
    Last edited by royUK; 02-15-2009 at 01:52 PM.
    Reply With QuoteRegister To Reply
  10. 02-15-2009, 01:52 PM #10
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    re: delete previous months data

    exactly, but if there is not an error and the cell is blank, it will bring in 0, which throughs out my report a little. Sorry an not very experienced at this, your help is great

    I believe the formula is OK, it only shows a bad reference where there is no date for the vlookup in the old tab. This is fine as the dates are rolling, so the formula just ignores any date that is longer
    Last edited by karloss; 02-15-2009 at 01:58 PM.
    Reply With QuoteRegister To Reply
  11. 02-15-2009, 02:43 PM #11
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: delete previous months data

    I've found the error, it's not the formula.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 02-16-2009, 05:29 AM #12
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    Re: delete previous months data

    Can someone adjust the formula above if my range is C7:C60 and E7:E60.
    Thanks, i feel stupid but it is not working for me...
    Last edited by karloss; 02-16-2009 at 05:41 AM.
    Reply With QuoteRegister To Reply
  13. 02-16-2009, 05:41 AM #13
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: delete previous months data

    There is no formula
    Reply With QuoteRegister To Reply
  14. 02-16-2009, 05:49 AM #14
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    Re: delete previous months data

    Sorry Roy, the code above looks at the range D5:D58 and F5:F58, vlookups from the old tab and pastes. I am adapting this to my actual spreadsheet but the ranges are C7:C60 and E7:E60. I just cant get it to work though
    Reply With QuoteRegister To Reply
  15. 02-16-2009, 06:11 AM #15
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: delete previous months data

    Can you attach the correct workbook?
    Reply With QuoteRegister To Reply
  16. 02-16-2009, 06:26 AM #16
    karloss
    karloss is offline
    Registered User
    Join Date
    11-07-2007
    Posts
    18

    Re: delete previous months data

    OK, please see attached
    Last edited by karloss; 02-16-2009 at 12:21 PM.
    Reply With QuoteRegister To Reply
  17. 02-16-2009, 07:14 AM #17
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: delete previous months data

    Try this
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1