I have a spreadsheet with a workbook for each job. Each workbook shows a list of materials needed. To make ordering them easier I would like these separate lists amalgamated into specific areas based on the supplier.
As the materials ordered are in a jumbled list the supplier name must be search on with an area and the results displayed in another sheet. Example attached.ExcelForum.xlsx
To consolidate the details from individual projects it would be much easier to use a single table in one sheet and then use the sort and select options at top of columns to get what you want - SEE sheet1
eg
sort first on supplier column
then on project column
which will give full list in project sequence and then in supplier sequence within that
or select just one one project etc
If you are interested I can write you a quick macro that you could run at any time to generate this automatically from the individual sheets
Ah yes, I can see how this would make things a great deal simpler, and YES PLEASE to the macro!
Okay, just had a good look at this again, the managers work from a spreadsheet list of around 100 items and type in the number they want. I have found how to condense this into a short list, as there are many gaps in the materials list when they do this. Once I have the condensed list I would like to be able to amalgamate them all together into one list, which can be filtered as you suggested. It is THIS which is causing me problems. Bearing in mind I have sometimes 30 different projects/completed materials spreadsheets I am dealing with at any one time!
So are you saying to me "WAIT" and "DO NOTHING" until you have the condensed list prepared?
No, I am asking if you know how to amalgamate data into one sheet from up to 30 separate sheets...please..
Provided that
- the sheets are all in the same format and
- the data logically presented then
this is easy using vba.
Is the data in separate sheets in the same workbook, or in separate sheets in different workbooks?
ah-ha! Same workbook
attach the file (unless huge) and I will rattle off a quick macro
(if confidentiality an issue let me know and we will fix that first)
Do all the sheets need consolidating into a single sheet?
If not I need to know which ones.
Do all columns need to be included, if not, which columns do you want to see on the list
1. Yes all the sheets need to be consolidated as this will be a working list for me to order directly from
2. All the columns on the condensed (GREEN) area need to be included
Materials Test.xlsx
Holly
Open the file
enable macros
to run the macro CTRL + T
Then try adding in a few extra worksheets and running macro again
Does this achieve what you want?
For a quick answer I "block copied" rows 8 to 1008 from the "green zone" and then deleted empty lines on the assumption that the last line holding text under the "description" field is the last "true" line. Is this a fair assumption?
Apologies to anybody reading this who wants to see the VBA code - my screen is not displaying the "code tags" icon! I will post it later
Last edited by Kevin#; 01-12-2016 at 04:35 AM. Reason: clarification
Kevin, wonderful!, just wonderful, you are a most splendid person. I have printed out the VBA and now learning about it, having not used it before!
Last edited by HollyDesBois; 01-12-2016 at 05:34 AM.
Something fundamental you MUST understand about VBA :
In Excel if you insert a column or move a cell, then virtually everything adjusts to reflect that. Very clever!
BUT
In VBA the code is static and will do exactly what it did last time irrespective of what you have done to the structure of the worksheet.
Example
(say) code includes line Range("C1").copy
- if 2 columns inserted to left of column C, it now becomes column E
- the data that was in "C1" is now in "E1"
BUT the code will still copy "C1"
-the code is not aware that you have changed the "structure".
The attached Word document has some brief notes to explain some of the code
Lol, yes, just found that out as I didn't want to include cell 'O' as that is unnecessary so changed the Range to P8:V1008 and it all went wrong, changed it back now! How does the code identify to use all the worksheets, even when I add a new one? and does pRange mean Page Range?
Sheets.Count counts the number of worksheets in the workbook
and using a loop
For i = 2 To Sheets.Count
....
Next i
And so, to VBA, Sheets(i) is every sheet in turn (except sheet(1) - which is the position of sheet "AllProjects")
pRange is a name I invented for a temporary variable - I could have named it CellsToPutNameOfThisSheet buyt that would have been a lot of typing!
to eliminate column "O" amend this line as follows
ws.Range("B" & nextRow & ":I" & nextRow + 1000) = Sheets(i).Range("P8:V1008").Value
(ALSO LOOK AT POST.#16)
Last edited by Kevin#; 01-12-2016 at 07:27 AM.
That amendment should throw up an error (hint:it is in the line you amended)
And another 2 lines later in the code will need amending to reflect the removal of column "O"from the copyFrom area
Can you puzzle that out?
Ah - this is exactly what I tried and it threw the data column out, could you tell me how to amend this AND I have realised I need the project number on the AllProjects sheet (have made a column for it) and would appreciate seeing how you amend the code to accommodate this.
Sorry - missed this reply - will have a look!
Okay - have managed to move everything over so that all the columns are in the right place, I would really appreciate knowing how to pick up the project Number from B3 into the A column on AllProjects sheet.
I will be away from PC until about 5pm now
I have now amended the macro to assign Project Name(previously derived from SheetName) and Number(new addition) from cells B3 and B4
Did not need to amend most of code to reflect missing column O because you replaced it with another to hold Project Number
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks