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

 

[truitt20]

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

 

[truitt20]

Sorry

errors out on this:

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

 

[firefytr]

The only things on that line that can error out with that error type is either lRow or WF. Try changing out WF with Application.WorksheetFunction, even if you need to put it in a With/End With statement.

Regards,
Zack Barresse

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

 

[truitt20]

I am obviously inept when it comes to VBA - could you explain?

thanks

 

[firefytr]

Where you see the..

WF

.. delete it and replace the text with ..

Application.WorksheetFunction

Make sense? Or, the alternative is ..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
    If Application.Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    With Application.WorksheetFunction
        lRow = Sheet2.Range("A:A").Find("*", after:=Sheet2.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
        ws2.Cells(lRow, 1) = Now
        ws2.Cells(lRow, 2) = .SumIf(Me.Range("AF:AF"), "*US*", Me.Range("D:D")) / Sheet3.Cells("A16").Value
    End With
End Sub

Regards,
Zack Barresse

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

 

[truitt20]

with:

.Cells(lRow, 2) = (Application.WorksheetFunction.SumIf(Sheet1.Range("AF:AF"), "*US*", Sheet1.Range("D"))) / (Sheet3.Cells("A16").Value)

still errors out on that line with Type Mismatch error

 

[firefytr]

Ah, simple...

Sheet3.Cells("A16").Value

.. should be ..

Sheet3.Range("A16").Value

Regards,
Zack Barresse

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

 

[truitt20]

Same error with line:

.Cells(lRow, 2) = (Application.WorksheetFunction.SumIf(Sheet1.Range("AF:AF"), "*US*", Sheet1.Range("D"))) / (Sheet3.Range("A16").Value)

 

[firefytr]

works for me ..

.Cells(lRow, 2) = Application.WorksheetFunction.SumIf(Sheets("Sheet1").Range("AF:AF"), "*US*", Sheets("Sheet1").Range("D:D")) / Sheets("Sheet3").Range("A16").Value

Regards,
Zack Barresse

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

 

[truitt20]

When I populate A16 with just a number the code runs perfectly, but when there is a formula in A16 it is causing the error.

 

[firefytr]

In your Immediate window, type this and hit enter..

Print sheets("Sheet3").range("A16").value

Print typename(sheets("Sheet3").range("A16").value)

What do they return?

Regards,
Zack Barresse

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

 

[truitt20]

Print sheets("Sheet3").range("A16").value
QUANT
Print typename(sheets("Sheet3").range("A16").value)
String

 

[truitt20]

Got it to work. My mistake. One additional question.


In sheet2 i want not only the % in column B, but the actual subtotal in column C. however i get an error with :

.Cells(lRow, 2) = Application.WorksheetFunction.SumIf(Sheets("Sheet1").Range("AF:AF"), "*US*", Sheets("Sheet1").Range("D")) / Sheets("Sheet3").Range("B16").Value

 

[truitt20]

Correction - Get the error with this:

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) = Application.WorksheetFunction.SumIf(Sheets("Sheet1").Range("AF:AF"), "*US*", Sheets("Sheet1").Range("D")) / Sheets("Sheet3").Range("B16").Value
.Cells(lRow, 3) = Application.WorksheetFunction.SumIf(Sheets("Sheet1").Range("AF:AF"), "*US*", Sheets("Sheet1").Range("D")
End With
End If
Set WF = Nothing
End Sub

 

[firefytr]

You're missing a paren at the end of the last function for .Cells(lRow, 3).

Regards,
Zack Barresse

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