Thank you Peter_SSs
Their are no less than 2 workbooks. The data in, lets call it the Master Workbook has been populated over phase 1 of the projects. That data is semi static. As their are upgrades to the projects the data on the Master will update, otherwise it is static enough to be used as the repository for the data. (more info than our current SQL databases need/want)
Data from the Master is used to build multiple types of other workbooks built around the projects: support, updates, maintenance, alarming, etc...
HISTORY: during phase 1 much of the data kept in the Master was incorrect. As phase 1 progressed the data was corrected and updated. Other workbooks were used to populate data into the Master so those were largely built by hand. Towards the end of phase 1 we had 5 columns of data for each project that was known to be correct. Building an automated workbook from 5 columns was not to harsh. Code snippet to follow. We would manually copy those 5 columns worth of data from the Master into the other workbook and run some VBscripting to build out the new workbook. During the project data was collected and manually populated into the new workbook. At the end that data was cleaned up and moved into the Master.
Code:
' ================================================================
' Code to copy Group # from Overview sheet to new group Worksheet.
' ================================================================
j = 2 ' Sets j to starting value.
' Sets starting point for copy/paste of data to correct Cell.
Set rFoundCell = Worksheets("overview").Range("A1")
' Starts for loop to search for xNumber vaule on the Overview Worksheet in the Group column.
For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("a1:a200"), xNumber)
' Finds the xNumber value on the Overview Worksheet in the Group column.
Set rFoundCell = Worksheets("overview").Columns(1).Find(what:=xNumber, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
' Start of the copy/paste data from Overview to new Group # Wroksheet in a transpose form.
With rFoundCell
Worksheets("overview").Range(rFoundCell.Address).Copy
Worksheets("group " & xNumber).Activate
' .Cells([row], [column]) - 06-12-2018 procedure was row 3
' 18 July, 2019 procedure now row 8
Worksheets("group " & xNumber).Cells(8, j).PasteSpecial xlPasteValues
j = j + 1
End With
Next lCount
xNumber is populated from the user via InputBox
That code is then slightly modified for each of the 5 columns worth of data. While that may not be the most efficient (yes screen updating and other updating is disabled during the process) it did its job and reduced a 40 hour process down to under 30 min.
CURRENT: The Master uses Table's containing thousands of points across many worksheets. Yes this is a good thing for us. I am trying to take the process from phase 1 and update the code to be able to pull hundreds of those data points from the Master to build out any of the other workbooks we will need in phase 2.
The Master is not locked so the column #'s will change. With the use of Table's that should not matter as long as the table column headers do not change. Instead of manually populating the "Overview" worksheet in workbook 2 with all of the columns from the Master and updating my existing code that is fixed and hard-coded not taking advantage of the power Table's provide.
The new code will reside within the Master workbook. It will open another workbook to be used as a template to populate data from Master into workbook2. I already know how to open the new workbook and perform a save as on that new workbook. What I am needing to learn now is how to pull data from the Master[tTable[Column](specific cell in that column)] to new Workbook.Worksheet.Cell
Thus when you asked for a fixed example, I cannot provide a fixed example. What I can say is this:
1. Master data is stored in Table's, currently 50+ tables
2. Master data is stored column base, currently 100+ columns
3. Other workbooks need to be able to pull fixed data from the Master for their needs; ie: troubleshooting, updating, repair, maintenance on project items.
4. Other workbooks are typically more than just a list of data. Can include items such as check-lists, punch-lists, confirmations, point of contact, dates, people involved, etc... none of that is stored in the Master and will be manually populated as the task is completed. These workbooks are more human friendly for reading and following processes.
5. Other workbooks will not need all 100+ columns worth of data, but the amount they CAN use is growing. To reduce manually copy/paste process I want to automate the population of data from the Master out to these other workbooks.
While the exact output will be different for each type of "Other workbooks" the process so far as coding is concerned should be similar enough to build on.
ie: Copy data from Master tTable1 Column header (Group) to Punch-list workbook on worksheet (Group #) into column A row 15
Column A and row 15 will not be fixed points. that is just a 1 cell from Master copied into Punch-list.
The process of moving a single point, than creating loops (for, do while, with, etc...) to populate sections of cells from the Master into Other workbooks is what I am after.
I will be researching the Me. today, if my google foo holds up to understand that process to pull data from tables.
Code:
Me.ComboBox.List = Range("tName[Name]").Value
worked great to make the drop down list for my user form.
enough ramble... I am looking to use VBscripting to pull data from tables in Master Workbook to populate non-table new workbook.worksheet.cell/range
Thank you.