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,
DropBox is one sharing site used by many - Dropbox Basic (free account) - Dropbox
but there are others.

You will need to remove any sensitive / personal data & provide a link to file

Dave

 

[Marc1919]

Hi Dave,

Please try this link.

Dropbox

www.dropbox.com

If there are any issues let me know I have not used DropBox often.

The sheet "Recap" has the table for each day of the year. The sheet "Trade Tape" is where I want to copy all inputted data into.

Thanks!

 

[dmt32]

Hi,
Sorry taken awhile to respond but having downloaded your workbook confirmed my thoughts on complexity of your workbook – where I thought you just had one table on the worksheet it turns out you have 100s and just to add to the complexity, you have numerous grouped ranges which explains why my simple code suggestion will not work.

The VBA project contains several almost identical userforms all with repeating code and all performing a very similar tasks but you only need one userform.

Not been able to spend too much time looking at your project but I suspect a better approach to achieve what you want may be to create a flat file database (single table) that your userform post all the data to and from this, you could then could create pivot tables.

A pivot table provides an interactive view of your data and with very little effort, you can look at the same data from many different perspectives. You can group data into categories, break down data by date (year, month), filter data to include or exclude categories, and even build charts. All this can be done without formulas or macros.

If you have not worked with pivot tables before there is plenty of reading material available to search for.

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

Dave

 

[Marc1919]

Hey Dave,

thanks for that I have implemented the pivot table and switched it to only use one master table now. It works perfectly!

thank you for taking the time to help me out!

 

[dmt32]

Hi,
glad suggestion helped resolve your issue & appreciate feedback

Dave