One Userform button to fill in TWO separate tables

[Marc1919]

Hey guys, first post here but I have been a follower/reader for quite a while.

I currently have a userform that enters into whatever table you double clicked on (Userform opens on double click). It will populate that current table properly no issues. I want it to populate the same information into a 2nd table to collect data from all the individual tables in one. So in the end I need the Userform to still fill in the table double clicked on but also fill in the same information on another table.

I hope I explained this well enough to get the drift.

Here is the code I have for the one (1) table:


Private Sub buylong_Click()

'General Variables
Dim numRows As Long
numRows = Range(Selection.ListObject.Name).Rows.Count

Dim firstRow As Long
firstRow = Range(Selection.ListObject.Name).Row

Dim tableName As String
tableName = Selection.ListObject.Name

Set tbl = ActiveSheet.ListObjects(tableName)

Dim x As Long

'-----------------------------------------------------------------------------------------------------------------
Dim currentRow As Long
currentRow = firstRow - 1

For x = 1 To tbl.Range.Rows.Count

If WorksheetFunction.CountA(tbl.Range.Rows(x)) = 0 Then

'Add the values to the current RECAP table
Worksheets("Recap").Cells(currentRow, 2).Value = Now
Worksheets("Recap").Cells(currentRow, 5).Value = "LONG"
Worksheets("Recap").Cells(currentRow, 3).Value = Ticker.Value
Worksheets("Recap").Cells(currentRow, 4).Value = Qty.Value
Worksheets("Recap").Cells(currentRow, 6).Value = entryprice.Value
Worksheets("Recap").Cells(currentRow, 7).Value = exitprice.Value
'Change Price of trading fees here!!!
Worksheets("Recap").Cells(currentRow, 8).Value = 4.95 * 2

Application.ScreenUpdating = True
Unload Me

Exit Sub

End If

currentRow = currentRow + 1

Next x

Dim endRow As Long
endRow = firstRow + numRows


'Find last row of table and adds a new row + resizes the table
Cells(endRow, 1).EntireRow.Insert

With tbl.Range
tbl.Resize .Resize(.CurrentRegion.Rows.Count)
End With

'Add the values to the current RECAP table
Worksheets("Recap").Cells(endRow, 2).Value = Now
Worksheets("Recap").Cells(endRow, 5).Value = "LONG"
Worksheets("Recap").Cells(endRow, 3).Value = Ticker.Value
Worksheets("Recap").Cells(endRow, 4).Value = Qty.Value
Worksheets("Recap").Cells(endRow, 6).Value = entryprice.Value
Worksheets("Recap").Cells(endRow, 7).Value = exitprice.Value
'Change Price of trading fees here!!!
Worksheets("Recap").Cells(endRow, 8).Value = 4.95 * 2

Application.ScreenUpdating = True

Sort
Unload Me

End Sub

 

[dmt32]

Hi
welcome to forum

Untested but make a back-up of your workbook & see if this update to your code does what you want

Private Sub buylong_Click()
    Dim wsConsolidated      As Worksheet
    Dim RecordRow(1 To 2)   As ListRow
    Dim tbl(1 To 2)         As ListObject
    Dim i                   As Long

    On Error GoTo myerror
    'set object variable to consolidated worksheet (change name as required)
    Set wsConsolidated = ThisWorkbook.Worksheets("Sheet1")
   
    'if consolidated table active raise error
    If ActiveSheet.Name = wsConsolidated.Name Then _
    Err.Raise 17, , "Cannot perform requested operation on this table"
   
    Set tbl(1) = ActiveSheet.ListObjects(1)
    Set tbl(2) = wsConsolidated.ListObjects(1)
   
    'add new row to tables
    Set RecordRow(1) = tbl(1).ListRows.Add(AlwaysInsert:=True)
    Set RecordRow(2) = tbl(2).ListRows.Add(AlwaysInsert:=True)
   
    'post record to both tables
    For i = 1 To 2
        With RecordRow(i)
            .Range(2) = Now()
            .Range(3) = Me.Ticker.Value
            .Range(4) = Me.Qty.Value
            .Range(5) = "LONG"
            .Range(6) = Me.entryprice.Value
            .Range(7) = Me.exitprice.Value
            .Range(8) = 4.95 * 2
        End With
    Next i
   
    'inform user
    MsgBox tbl(1).Name & " AND " & tbl(2).Name & Chr(10) & _
                      "Updated", 64, "Tables Updated"
   
    Unload Me
  
'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Change the Consolidated Sheet Name shown in BOLD as required.

Hope Helpful

Dave

 

[Marc1919]

Hi
welcome to forum

Untested but make a back-up of your workbook & see if this update to your code does what you want

Private Sub buylong_Click()
    Dim wsConsolidated      As Worksheet
    Dim RecordRow(1 To 2)   As ListRow
    Dim tbl(1 To 2)         As ListObject
    Dim i                   As Long

    On Error GoTo myerror
    'set object variable to consolidated worksheet (change name as required)
    Set wsConsolidated = ThisWorkbook.Worksheets("Sheet1")
  
    'if consolidated table active raise error
    If ActiveSheet.Name = wsConsolidated.Name Then _
    Err.Raise 17, , "Cannot perform requested operation on this table"
  
    Set tbl(1) = ActiveSheet.ListObjects(1)
    Set tbl(2) = wsConsolidated.ListObjects(1)
  
    'add new row to tables
    Set RecordRow(1) = tbl(1).ListRows.Add(AlwaysInsert:=True)
    Set RecordRow(2) = tbl(2).ListRows.Add(AlwaysInsert:=True)
  
    'post record to both tables
    For i = 1 To 2
        With RecordRow(i)
            .Range(2) = Now()
            .Range(3) = Me.Ticker.Value
            .Range(4) = Me.Qty.Value
            .Range(5) = "LONG"
            .Range(6) = Me.entryprice.Value
            .Range(7) = Me.exitprice.Value
            .Range(8) = 4.95 * 2
        End With
    Next i
  
    'inform user
    MsgBox tbl(1).Name & " AND " & tbl(2).Name & Chr(10) & _
                      "Updated", 64, "Tables Updated"
  
    Unload Me
 
'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Change the Consolidated Sheet Name shown in BOLD as required.

Hope Helpful

Dave

Hi Dave!

Thanks for the response! I tried to copy and paste the code and changed the sheet name to "Recap" which is the name of my sheet.

It gave me an error code and highlighted this section : "On Error GoTo myerror" and a pop up box that says "Compile error: Label not defined"

Any insight you could provide for this?

Appreciate your time

 

[dmt32]

Hi,
You need to copy the WHOLE of the code I posted without changes (apart from sheet name). If you look at the end you will see the Label

'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[Marc1919]

Hi Dave,

My apologies I did not see those last lines of code originally. The message box I am now getting says "Cannot perform requested operation on this table".

A couple things to note: I have a table for each day of the years (365 of them) and then the master table that I want all table data to be collected into simultaneously. Each of the 365 tables are labeled as follows - Jana, Janb, Janc, Jand, etc... and Feba, Febb, Febc, Febd, etc... does this change anything on your end?

The error code comes up as soon as I hit the "Long" button and doesnt enter data into either tables.

The master table is called "Tradingmetricsystem"

Once again, truly appreciate all of the time you have taken to assist me!

 

[Marc1919]

Here is a screen shot

 

[dmt32]

Hi Dave,

My apologies I did not see those last lines of code originally. The message box I am now getting says "Cannot perform requested operation on this table".

You get that Error message if the ActiveSheet is the Consolidated Sheet - The sheet with table you are adding to needs to be the activesheet.

Code is written to work on an activesheet (name not important) with a table & should add new record to both this & your consolidated table on specified sheet.

Dave

 

[Marc1919]

Hi Dave,

I copied the formula word for word and simply changed the "Sheet 1" to "Trades" as the Consolidated sheet.

For some reason once when I enter the trade in, the entire sheet gets butchered. Rows move around and charts erase etc... see attached photos.

Any idea what has happened?

 

[dmt32]

I did state solution untested & is based on each worksheet containing just 1 table, - Code should enter new record in the table in activesheet + table in consolidated sheet.

If structure of your worksheets are more complex than this then best to revert to an original copy & share via file sharing site, copy of your workbook otherwise difficult to guess what is going on.

Dave

 

[Marc1919]

Hello Dave,

I understand i think its because i have more going on in the workbook. I would love to share the excel file with you. Do you have a recommended 3rd party file sharing site? Also where would you like me to post the link... here?