+ Reply to Thread
Results 1 to 5 of 5

VBA to enter date range based on date range in above cell

  1. #1
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    VBA to enter date range based on date range in above cell

    Hi All,
    I was wondering if it is possible to have my macro also enter the next dange range when it inserts a new cell range.
    It is currently called in the worksheet module when cell A1 is selected:

    Please Login or Register  to view this content.
    Which executes the macro:

    Please Login or Register  to view this content.
    With the logic here being to copy the first row of data then paste it off the viewing area (in this case column T) for historical data, then to move the data up one row then clear the contents of the bottom row.
    I also would like the next work weeks date range (Monday through Friday) to be inserted in Range A31:D31 regardless of what the actual date is. This data feeds a rolling 30 week trend chart.

    Any help would be greatly appreciated, thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-15-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Office 365
    Posts
    282

    Re: VBA to enter date range based on date range in above cell

    Hi Pierce

    Could you describe the business proposition that you are dealing with. What you have is a whole load of purely VBA questions that don't (yet!) make sense. If you can tell us what you're trying to accomplish, this would help. As an example of some of the confusion - you seem to call the Refresher routine only if the value of cell A1 changes. This is the caption "Dates". Now I can imagine someone needing to change this from Dates, to Monkeys or even Barrels of Cheese, but I don't see why you would then need to manipulate the rest of the sheet.

    Also, you reference cell T3, which doesn't have anything in it (at least to begin with).

    If you can tell us what the spreadsheet is about, and what you are trying to accomplish, then maybe we can help solve your problem, rather than trying to make stabs in the dark at the VBA!

    HTH

    Tony

  3. #3
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to enter date range based on date range in above cell

    Quote Originally Posted by tfurnivall View Post
    Hi Pierce

    Could you describe the business proposition that you are dealing with. What you have is a whole load of purely VBA questions that don't (yet!) make sense. If you can tell us what you're trying to accomplish, this would help. As an example of some of the confusion - you seem to call the Refresher routine only if the value of cell A1 changes. This is the caption "Dates". Now I can imagine someone needing to change this from Dates, to Monkeys or even Barrels of Cheese, but I don't see why you would then need to manipulate the rest of the sheet.

    Also, you reference cell T3, which doesn't have anything in it (at least to begin with).

    If you can tell us what the spreadsheet is about, and what you are trying to accomplish, then maybe we can help solve your problem, rather than trying to make stabs in the dark at the VBA!

    HTH

    Tony
    Hello Tony, thanks for the reply!

    The routine exectues when cell A1 is selected not when the value of the cell changes. Any labels in row 1 are meant as headers for the data, not the data itself.

    There is nothing in T3 because the macro has not been executed yet in the attached book. This will be where the historical data will be located.

    The concept behind this is to enter data every week for that date range in columns B and C. When the 30 week table is filled, the user will select cell A1 to run the macro, the oldest weeks data will be copy and pasted in another location for historical reference, deleted, the rest of the data will be moved up one row, leaving the last row open for new data, hence"refreshing" the table.
    What I am looking for is that after all this happens, the next weeks date range is automatically pasted in the open A31 cell.

    Thanks!

  4. #4
    Forum Contributor
    Join Date
    11-15-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Office 365
    Posts
    282

    Re: VBA to enter date range based on date range in above cell

    Aaah!

    Now it makes sense!

    So what we want to do is to shift the top cell out, into a different area, move all the other rows up, and then add a new row at the bottom, right?

    So that's more or less what we code. (Incidentally your present data has only 29 rows, so I'm guessing that at this point you simply add a line and do no transfer, right? - I've modified the SelectionChange routine to reflect this).

    **Just one word of warning - your trigger is VERY sensitive. Any time you do a HomeUp you will transfer a row of data if
    the table is full. (I've backed up your 29 rows of data to allow you to try it more than once!)


    Please Login or Register  to view this content.
    If you have any questions, please let me know!

    HTH

    Tony

  5. #5
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: VBA to enter date range based on date range in above cell

    Quote Originally Posted by tfurnivall View Post
    Aaah!

    Now it makes sense!

    So what we want to do is to shift the top cell out, into a different area, move all the other rows up, and then add a new row at the bottom, right?

    So that's more or less what we code. (Incidentally your present data has only 29 rows, so I'm guessing that at this point you simply add a line and do no transfer, right? - I've modified the SelectionChange routine to reflect this).

    **Just one word of warning - your trigger is VERY sensitive. Any time you do a HomeUp you will transfer a row of data if
    the table is full. (I've backed up your 29 rows of data to allow you to try it more than once!)


    Please Login or Register  to view this content.
    If you have any questions, please let me know!

    HTH

    Tony
    Hello Tony, thanks for the reply!
    In the actual sheet, the top row is frozen so cntrl-home isnt an issue.
    Thank you for the code, as well as the abundance of notes!
    Ill be running some tests after the first of the year, I will catch up with you then!
    Thanks again!

+ 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. [SOLVED] Enter Value of cell within a date range
    By keithnrhonda in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-21-2013, 12:34 PM
  2. [SOLVED] Date Filter based on Cell Input - single date or range of dates
    By babaloo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2012, 10:32 AM
  3. Max, Min date range based on another cell range
    By va_san in forum Excel General
    Replies: 2
    Last Post: 06-18-2010, 01:46 PM
  4. copy date based on date -refer to date range
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2006, 08:35 AM
  5. [SOLVED] How do I enter a date range ie -7 from current date in MS QUERY
    By notsmartenough in forum Excel General
    Replies: 1
    Last Post: 11-11-2005, 06:25 PM

Tags for this Thread

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