Looking to copy select cells in table using dropdown list to paste to new table in another worksheet

NoSparks · #1 12-19-2017, 01:20 PM

Quote:

My research so far has led me to believe that I would need to put this code in each of the 24 worksheets.

Sounds like you could use the Workbook_SheetChange event in the ThisWorkbook module.
Attaching a sample workbook to work with would be advantages.

Without knowing how your 24 sheets are associated with the correct table on the main worksheet can't elaborate much,
would think you could use something along the lines of this...

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'don't apply to Main sheet
    If Sh.Name = "Main" Then Exit Sub
    'limit to single cell in column K
    If Target.Count > 1 Or Target.Column <> 11 Then Exit Sub

    Dim ray As Variant
    Dim oLo As ListObject, nxtRow As ListRow

If UCase(Target.Value) = "NO" Then
    ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
    With Sheets("Main")
        Set oLo = .ListObjects("?")  'don't know how you associate table to sheet
        Application.EnableEvents = False
        Set nxtRow = oLo.ListRows.Add
        nxtRow.Range.Cells(1, 2).Resize(, 3).Value = ray
        Application.EnableEvents = True
    End With
End If

End Sub

Here's a couple of links to sites dealing with tables
https://www.thespreadsheetguru.com/b...t-excel-tables
http://www.jkp-ads.com/Articles/Exce...lComments=True

CaptainRetired · #2 12-19-2017, 02:17 PM

I looked at your code and it is basically what I have been trying to do, but you bring up a very good point. I cannot find anywhere how to associate those 24 tables that are on the MAIN GOE RECONCILIATION worksheet. I think this is what my problem is. For example, one table in this worksheet is labeled "State" in the Table Name in Design tab and the worksheet name on the tab is MAIN GOE RECONCILIATION. The Table Name upon which I am trying to pull the data from (one of 24) using the "NO" dropdown is titled "StateProgramTravel" in design field and its tab name at bottom is 21000 State Program Travel. I believe this is the issue I am fighting! Any recommendations or guidance? Do I need another table in my workbook that brings these two together. I only want the tables in the MAIN GOE RECONCILIATION worksheet to include the selected cells identified in main post.

Thanks for any help you can provide.

CaptainRetired · #3 12-19-2017, 02:32 PM

I can send you the complete workbook if that would help. Not sure how to do that though. I have cleared all sensitive information from a copy saved in xlsm.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to paste the data from one table into the cells of another table, without overwriting anything	CClio333	Word Tables	1	08-12-2014 05:17 PM
Is it possible to copy non-contiguous rows of a Table and paste them as a separate Table in Word?	Joey Cheung	Word Tables	1	08-12-2014 05:15 PM
Can you copy & paste cells across worksheets and preserve reference to worksheet?	New Daddy	Excel	2	11-27-2013 07:19 AM
Copy and paste table	Patrickjm	Word	1	03-19-2013 03:30 PM
Populate dropdown list with data from Access table	spw4000	Office	0	02-24-2012 05:22 AM