Grabbing dynamic data from a worksheet

[truitt20]

I want to grab dynamic data from a worksheet1 and place it in worksheet2 with the value and a timestamp in the cell next to it. The data in worksheet1 gets refreshed every minute, so I would like the data that is being populated in wrksheet2 to create a list and not get over written.

worksheet1 - 12:36PM data would equal 1234
and at 12:37PM data in wrksheet1 would equal 1578

I would like wrksheet2 to begin to look like:

12:36PM 1234
12:37PM 1578
12:38PM 1456
12:39PM 1545

and so on

Anybody have ideas to help me out?

thanks

 

[SkipVought]

Hi,

right click sheet 1 and select View Code

paste in this code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
    If Target.Address = [A1].Address Then
        With Sheet2
            lRow = .[A1].CurrentRegion.Rows.Count + 1
            .Cells(lRow, 1) = Sheet1.[A1]
            .Cells(lRow, 2) = Sheet1.[B1]
        End With
    End If
End Sub

when the valus in A1 on Sheet1 changes, the Worksheet_Change event will fire this procedure.

Skip,

_[red][/red]

 

[truitt20]

Thanks Skip. That works on when the data is always in A1, but this is dynamic data and the subtotal formula is always changing cells (formula= =SUBTOTAL(9,(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1))))

And do you know how to time stamp the sheet2 value with a time stamp so I know when the value populated the sheet?

thanks

 

[SkipVought]

You have not told me where the data is that you want to transfer to sheet2.

Is this just a SUM of values in column D?

Do you want only the TIME or the Date & Time?

Skip,

_[red][/red]

 

[truitt20]

skip,

The data in wrksheet1 is the sum of column D, but the sumtotal could be in cell D2000 or D2023 depending on if rows are being added or taken away. Rows are continuously being added and subtracted from the wrksheet1 every minute.

Date and Time would be helpful.

thanks

jt

 

[SkipVought]

Thats not ver smart. Why put a total in a "floating" cell that you have to HUNT for? I'm choosing NOT to hunt for it...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long, WF As WorksheetFunction
    Set WF = Application.WorksheetFunction
    If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
        With Sheet2
            lRow = .[A1].CurrentRegion.Rows.Count + 1
            .Cells(lRow, 1) = Now
            .Cells(lRow, 2) = WF.Subtotal(9, Sheet1.Range("D:D"))
        End With
    End If
    Set WF = Nothing
End Sub

Skip,

_[red][/red]

 

[truitt20]

Thanks SKIP!!! Works Great!!

 

[truitt20]

Hey Skip,

another question for you. this code works perfect, but how do I only subtotal column "d:d" based on criteria in column "AF". For example I want to subtotal "D" but only for the rows where column "AF" has <>"*JAPAN*"?

thanks

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long, WF As WorksheetFunction
    Set WF = Application.WorksheetFunction
    If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
        With Sheet2
            lRow = .[A1].CurrentRegion.Rows.Count + 1
            .Cells(lRow, 1) = Now
           [b] .Cells(lRow, 2) = WF.SumIf(Sheet1.Range("AF:AF"), "Japan", Sheet1.Range("D:D"))[/b]
        End With
    End If
    Set WF = Nothing
End Sub

Skip,

_[red][/red]

 

[firefytr]

Are you double posting now? ..

http://www.tek-tips.com/viewthread.cfm?qid=1268746

If there is no autofilter in place, you can use Skip's code (although I disagree with the CurrentRegion portion).

As a side note, there is absolutely no need for the SUBTOTAL function if you do not have autofilter applied for (which is what it's intended for).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[truitt20]

Skip,

works great again. Another question for you...

I want to divide the following by a cell in another sheet:

.Cells(lRow, 2) = WF.SumIf(Sheet1.Range("AF:AF"), "Japan", Sheet1.Range("D"))

how should it go?

".Cells(lRow, 2) = (WF.SumIf(Sheet1.Range("AF:AF"), "Japan", Sheet1.Range("D")))/(Sheet3.Cells(A16))?

this doesnt work.

thanks

jt

 

[firefytr]

I sincerely hope this is the ONLY thead you're sticking to now regarding this topic ....

.Cells(lRow, 2) = (WF.SumIf(Sheet1.Range("AF:AF"), "Japan", Sheet1.Range("D:D"))) / (Sheet3.Cells("A16").Value)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[truitt20]

Yes sir.

That is actually causing a "Type mismatch" error, b/c cell A16 is actually a formula ( =SUBTOTAL(9,(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1))))

Any way around this?

thanks

jt

 

[firefytr]

Well, that's not the reason you're getting a type mismatch, not because it's a formula, it's for some other reason. Just prior to the above line, try adding these lines, see what you get in your Immediate window...

Debug.print WF.SumIf(Sheet1.Range("AF:AF"), "Japan", Sheet1.Range("D:D"))
Debug.print Sheet3.Cells("A16").Value

Do you get the expected results?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[truitt20]

That still produced the error. This is the original code and it is running fine:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, WF As WorksheetFunction
Set WF = Application.WorksheetFunction
If Not Application.Intersect(Target, Range("D")) Is Nothing Then
With Sheet2
lRow = .[A1].CurrentRegion.Rows.Count + 1
.Cells(lRow, 1) = Now
.Cells(lRow, 2) = WF.SumIf(Sheet1.Range("AF:AF"), "*US*", Sheet1.Range("D"))
End With
End If
Set WF = Nothing
End Sub

Not sure why it isnt running by adding the additional equation after the sumif function.

any help is much appreciated.

thanks

 

[firefytr]

What line errored and what was the error message?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[truitt20]

Still received the Type mismatch error.

Immediate Window:
0
0
-255471.18309
-254939.392490001
0 -231982.74959

 

[SkipVought]

How about

(Sheet3.Range("A16").Value)
or
(Sheet3.Cells(16, "A").Value)
or
(Sheet3.Cells(16, 1).Value)

Skip,

_[red][/red]

 

[truitt20]

Still errors out. Grrrrr

Run_Time error "13":

Type Mismatch

 

[firefytr]

On what line???

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci