Sheetname used in Macro Autochange as per time

[Vbalearner85]

Hi,

I have a macro which copies data from sheet "Cdata" to "Ddata" and then loop it with per sec "timer1" macro. I do the same action with three other separate macros based on time to change copying data to 3 other sheets (Edata/Fdata/Gdata)

My question is how auto change the copy destination sheetname (from Ddata to (Edata/Fdata/Gdata) in the same macro based upon below time conditions, instead of using multiple macros

If TimeSerial(9, 0, 1) <= Time <= TimeSerial(10, 0, 0).Value Then ws = "Ddata"
If TimeSerial(10, 0, 1) <= Time <= TimeSerial(11, 0, 0).Value Then ws = "Edata"
If TimeSerial(11, 0, 1) <= Time <= TimeSerial(12, 0, 0).Value Then ws = "Fdata"
if TimeSerial(12, 0, 1) <= Time <= TimeSerial(13, 0, 0).Value Then ws = "Gdata"

Sub Ddata()

Dim lastrow As Long

With ThisWorkbook
    .Sheets("Cdata").Range("AA1").ClearContents
    lastrow = .Sheets("Ddata").Range("A1048576").End(xlUp).Row
    .Sheets("Ddata").Cells(lastrow + 1, 1).Resize(141, 25).Value = .Sheets("CData").Range("A2:Y142").Value

End With

Call Timer1

End Sub

 

[Puertorekinsam]

I think this will work for you

Sub Ddata()

Dim lastrow As Long

Dim ws as worksheet

With ThisWorkbook
    If TimeSerial(9, 0, 1) <= Time <= TimeSerial(10, 0, 0).Value Then  set ws = .sheets("Ddata")
    If TimeSerial(10, 0, 1) <= Time <= TimeSerial(11, 0, 0).Value Then set  ws = .sheets("Edata")
    If TimeSerial(11, 0, 1) <= Time <= TimeSerial(12, 0, 0).Value Then  set ws = .sheets("Fdata")
    if TimeSerial(12, 0, 1) <= Time <= TimeSerial(13, 0, 0).Value Thenset   ws = .sheets("Gdata")


   ws.Range("AA1").ClearContents
    lastrow = ws.Range("A1048576").End(xlUp).Row
    ws.Cells(lastrow + 1, 1).Resize(141, 25).Value = ws.Range("A2:Y142").Value

End With

Call Timer1

End Sub

 

[Vbalearner85]

Hi,

Stops on this line with runtime error
With ThisWorkbook
If TimeSerial(9, 0, 1) <= Time <= TimeSerial(10, 0, 0).Value Then set ws = .sheets("Ddata")

Also, just curious how are we referencing sheet "Cdata" which is data source sheet to copy to the destination sheets.

Thanks

 

[Puertorekinsam]

Sorry, I left out the () on time, and accidently removed the sheet Cdata

Sub Ddata()

Dim lastrow As Long
Dim ws As Worksheet



With ThisWorkbook


    If TimeSerial(9, 0, 1) <= Time() <= TimeSerial(10, 0, 0) Then Set ws = .Sheets("Ddata")
    If TimeSerial(10, 0, 1) <= Time() <= TimeSerial(11, 0, 0) Then Set ws = .Sheets("Edata")
    If TimeSerial(11, 0, 1) <= Time() <= TimeSerial(12, 0, 0) Then Set ws = .Sheets("Fdata")
    If TimeSerial(12, 0, 1) <= Time() <= TimeSerial(13, 0, 0) Then Set ws = .Sheets("Gdata")
    .Sheets("Cdata").Range("AA1").ClearContents
    lastrow = .Sheets("Ddata").Range("A1048576").End(xlUp).Row
    ws.Cells(lastrow + 1, 1).Resize(141, 25).Value = .Sheets("CData").Range("A2:Y142").Value

End With

You also might want to error code for anything between 1:01PM and 8:59AM

 

[Vbalearner85]

Not quite there,

My original macro pastes data from sheet "Cdata" into Ddata in blank row starting from row 2 (and move down), and so on every sec

Above macro only pastes data in tab "G data" irrespective of time in conditions, and that too in starting 142 rows.(replaces data in same rows)..does not move down for next blank row every 1 sec.

 

[Puertorekinsam]

Your time serial code you originally indicated says you want to use the sheet DData when the time is between 9:00AM and 10:00AM and EData between 10:00AM and 11:00AM. etc...
I assume you want these between 9:00 and 9:59,10:00-10:59 etc... so I changed the <= to just < for the second value in the if statements.

What are you doing if the time is before 9AM or after 1PM?

If this doesn't work, I'm going to have to concede to someone else to try.

Sub Ddata()

Dim lastrow As Long
Dim ws As Worksheet



With ThisWorkbook


    If TimeSerial(9, 0, 1) <= Time() And Time() < TimeSerial(10, 0, 0) Then Set ws = .Sheets("Ddata")
    If TimeSerial(10, 0, 1) <= Time() And Time() < TimeSerial(11, 0, 0) Then Set ws = .Sheets("Edata")
    If TimeSerial(11, 0, 1) <= Time() And Time() < TimeSerial(12, 0, 0) Then Set ws = .Sheets("Fdata")
    If TimeSerial(12, 0, 1) <= Time() And Time() < TimeSerial(13, 0, 0) Then Set ws = .Sheets("Gdata")
    .Sheets("Cdata").Range("AA1").ClearContents
    lastrow = ws.Range("A1048576").End(xlUp).Row
    ws.Cells(lastrow + 1, 1).Resize(141, 25).Value = .Sheets("CData").Range("A2:Y142").Value

End With

Call Timer1

End Sub

 

[Vbalearner85]

Sir,

Excel file auto opens within these specified timelines only and closes after data is copied for these 4 macros. I have other bunch of macros which do these tasks.

Issue with above code are:-
1) Not doing intented task as original macro - copy data from sheet "Cdata" to destination sheet in next available row
2) Macro copies data only in sheet Gdata irrespective of time condition(taking only last condition).I tried using elseifs(that should be ideally used based upon my idea) in the same...somehow not yet able to resolve.

Thanks for your efforts..Regards PK

 

[Vbalearner85]

MS Excel VBA - ScreenUpdating False not working Excel 2016/2019

I have below macro to run for file "Data.xlsm" to copy data from sheet "Cdata" to "Ddata". I want this code to run without going to the sheets (to not show the vba actions)..but it is not working..Any suggestions as to how to make it happen ..so that I can work on some other tab in the same...

www.mrexcel.com

This solves the issue..went back to my old thread.

Regards