VBA: Pull data based on time frame from Raw data or pivot table to dashboard sheet column

Dear Experts,
Please assist on this request.
I kindly need the codes for extracting TURNS for a specific date and time from the database to Dashboard sheet under column B.
(26/11/2018 06:01:00 to 26/11/2018 17:59:00)

Should i extract the values from RawData or PivotStage sheet?
Please advise.

DashboardNew.PNG

PivotTable1.PNG

Raw Data.PNG

Hi All,
To be more specific:-

I would like to capture the values/turns from either Raw data or Pivot table on this particular time frame as shown below.
How can i accomplish this task?
Please let me know.


e.g

StartTime = 4/12/2018 06:01:00
EndTime = 4/12/2018 17:59:00

Please help to solve this task..
i've been at it and it's really tough..
Any idea pop out, do share with us..
Thank you.

Attached is the similar issue.
Summary, this Dashboard sheet will accumulate real-time values/turns from 6:01:00 to 17:59:00 and loop to the next time frame of 18:01:00 to 05:59:00.
It will continue to loop on both of this time frame.


https://www.excelforum.com/excel-pro...iod-print.html

I need to loop a macro to add X values of rows whenever it's being updated from the database to a column.
Is it possible?
Please help to assist.
Thank you.

Any ideas or input to accomplish this task?

This is what i have so far.
How can i sum up the values in C column(ENDTIME) under "RawData" following on this time frame?

I'm already out of ideas..


Sub test1()
Dim FDate As Range, FF As Range
Dim FVal As Range
Dim WkRg As Range
Dim WS As Worksheet

Const StartTimeNight As Date = #6:01:00 PM#
Const EndTimeNight As Date = #5:59:00 AM#
Const StartTimeDay As Date = #6:01:00 AM#
Const EndTimeDay As Date = #5:59:00 PM#
Const ValStg As String = "0"
Set WS = Worksheets("RawData")

With WS
Set FDate = .Cells.Find(What:="Time", After:=Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

If (FDate Is Nothing) Then Exit Sub
Set WkRg = .Range(FDate.Offset(1, 0), Cells(Rows.Count, FDate.Column).End(xlUp))

Set FVal = .Cells.Find(What:=ValStg, After:=Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If (FVal Is Nothing) Then Exit Sub

Anyone has suggestion?

I'm still stuck on solving this task..
Anybody up for a challenge or provide a suggestion

Dear Experts,
I really need your assistance on this task.
Please have a look and thanks in advance.

Tasks: 1). To pull current accumulated values in the column(ENDQTYMAIN) from database("RawData") WS to another WS("DASHBOARD") in the column(TURNS) based on criteria which would be
STAGE column for both WS.
2). DASHBOARD WS under TURNS column will have the current accumulated values based on current time frame which will be from 12/9/2018 18:01:00 to 12/10/2018 05:59:00 and another
time frame which will be from 12/10/2018 06:01:00 to 12/10/2018 17:59:00.
3). It will continue to loop for the next time frame and day.

DashboardNew.PNG

Raw Data.PNG

PCI,
Here you go.

FinalFilm.xlsm

PivotStage is extracted from RawData sheet.
I will attach a new updated file.
It's supposed to update based on this time frame and for the next following days.

FinalFilm.xlsm

It means when the connection is being updated every minute based on current live raw data, DASHBOARD sheet under "TURNS" will keep on accumulating values till the end time.

Hi PCI,
I've tried but came up with an error as shown below.

Error.PNG

This is the latest error shown.

ErrorNew.PNG

With pop out display error when i hit the "update" tab.

Pop Out.PNG

I haven't read all through the thread, but based on your post #15 in previous page.

Is this kind of what you are after?
Just a demonstration purpose.

Please Login or Register  to view this content.

I've attached the file for your reference on the requirement needed.

It's supposed to capture the values from that time frame and loop to the next time frame.(under PivotStage)
Dashboard will update the accumulative values from the current database based on this current time frame.(live feed)
I don't require for yesterday values but only for today and the following days.

It means that Dashboard values under TURNS will reset to "0" whenever it hits at 06:00:00 and 18:00:00. Next, it will accumulate for the current/live or NOW values into the Dashboard based on this time frame given.
Continue to loop for the next following days,weeks and months based on this time frame without hitting any tab button.

I'm really sorry for the confusion statement.
I do hope it clears your doubt and really appreciate your effort and time spent on this task.
Kudos!!


Thank you.

FinalFilm2.xlsm

Yea, it is working fine but when i edited the st and et and click "update", pivot table did not give the correct values based on that time frame.

my statement #24 is referring to statement#22.

Hi PCI,
I stand corrected on my statement#24. It is working fine with the current values from 12/10/2018 06:01:00 to 12/10/2018 17:59:00.
But i don't understand why is the Endtime refers to yesterday date to obtain today's date as mentioned above.

st.et.PNG

Hi Jindon,
Thanks for your offer to help.

Dashboard sheet under TURNS will keep on accumulating values (similar to Pivot table display) and loop to the next days within this two time frame based on current/NOW time and date.

Still not sure...

If you want to get only the time frame of 6:01:00 to 17:59:00 on Today then...

Please Login or Register  to view this content.

PCI,
Awesome.
It works perfectly now.

How can i accomplish a live dashboard without clicking on "update" tab?
I would like to have a live dashboard under TURNS based on current time.
It will continue to loop for the coming days.

e.g Day = 06:01:00 to 17:59:00
Night = 18:01:00 to 05:59:00

Jindon,
Thanks for the input/code.
But, i would like to have two different time frame.
Basically, a live dashboard based on this two timing using the same sheet of DASHBOARD under TURNS.
It will continue to loop for the coming days.

e.g Day = 06:01:00 to 17:59:00
Night = 18:01:00 to 05:59:00

OK, if you got a working code, forget mine and stick with PCI's.

Hi PCI and Jindon,
I really appreciate your inputs given.
Literally, It has kick-start my assignment.
Thank you so much.

However, i would like to request inputs on below task:-
A live dashboard based on this two timing using the same sheet of DASHBOARD under TURNS column.
It will continue to loop for the coming days.

* If i'm working in the day shift, i would be able to monitor the live outs based on the day shift time frame and vice-versa.

e.g Day = 06:01:00 to 17:59:00
Night = 18:01:00 to 05:59:00

Are you updating the same list for day & night in "DashBoard" sheet?

Indeed, you are right.

This should update "Day" shift when you run during the day between 06:01:00 to 17:59:00, otherwise "night" shift.

Please Login or Register  to view this content.

You guys are really fabulous and awesome!!!
If only i could give you extra reps, i definitely would have without any doubt.
You guys are indeed a life saver...just brilliant!!

Last final request, can we match the STAGE with DASHBOARD and RawData sheet? It means the DASHBOARD sheet under STAGE column will always be static with the current text.
Also, if no values, is it possible put a "0" into that column until a value been updated?
Currently, the others stages are left blank and without any value.

Dashboard Stage.PNG

Oh yea, i forgot one more thing..sorry about it.
Is it possible to reset the values to "0" on DASHBOARD sheet under TURNS when it hits at 6:00:00 and 18:00:00 respectively?

Please Login or Register  to view this content.

If you want to fully automate, add following code to "RawData" sheet code module.

Please Login or Register  to view this content.

All good on matching the stages.
What do you mean by fully automate?

Is it possible to reset the values to "0" on DASHBOARD sheet under TURNS when it hits at 6:00:00 and 18:00:00 respectively?
Also, if no values, is it possible put a "0" into that column until a value been updated?

Jindon,
I'm not getting the results needed.

DASHBOARD sheet still shows blank under TURNS when i refresh my connection/database every minute.
Any idea on what went wrong with my file?
Attached is the file.

FinalFilm2.xlsm

Are you talking about "Sub Dashboard()"?

Try delete following lines

Please Login or Register  to view this content.

I've already deleted those unrelated modules as per attachment.

Should i run all macro for every minute?

Originally Posted by Sharonjit

I've already deleted those unrelated modules as per attachment.

It is there in Module1.

As I have no access to the database, I have no idea if it updates properly or not.

The code I have posted should work, if the raw data updates properly.

As shown below.

Blank Cell.PNG

Yeah, raw database is being updated every minute and values are being transposed to column under TURNS.
But, sometimes the raw database will not update several stages every minute.
Please have a look on below display on database.

Raw Data.PNG

Yes, it is correct and working perfectly.
It's just that i want those stages which has no value to be "0" until a value been added.
I hope you understand what i meant.

OK, then just change one line.

Please Login or Register  to view this content.

Finally, all is good and working smoothly...pheww...
Thanks a bunch Jindon..
You've been very helpful and understanding.
Have a nice day!!

I would like to do some changes on the coding based on column and rows.

Please have a look on it.

Testing1.xlsm

Change to

Please Login or Register  to view this content.

Great!!
It is working perfectly.
Thanks again so much..:-)

Hi Jindon,
I'm getting an error here when i run the macro as shown below in a module.

Error.PNG

When i remove this code, this next error will be shown when i run the macro on Sub Set0.
Any idea on why?

ErrorSet0.PNG