Creating a Summary Page with Data from Multiple Worksheets Within the Same Workbook

Aerialshot

New Member
Joined
Jan 12, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I am trying to build a Stock Options Trading Journal. To this end, I have a Summary page at Sheet1 position and each trade with all its data on each consecutive worksheet page, named with a code representing the trade identifier.

Is there a way to automatically draw data from each specific recurring cell on multiple worksheets and import that data into the summary page? I need it to work and adjust as I add new worksheets to the file. At present, my only option, based on my limited knowledge, is to go to each cell and enter ='001-LLY-P'!G3, etc, etc. Automation must be available for something like this. Your assistance is greatly appreciated.
 

Attachments

  • Screenshot 2025-01-11 234131.png
    Screenshot 2025-01-11 234131.png
    132.5 KB · Views: 17

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the Summary sheet and at least one of the trade sheets (not pictures). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. This macro will run automatically after you enter a Ticker in C1 and press the ENTER key in any of the trade sheets. It is important that the ticker in C1 is the very last piece of data entered after all the other data in the trade sheet is entered.
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name = "Summary" Or ActiveSheet.Name Like "MASTER*" Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim desWS As Worksheet, lRow As Long
    Set desWS = Sheets("Summary")
    With desWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lRow = 1 Then
            .Range("B2") = WorksheetFunction.Min(ActiveSheet.Range("A8"), ActiveSheet.Range("A23"))
            .Range("C2") = ActiveSheet.Range("C2")
            .Range("D2") = ActiveSheet.Range("G3")
            .Range("E2") = .Range("D2")
        Else
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            .Range("B" & lRow) = WorksheetFunction.Min(ActiveSheet.Range("A8"), ActiveSheet.Range("A23"))
            .Range("C" & lRow) = ActiveSheet.Range("C2")o
            .Range("D" & lRow) = Format(ActiveSheet.Range("G3"), "0")
            .Range("E" & lRow) = Format(.Range("D" & lRow) + .Range("E" & lRow - 1), "0")
        End If
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
There seems to be a typo. Please delete the “o” at the end of this line of code
Code:
.Range("C" & lRow) =  ActiveSheet.Range("C2") o
 
Upvote 0

Forum statistics

Threads
1,225,608
Messages
6,185,963
Members
453,333
Latest member
BioCoder84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top