Use table column headers for cell reference & search criteria?

[LlebKcir]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City/Network[/TD]
[TD]Group[/TD]
[TD]Position[/TD]
[TD]Foo[/TD]
[TD]Stuff
[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]xyz[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]def[/TD]
[TD]pqr[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]asdf[/TD]
[TD]asdf[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]wer vd[/TD]
[TD]asdf [/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3254[/TD]
[TD]ghjk[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]qwre[/TD]
[TD]tyu[/TD]
[/TR]
[TR]
[TD]New York - Bronx[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]asdf[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]New York - Queens[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]mno[/TD]
[TD]srt[/TD]
[/TR]
[TR]
[TD]New York - Queens[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4536asf[/TD]
[TD]a43[/TD]
[/TR]
[TR]
[TD]New York - Queens[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]asdf v[/TD]
[TD]3425[/TD]
[/TR]
[TR]
[TD]New York - Queens[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2345[/TD]
[TD]2436[/TD]
[/TR]
</tbody>[/TABLE]

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 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

 

[Fluff]

Why not use AutoFilter or advancedFilter to filter the data & then copy visible cells?

 

[LlebKcir]

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.

 

[Fluff]

You can use both types of filter within VBA.

 

[LlebKcir]

That is good to know. As long as I turn off the updates

' ============================================          ' 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.

 

[Fluff]

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.

 

[LlebKcir]

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. <see example="" above="">
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) [TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Position[/TD]
[TD]Foo[/TD]
[TD]Stuff[/TD]
[/TR]
</tbody>[/TABLE]

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.</see>

 

[Fluff]

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.

 

[LlebKcir]

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.

 

[Fluff]

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.