LinkBack URL
About LinkBacks
I am new to this forum, so please forgive me if I have duplicated an existing thread. I have searched high and low for a problem similar to mine but have had no luck after several days of trying and tinkering. I also apologies if the title of this thread does not accurately reflect what I am trying to achieve - often knowing what the key buzz words are that describe your problem result in being able to find a suitable / similar example and reach a solution.
As per the title, I am looking to merge contents from multiple worksheets into a a single worksheet using VBA. Each seperate worksheet has the exact same layout and is considered a data entry form where data is captured relating to different people's skills and their relative level / rating. From here, I can then use a pivot table to filter and assess the data and skill levels to assist with decision making got resource and capability when undertaking new or existing projects, as skill gaps or strengths may be identified.
The problem I am experiencing is that I am not wishing to merge the data into the new summary sheet in the same format as the worksheet sources due to how the 'form' worksheets are laid out. I have attached a blank copy of my form to this thread to help readers visualise the layout.
I have multiple skills (vertically in the B column) mapped to the different stages of project lifecycle experience (in columns D to L) and then a relative score within the cells where the two intersect. There are then gaps in the rows to segregate different skill groups, with multiple skills within each group - for example skill group 1 uses rows 10 to 30, then skill group 2 uses rows 32 to 47, as row 31 is title for skill group 2, etc and so on.
My intention is to extract all of the values for each person (worksheet), which may be up to 100 people, and display it in the example shown in the summary tab - effectively each cell from the form is translated into a row which shows the relative lookup info that one would follow if interpreting the value from the form. For example, using cell D10 from the blank form, the newly merged row on the summary sheet would consist of
- name (from either the merged cell C4 to F4 or from the name of the tab/worksheet)
- Skill 1
- Lifecycle Stage 'R'
- the value assigned to this person
....and this is done for each cell, across all worksheets.
I hope this is all making sense.
Note that I have not included all of the worksheets within the spreadsheet (raw data set) as these contain sensitive data on individuals. The spreadsheet I have uploaded and attached has been modified to contain only the blank sheet which is the form that is populated by each person, and the summary sheet showing an example of how I wish to extract and merge the data from each worksheet, as described in the bullet points above.
I really would appreciate any help or guidance you may be able to offer as I have been working on this for several days now with no joy as I cannot find an example that is similar to it which requires the data to be manipulated during the merge to the summary sheet. No matter how small or trivial you may deem the advice, I would be most grateful and it may be something so simple that needs to be done in order to translate and select the relevant data for merging.
Thanks in advance,
Matthew
Electronics & Telecoms Engineer
Using: MS Excel 2007 / 2010
Register To Reply
Bookmarks