Copy and paste command toggle.

[robdawg]

I am new to Macros and VBA but have quickly realized the potential and I am working on automating a task at work.

I would like to use a command Toggle button to copy data from one sheet into the next sheet every 30 min. The first sheet" Import sheet" is updated every 30 min with new data. I want to take that data and place it on a time line for as long as the command toggle is pressed. The toggle will be pressed on every morning and off every night. There are 5 toggle buttons per work sheet for 5 days of the work week and there will be 52 worksheets.

Example:

Copy from "Import worksheet", B2, C2, E2.
Paste the data only to the worksheet with the toggle in range B7:D7
Then 30 min Later to B8:D8
Then 30 min Later to B9:D9
etc.

Until the toggle is pressed off.

To save time I would like to build one work sheet with 5 Different Values and then simply copy the entire worksheet 52 times.


Any help would be greatly appreciated.

 

[Rijnsent]

Hi robdawg,
that sounds complicated... What I would do: have a macro in that workbook that runs every 1/2 hour as long as the workbook is open. The macro itself can figure out what day of the week it is and what the last line with stored information is (no need for 5 toggle buttons).
From these two posts below some code (not tested):
http://www.mrexcel.com/forum/excel-questions/45395-procedure-macro-run-automatically-every-hour.html
9. VBA Tips - Run Code Every Hour, Minute or Second | ExcelExperts.com

Private Sub Workbook_Open()
    'Activated on workbook opening
     Call StoreInfoTimer
End Sub

Sub StoreInfoTimer()
   RunNow = Now + TimeValue("00:30")
   Application.OnTime RunNow , "StoreInfo"
End Sub

Sub StoreInfo()
    StoreSht = Worksheets("Sheet2")
    last_rw = StoreSht.Range("A1").End(xlDown).row
    StoreSht.Range("A" & last_rw +1).value = Now()
    StoreSht.Range("B" & last_rw +1).value = Worksheets("Import worksheet").Range("B2").value
    StoreSht.Range("C" & last_rw +1).value = Worksheets("Import worksheet").Range("C2").value
    StoreSht.Range("D" & last_rw +1).value = Worksheets("Import worksheet").Range("E2").value
    Call StoreInfoTimer
End Sub

That should kind of do the trick (as said, untested ).
Cheers,
Koen