Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Maryland, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attached workbook is used to track server status (up time, down time). I am doing updates and tracking server maintenance down time and status on Sheet1. I would like to add a button C1 that will execute script to save the Sheet1 data table into sheet 2 and keep adding new records (copy and paste special values of Sheet1 table) to the bottom of the Sheet2 data table. As changes to status are recorded (and over written) on Sheet1, I want to click the button to add to the Sheet2 data store so that I can eventually do trend analysis over time.

    Thanks
    Huck
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Huck,

    See if this meets your needs.

    Just click the button when the record you want to move to history is selected.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Maryland, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG,
    That is what I am thinking. I was altering the data entry table and the archive table earlier. Applying your VBA wasn't a simple transfer. Take a look.

    Can you explain the Option Explicit function a bit. It seems if I make any changes (data entry) on the Servers worksheet any where on the worksheet it is added to the ServHistory worksheet with an OFFSET function dropping it to the bottom of the data range.

    There are some different things that I might want to do like add record entries or data columns to the master data table. Would archive table adjustments need to be made. You don't need to answer all that, but I'm just wondering how scalable it is.

    Is it possible to get some interaction with using the =TODAY() function previously in the? I want to use it as a paste special values date/time stamp for the Last Updated and possibly use it for the End Date/Time stamp as well. I'll have to play with the data schema I think to be able to graph the trend the way I want.

    Does =TODAY() have an interaction with a computer clock. The data changes from day to day, but the time does not seem to change?

    I was going to set up some kind of INDEX data table against the archive data set (even though it expands). Would I need some kind of dynamic named range to chart it? Again many questions that might be better if I break them up in the forum. (Please forgive me Lounge Moderator).

    Would this have been better posted in the VBA section vs Spreadsheets?

    Huck
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    UK
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Huck,

    I won't get involved with the wider conversation, but if you're looking to incorporate time as well as the date into things, then use =NOW() instead of =TODAY().

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Maryland, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mike,
    Thanks for the tip

    Huck

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Huck,

    Option Explicit forces you to declare all your variables. If you don't the VBE will complain and not let your run the code. This simply enforces good coding practice.

    It seems if I make any changes (data entry) on the Servers worksheet any where on the worksheet it is added to the ServHistory worksheet with an OFFSET function dropping it to the bottom of the data range. That is correct if you click the button while the record is selected. You can easily sort the ServHistory sheet on date as needed.

    There are some different things that I might want to do like add record entries or data columns to the master data table. Would archive table adjustments need to be made. You only need to adjust the archive table if you add columns to the master table.

    Is it possible to get some interaction with using the =TODAY() function. Depends what interaction you want and I'd consider the Now() function as mentioned by Rosoft Mile. And yes both Today() and Now() use the local computer date/time information.

    Would I need some kind of dynamic named range to chart it? That would be my recommendation.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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