Results 1 to 10 of 10

Thread: Use table column headers for cell reference & search criteria?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use table column headers for cell reference & search criteria?

    I have a spreadsheet with 15 worksheets containing data for 21 cities. Some cities have multiple entries on their worksheet as they are separated by either north, south, etc... or uptown, downtown, etc...

    New York - Bronx
    New York - Queens
    New York - Brooklyn
    New York - Staten Island

    as an example as they all reside on the New York worksheet. All 15 worksheets will have 100% identical table column headers.

    City/Network Group Position Foo Stuff
    New York - Bronx 1 1 xyz abc
    New York - Bronx 1 2 def pqr
    New York - Bronx 1 3 asdf asdf
    New York - Bronx 1 4 wer vd asdf
    New York - Bronx 2 1 3254 ghjk
    New York - Bronx 2 2 qwre tyu
    New York - Bronx 2 3 asdf 789
    New York - Queens 1 1 mno srt
    New York - Queens 1 2 4536asf a43
    New York - Queens 1 3 asdf v 3425
    New York - Queens 1 4 2345 2436

    That could be an example of some data. Note that both the group and position values are identical, thus I will need to filter based on the column City/Network before I can look at the Group and Position and other column headers for data to extract.

    I am not looking to paste any data into this workbook, just pull data out of the workbook to populate another workbook. The row values will change and are unique for each worksheet as would be expected for a network of city data. The position and group columns will have many duplicate data values as all networks have a minimum number of groups and each group has a minimum number of positions.

    Looking for New York group 1 if not filtered further can result in 20 data sets (hope I am using that term here correctly) instead of the specific data set desired of New York - Queens group 1. That output based on the table above should have 4 rows worth of data for its data set. That is the range of cells I need to copy data from. Sadly I cannot just .value=.value the entire data set as I only need sections of the data. Once I am able to user the table column headers as part of the .Cells([searched_for_row_value],[table_column_header]).value it will be easy enough to copy/paste (.value=.value) between the 2 workbooks, I hope.

    I have some copy/paste code that i have used in the past, but it is all hard codded to the .Cells(12, 2) as an example.

    Code:
    ' ================================================================          ' Code to copy NETWORK from Overview sheet to new group Worksheet.
              ' ================================================================
    
    
            j = 2       ' Reset j back to 2.
                        ' Sets starting point for copy/paste of data to correct Cell.
                        Set rFoundCell = Worksheets("overview").Range("A1")
    
    
                   ' Starts for loop to search for 'start' vaule on the Overview Worksheet in the NETWORK column.
                   For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("a1:a200"), start)
                   ' Finds the 'start' value on the Overview Worksheet in the NETWORK column.
                   Set rFoundCell = Worksheets("overview").Columns(1).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
    
    
                         ' Start of the copy/paste data from Overview to new Group # Worksheet in a transpose form.
                         With rFoundCell
                             ' The offset will move the source from column A to column H.
                             Worksheets("overview").Range(rFoundCell.Address).Offset(, 7).Copy
                             Worksheets("group " & start).Activate
                             '                             .Cells(12, j) moves down to Row 12. - 06-18-2018 procedure
                             '                             18 July, 2019 procedure now row 15 - .Cells(15, j)
                             Worksheets("group " & start).Cells(15, j).PasteSpecial xlPasteValues
                             j = j + 1
         
                         End With
    
    
                   Next lCount ' End loop for copy NETWORK.
    Notice the comment 18 July, 2019 had to hard code from row 12 down to row 15. That is they type of editing I would like to avoid (I know rows and columns are not the same for table headers) for this next project as new columns may be added down the road.

    Thank you in advance.

    Excel 2010 & 2016

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,213
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    Why not use AutoFilter or advancedFilter to filter the data & then copy visible cells?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    hundreds of cell values that need to be both transposed and value only copy/paste across 30+ different worksheets in the new workbook.

    Takes way to much time to do by hand.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,213
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    You can use both types of filter within VBA.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    That is good to know. As long as I turn off the updates
    Code:
    ' ============================================          ' Get current state of various Excel settings.
              ' ============================================
    
    
              screenUpdateState = Application.ScreenUpdating
              statusBarState = Application.DisplayStatusBar
              calcState = Application.Calculation
              eventsState = Application.EnableEvents
              displayPageBreakState = ActiveSheet.DisplayPageBreaks   ' This is sheet-level only.
    
    
              ' ==================================================
              ' Turn off Excel functionality to speed up the code.
              ' ==================================================
    
    
              Application.ScreenUpdating = False
              Application.DisplayStatusBar = False
              Application.Calculation = xlCalculationManual
              Application.EnableEvents = False
              ActiveSheet.DisplayPageBreaks = False   ' This is sheet-level only.
    
    'stuff here
    
        ' ===============================
        ' Return Excel to original state.
        ' ===============================
    
    
              Application.ScreenUpdating = screenUpdateState
              Application.DisplayStatusBar = statusBarState
              Application.Calculation = xlCalculationAutomatic
              Application.EnableEvents = eventsState
              ActiveSheet.DisplayPageBreaks = displayPageBreakState   ' This is sheet-level only.
    I suppose that will work. For each group within a specific network I typically will copy/paste 50 cells data and manipulate much of that data depending on the value in the 1st workbook after it is pasted into the 2nd workbook. Ex: change TRUE/FALSE to Y/N.

    The .value=.value will be a set of loops within loops to gather all of the data from workbook 1, place into correct worksheets and cells in workbook 2. wash/repeat until all worksheets in workbook 2 are populated with data from workbook 1.

    Worksheets in workbook 2 are labeled group # so group 1, group 2, etc...

    would using the filters still be the good way to go? I would still like to use the table column headers as the column # will change as this spreadsheet lives.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,213
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    Without knowing exactly what you are doing, or what your data is like, it's difficult to give an accurate answer.
    That said I would think that using filters would the best way to go. It means that you no longer need to figure out where the data to copy starts or finishes.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    I will endeavor to clear up the end goal.

    Workbook 1 contains data to be copied into workbook 2 on multiple worksheets
    as mentioned above workbook 1 will contain multiple values on a main search criteria for workbook 2 on the appropriate worksheet.
    Workbook 1 does not contain the data in the same order that workbook 2 needs. vertical storage vs horizontal access, different column/row names, nor sequential from A - ZZZ column # vs row 1 - 1000 population.

    In the table above (OP)
    Group Position Foo Stuff

    are columns 2 - 5

    The value in column Group on workbook 1 will be copied into workbook 2, worksheet (Group Y) such that Y is the group #, columns 2 - X, such that X = max Position, across row 7.
    The value in column Position on workbook 1 will be copied into workbook 2, worksheet (Group Y) such that Y is the group #, column 2 - X, such that X = max Position, transposed across row 10

    While the value for Group remains unchanged position will be a new value, same with the remaining 50+ cells needed to copy from workbook 1. Workbook 1 today has columns A - CA, and that is growing all the time with some cities having more than 1000 rows worth of data. I do not need all of that data copied over into workbook 2, just specific values.

    Each worksheet in workbook 2 will be identical in layout and data requested from workbook 1.

    In workbook 2, worksheet (group #) some data on row 5 might be found in column X on workbook 1, but row 6 in workbook 2 might require data from workbook 1 column BS, that prevents a straight copy/paste of data.

    I hope that is a bit more clear, yes not a small project, just trying to start out small so I can slowly wrap my head around the code. I HATE coding, but I hate spending hours upon hours manually copy/paste/transpose data that can be automated.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,213
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    Unfortunately that is even more confusing.
    However from what I do understand, this is no small task & you maybe better off paying a consultant to do it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    Agree, no small task. I have succeeded in the paste creating a manual copy/paste some data into a raw data worksheet, than create X number of worksheets based on the MAX number of groups, rename them, and copy/paste data from the raw data into the new worksheets. That is the code I pasted above so while this is a large project, ill avoid getting a consultant involved and keep asking my questions until i can build the code.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,213
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Use table column headers for cell reference & search criteria?

    Ok, that's fair enough.
    I suggest you start a new thread for the first part of this project & give exact information. The information you have posted here is so vague & blurry I find it impossible to understand what you want or need.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •