Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-19-2017, 11:40 AM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default Looking to copy select cells in table using dropdown list to paste to new table in another worksheet

Good afternoon - Am just learning VBA for excel and am stumped on creating macro in excel 2016. I have developed a workbook that contains 24 different worksheets. For each of these worksheets, in column "K", I have a drop down list of "YES" and "NO". When the user selects "NO" for each row in the worksheets, I want it to copy the cells " B, D and J" for that row and paste the information into another table located in another worksheet, location "B, C and D" on first available row.



The 24 worksheets are named differently than the 24 tables located on the main worksheet. I have built the main worksheet "MONTHLY GOE RECONCILIATION" with 24 separate tables. I have several other macros that work on the 24 separate worksheets, so not sure how to write the code to use which name (tab name or table name listed under "design". The main worksheet named above has the 24 tables, each named with a different table name under "design" and an assigned tab name. (Hope that makes sense!).

The purpose of this workbook is to manage the monthly General Operating Expenses and to only post on the main worksheet (MONTHLY GOE RECONCILIATION). Those items listed as "NO" from the 24 separate worksheets as "NOT RECONCILED".

If anyone can help me out, that would be great! I can post the various names for the worksheets if needed. My research so far has led me to believe that I would need to put this code in each of the 24 worksheets.

My workbook has sensitive data in it, but I can clear it out if you need me to email you the workbook for review.

Thanks
Reply With Quote
  #2  
Old 12-19-2017, 01:20 PM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #3  
Old 12-19-2017, 02:17 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

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.

Last edited by CaptainRetired; 12-19-2017 at 02:19 PM. Reason: additional clarification
Reply With Quote
  #4  
Old 12-19-2017, 02:32 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 12-19-2017, 03:17 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

Ok had to upload it as a Zip file. Hope it has everything you need in it. Let me know if you got it okay and it has all that you need to review.

Thanks again
Attached Files
File Type: zip (1) DEMO GOE Tracker Workbook 12-19-17.zip (1.55 MB, 9 views)
Reply With Quote
  #6  
Old 12-19-2017, 03:20 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

If you look at Column K (11) in each worksheet, when the "NO" is selected, it turns red and this is when I need the data to copy to the MAIN GOE RECONCILIATION worksheet under the table that goes with that particular worksheet.
Have tried my best to build a fully automated GOE tracking workbook to simplify entry and eliminate the chance of entry error or omission of data. I have to travel for work tonite but will be back online in morning. Thanks.

Last edited by CaptainRetired; 12-19-2017 at 03:47 PM. Reason: additional info
Reply With Quote
  #7  
Old 12-19-2017, 04:25 PM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Hello Captain

I've just opened your workbook and it's going to take a bit of a while to figure things out but I'll work on it.
It appears the tab names and the table names are close.
Reply With Quote
  #8  
Old 12-19-2017, 11:11 PM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Okay, maybe this will work for you...

rename current table "State" to "_21000_State_Program_Travel"
rename current table "Sheet23" to "_26062_Supplies"
now the name of the associated tables can be derived from the sheet names.

The formulas within the tables refer to things by referencing sheet cell locations as opposed to table locations so I left the tables as-is with all those empty hidden rows.
Not adding new rows to the tables, instead start at the header row and step down column E to find the first empty cell.
You might need to change that to a different column that will be populated for every record.

Put this in the ThisWorkbook module and give it a shot
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'limit to sheets whose name starts with 5 digits
 If Not IsNumeric(Left(Sh.Name, 5)) 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
    Dim destTable As String
    Dim HderRow As Long
    Dim i As Integer
    
If UCase(Target.Value) = "NO" Then
    destTable = "_" & Replace(Sh.Name, " ", "_")
    ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
End If

Application.ScreenUpdating = False

With Sheets("MONTHLY GOE RECONCILIATION")
    .Select
    .ListObjects(destTable).HeaderRowRange.Select
    HderRow = Selection.Row
    For i = 1 To 99
        If IsEmpty(.Cells(HderRow + i, 5)) Then Exit For
    Next i
    Application.EnableEvents = False
    .Cells(HderRow + i, 2).Resize(, 3).Value = ray
    Application.EnableEvents = True
End With

Sh.Select

Application.ScreenUpdating = True

End Sub
Reply With Quote
  #9  
Old 12-20-2017, 08:37 AM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

Good morning - Thanks for working on this! The two tables that you had me change were once the same as you said, but I had tried several things to get up and running and forgot to change them back to what I had originally named them, like the other tables on that worksheet.

I have a little issue tho as it seems to work fine when "NO" is put in, but it gives an error code when I remove the "NO". Once an item has reconciled, the user will then go in and change that dropdown from "NO" to "YES" and the data would then be removed from the Table on the "MONTHLY GOE RECONCILIATION" worksheet.

The error shows in your code on the highlighted line .ListObjects(destTable).HeaderRowRange.Select

Not sure how to set that code up to remove the copied data when "YES" or blank cell selected.

Also, when that data line, such as when I select "NO" on one line of the 21000 State Program Travel worksheet and the data is copied to the "MAIN GOE RECONCILIATION" worksheet on the table "_21000_State_Program_Travel, the totals row down below on column D does not reflect the value that was copied to column D row 4 even though there is a formula in D102. Why wont that pick up the copied data?

Thanks again for your help.

Last edited by CaptainRetired; 12-20-2017 at 09:28 AM. Reason: Additional Information
Reply With Quote
  #10  
Old 12-20-2017, 11:15 AM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
The error shows in your code on the highlighted line .ListObjects(destTable).HeaderRowRange.Select
Opps... eliminate the error by adding the Else Exit Sub bits here
Code:
If UCase(Target.Value) = "NO" Then
    destTable = "_" & Replace(Sh.Name, " ", "_")
    ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
Else
    Exit Sub
End If
Quote:
Not sure how to set that code up to remove the copied data when "YES" or blank cell selected.
The code for removal of the data from table will need to be in the above Else ahead of Exit Sub.
What is there to uniquely, positively, absolutely and unmistakenly identify the line to be removed?
(A record ID number would be a great thing to have)

Quote:
Why wont that pick up the copied data?
It was being copied as text.

The revised code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'limit to sheets whose name starts with 5 digits
 If Not IsNumeric(Left(Sh.Name, 5)) 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
    Dim destTable As String
    Dim HderRow As Long
    Dim writeRow As Long
    Dim i As Integer
    
If UCase(Target.Value) = "NO" Then
    destTable = "_" & Replace(Sh.Name, " ", "_")
    ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
Else
    'removal would be done here
    Exit Sub
End If

Application.ScreenUpdating = False

With Sheets("MONTHLY GOE RECONCILIATION")
    .Select
    .ListObjects(destTable).HeaderRowRange.Select
    HderRow = Selection.Row
    For i = 1 To 99
        If IsEmpty(.Cells(HderRow + i, 5)) Then Exit For
    Next i
    writeRow = HderRow + i
    Application.EnableEvents = False
    .Cells(writeRow, 2) = ray(0)
    .Cells(writeRow, 3) = ray(1)
    .Cells(writeRow, 4) = ray(2) * 1
    Application.EnableEvents = True
End With

Sh.Select

Application.ScreenUpdating = True

End Sub
Hope that rectifies some of the issues.
Reply With Quote
  #11  
Old 12-20-2017, 02:54 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

For worksheets 21000, 21010, 21020, 21030, 21036, 21070, and 21090 we can use the Travel Order Number column (C) as unique identifier to remove data from the MAIN GOE RECONCILIATION worksheet table. For the other worksheets, data is automatically populated to each worksheet thru a macro that runs after data entered on the Procurement Log worksheet. Maybe we could use the PR NO Column for those other worksheets, as it is entered on the Procurement Log, update button hit and data goes to the appropriate worksheet. Just a thought. Any suggestion or help welcome.

Code runs for "NO" and now subtotal shows at bottom of table. We are so close, just need to remove data when Yes or blank entered.

I added the new updated workbook.
Attached Files
File Type: zip (1) DEMO GOE Tracker Workbook 12-20-17.zip (1.56 MB, 9 views)
Reply With Quote
  #12  
Old 12-20-2017, 11:33 PM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Well Captain, I believe this addresses the things you've mentioned.

Not sure about being so close.
Don't know if you've worked with the sheets automatically populated via the Procurement Log worksheet yet.
Suspect there's another movement of the goal posts coming.
Attached Files
File Type: zip (1) DEMO GOE Tracker Workbook 12-20-17_V2.zip (1.48 MB, 16 views)
Reply With Quote
  #13  
Old 12-21-2017, 06:51 AM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

Good morning - In response to your question, once I enter information into the Procurement Log and hit the UPDATE button, it automatically populates the data into the respective worksheet (either 23370 Telephone Services or 26062 Supplies). These are the only two classes that procurements are made to.

I checked and it appears that all required data cells are populated to the MAIN GOE RECONCILIATION from the 21000 State Program Travel worksheet when "NO" is selected. It also looks like when I "clear" the "NO" out the data is removed from the MAIN GOE RECONCILIATION worksheet. Just what I needed!

However, when I tested other worksheets, such as 23370 and 26062, when "NO" is selected, only the description column is showing up on the MAIN GOE RECONCILIATION worksheet. No date or dollar amount. Any suggestions. When the "NO" column on the worksheets is removed, the description is removed, which is good. Any further tweaking needed on my end?

Thanks again -

Last edited by CaptainRetired; 12-21-2017 at 06:52 AM. Reason: Spelling
Reply With Quote
  #14  
Old 12-21-2017, 08:21 AM
NoSparks NoSparks is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 7 64bit Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
However, when I tested other worksheets, such as 23370 and 26062, when "NO" is selected, only the description column is showing up on the MAIN GOE RECONCILIATION worksheet. No date or dollar amount. Any suggestions. When the "NO" column on the worksheets is removed, the description is removed, which is good. Any further tweaking needed on my end?
Check the columns being copied with the request of your original post.

I have no idea how those sheets are being populated but suspect it is by sheet reference rather than table reference.
The intermingle of sheet addresses with tables makes things difficult.
Reply With Quote
  #15  
Old 12-21-2017, 12:34 PM
CaptainRetired CaptainRetired is offline Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Windows 10 Looking to copy select cells in table using dropdown list to paste to new table in another worksheet Office 2016
Novice
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet
 
Join Date: Dec 2017
Posts: 11
CaptainRetired is on a distinguished road
Default

Afternoon - I know you have been more than patient with me on this project. I have been studying your code and cannot figure out why the sheets (only 21000 seems to work) will not copy data over to the MAIN worksheet's relevant tables. I have tried samples on various pages and saved it in this attachment so you can see. I know it is close to the holidays, but you have been SO helpful as I have nowhere else to turn. I have bought so many books but cannot seem to find the answers that you have written in code. Is there anyway you can look at this again over the next week or so (I need to figure out how to have this ready by January 5th for agency presentation) and help me out? If not, I completely understand as you have given so much time to me.

Thanks,
Larry (Yes, I am retired Captain, served 24 years).

Last edited by CaptainRetired; 12-21-2017 at 12:36 PM. Reason: Spelling
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet 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
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft