VBA Code

Hi,

Attached i have a file with a pivot that changes each day (Screen). In the tab "RC Check" i want to start a daily build up of the two amounts in E121+G121. So i would like a sub that can read those two headings and place them in the next cell down in A+B. I would also like to be able to place the formula in the C+D that would calculate the difference between today and yesterday, and also drop now() into the adjacent cell E

The aim is to see any differences betwee today and yesterday for those sub groups.

Once challenge however is that in the pivot table,  E121+G121 are not static so the figures would need to be read by the Sub Total of Risk and Control, column 5+7.

Thanks!
Seamus
Example.zip
Seamus2626Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SiddharthRoutConnect With a Mentor Commented:
Try this.

Private Sub CommandButton1_Click()
    Dim i As Long, lastRowWs1 As Long, nRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchString As String
    Dim aCell As Range
    
    Set ws1 = Sheets("RC Check")
    Set ws2 = Sheets("Screen")
    
    SearchString = "Risk and Control Total"
    
    lastRowWs1 = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set aCell = ws2.Cells.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        nRow = aCell.Row
        ws1.Range("A" & lastRowWs1).Value = ws2.Range("E" & nRow).Value
        ws1.Range("B" & lastRowWs1).Value = ws2.Range("G" & nRow).Value
        ws1.Range("E" & lastRowWs1).Value = Now
        If lastRowWs1 <> 2 Then
            ws1.Range("C" & lastRowWs1).FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"
            ws1.Range("D" & lastRowWs1).FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"
        End If
    End If
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
What formulas do you want in Cell C and D. I will adjust that in the code. Can you give me an example?

For the rest see this sample File. The values will be updated every time you click on the update button.

Sid

Code Used

Private Sub CommandButton1_Click()
    Dim i As Long, lastRowWs1 As Long, nRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchString As String
    Dim aCell As Range
    
    Set ws1 = Sheets("RC Check")
    Set ws2 = Sheets("Screen")
    
    SearchString = "Risk and Control Total"
    
    lastRowWs1 = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set aCell = ws2.Cells.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        nRow = aCell.Row
        ws1.Range("A" & lastRowWs1).Value = ws2.Range("E" & nRow).Value
        ws1.Range("B" & lastRowWs1).Value = ws2.Range("G" & nRow).Value
        ws1.Range("E" & lastRowWs1).Value = Now
    End If
End Sub

Open in new window

Example.xls
0
 
Seamus2626Author Commented:
thanks Sid!

I just need

C2= A2-A1
D2= B2-B1

C3=A3-A2
D3=B3-B2

etc

Thanks!
0
 
Seamus2626Author Commented:
Absolutely perfect Sid!!

Thank you so much

Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.