I'm using Excel for Mac 2011 - 14.4.3
Hi Renny,
. I have no experience with mac and I was not aware of a 2011 version of Excel. I have done the stuff for you in XL 2007 (PC with Vista). The code is fairly simple so I expect there should be no compatibility problems.
. I return you file to you with the code in it (Via Filesnack and box)
FileSnack | Easy file sharing
https://app.box.com/s/8k4k54tqsrtg7993q6eo
Here is the code (Which is in the OverviewSheet Module in your returned File)
<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> RennyTMW()<br><SPAN style="color:#007F00">'......sheet in my workbook, one of which is the Overview, and one is a</SPAN><br><SPAN style="color:#007F00">'reference page (that's where I keep all my data for lookup functions).</SPAN><br><SPAN style="color:#007F00">'The other 13 are all various departments for a festival I work for.</SPAN><br><SPAN style="color:#007F00">'First is the Overview page- fewer columns to condense info a bit.</SPAN><br><SPAN style="color:#007F00">'Second is the department page- all 13 have one identical to this,</SPAN><br><SPAN style="color:#007F00">'though they range in size from 1 or 2 rows to more than twenty.</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd <SPAN style="color:#007F00">'Not necerssary but a good idea: Tells the computer what to do if an error occour rathe than possibly crashing badly!</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Not necerssary but speeds thimgs up a bit by not updating the screen every time.</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MaxDeptRowNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, OverviewSheetLastEntryRowNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'last Row Number used</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> RowCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, OverviewHeadingNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, DeptHeadingNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Integers used as counts in loops</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> NumberOfDepts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Number of Departments =....</SPAN><br><SPAN style="color:#00007F">Let</SPAN> NumberOfDepts = Worksheets.Count - 2 <SPAN style="color:#007F00">'....number of worksheets - 2</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MaxOverviewHeadingNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, MaxDeptHeadingNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' ' Number of headings you have</SPAN><br><SPAN style="color:#00007F">Let</SPAN> MaxOverviewHeadingNumber = Worksheets("Overview").Cells(3, Columns.Count).End(xlToLeft).Column<br><SPAN style="color:#00007F">Let</SPAN> MaxDeptHeadingNumber = Worksheets(2).Cells(3, Columns.Count).End(xlToLeft).Column<br><SPAN style="color:#00007F">Dim</SPAN> wkshtNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Number of the Worksheet counting from the left</SPAN><br> <SPAN style="color:#00007F">For</SPAN> wkshtNumber = 2 <SPAN style="color:#00007F">To</SPAN> NumberOfDepts + 1 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">'Go througth all your Departments and....</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> OverviewSheetLastEntryRowNumber = Worksheets("Overview").Cells(Rows.Count, 2).End(xlUp).Row <SPAN style="color:#007F00">' First check the last entry in Overview Sheet so you know where to start putting new enries in</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> MaxDeptRowNumber = Worksheets(wkshtNumber).Cells(Rows.Count, 1).End(xlUp).Row <SPAN style="color:#007F00">' get the maximum row number in the department</SPAN><br> <SPAN style="color:#00007F">For</SPAN> RowCount = 4 <SPAN style="color:#00007F">To</SPAN> MaxDeptRowNumber <SPAN style="color:#007F00">' Go through every Row in the department and..</SPAN><br> Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, 1).Value = Worksheets(wkshtNumber).Name <SPAN style="color:#007F00">'Put the worksheet name in the first column</SPAN><br> <SPAN style="color:#00007F">For</SPAN> OverviewHeadingNumber = 2 <SPAN style="color:#00007F">To</SPAN> MaxOverviewHeadingNumber <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' go through every Overview heading...</SPAN><br> <SPAN style="color:#00007F">For</SPAN> DeptHeadingNumber = 1 <SPAN style="color:#00007F">To</SPAN> 14 <SPAN style="color:#007F00">'and for each Overview heading look at each department heading...</SPAN><br> <SPAN style="color:#00007F">If</SPAN> Worksheets("Overview").Cells(3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(3, DeptHeadingNumber).Value <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'If a match is found...</SPAN><br> Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(RowCount, DeptHeadingNumber).Value <SPAN style="color:#007F00">'Put the appropriate value from the department in the Overview Sheet</SPAN><br> <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' Otherwise do nothing and..</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> DeptHeadingNumber <SPAN style="color:#007F00">'Go to the next Department heading until you have been through them all, then</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> OverviewHeadingNumber <SPAN style="color:#007F00">' start again for the next Overview heading</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> RowCount <SPAN style="color:#007F00">' When you finish going through the row go to the next row</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> wkshtNumber <SPAN style="color:#007F00">' when all the rows are finished go to the next Worksheet</SPAN><br>TheEnd: <SPAN style="color:#007F00">'Not necerssary but a good idea to have this: Computer comes here if there is an error</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">' Important that this is always done, even in the event of an error: Turns the screen updating back on</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'RennyTMW()</SPAN></FONT>
And here again, the basic code without my ‘Green comment graffit!
Code:
Sub RennyTMW()
Let NumberOfDepts = Worksheets.Count - 2
Let MaxOverviewHeadingNumber = Worksheets("Overview").Cells(3, Columns.Count).End(xlToLeft).Column
Let MaxDeptHeadingNumber = Worksheets(2).Cells(3, Columns.Count).End(xlToLeft).Column
For wkshtNumber = 2 To NumberOfDepts + 1 Step 1
Let OverviewSheetLastEntryRowNumber = Worksheets("Overview").Cells(Rows.Count, 2).End(xlUp).Row
Let MaxDeptRowNumber = Worksheets(wkshtNumber).Cells(Rows.Count, 1).End(xlUp).Row
For RowCount = 4 To MaxDeptRowNumber
Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, 1).Value = Worksheets(wkshtNumber).Name
For OverviewHeadingNumber = 2 To MaxOverviewHeadingNumber Step 1
For DeptHeadingNumber = 1 To 14
If Worksheets("Overview").Cells(3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(3, DeptHeadingNumber).Value Then
Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(RowCount, DeptHeadingNumber).Value
Else
End If
Next DeptHeadingNumber
Next OverviewHeadingNumber
Next RowCount
Next wkshtNumber
End Sub
. It is very basic (written by a beginner!) but it gives you the basic idea. A more experienced Board member may follow up with a better code. (I hope so!- that is a great help in my learning!)
. Note:
. - You can add sheets or add extra headings in your overview Sheet and the Program will automatically take care of that.
. - As it is written you are limited to about 255 rows, columns, sheets etc. But that is a fairly simple modification to change that.
. I chopped out some of your data, limiting to 2-3 rows in each department, just to make it a bit easier to test and look at. With that in mind, I think you will see that the end result below (which you get in the Overview sheet after running the Macro) is what you wanted?
Book1 |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | | | | | | | | |
---|
2 | | | | | | | | |
---|
3 | Department | Driver | Model | PU Date | DO Date | Estimated Cost | Approved | Notes |
---|
4 | DV | TBD | Standard SUV | 9.1.14 | 10.1.14 | 264 | | |
---|
5 | DV | TBD | Compact | 07.09.2014 | 01.10.2014 | 216 | | |
---|
6 | DV | TBD | 15 Pass Van | 09.09.2014 | 01.10.2014 | 192 | | |
---|
7 | OPs | TBD | Compact | 04.09.2014 | 04.10.2014 | 264 | | |
---|
8 | OPs | TBD | Standard SUV | 04.09.2014 | 04.10.2014 | 264 | | |
---|
9 | Mobility | TBD | | | | #NV | | |
---|
10 | Management | TBD | Compact | | | 0 | | |
---|
11 | Management | TBD | Compact | | | | | |
---|
12 | Management | TBD | | | | | | |
---|
13 | Management | TBD | | | | | | |
---|
14 | Management | TBD | | | | | | |
---|
15 | Management | TBD | Compact | | | | | |
---|
16 | Finance | TBD | | | | #NV | | |
---|
17 | Stage Co | #1 | Minivan | 8.30.14 | 9.17.14 | 168 | | |
---|
18 | Stage Co | #2 | Minivan | 8.30.14 | 9.17.14 | 168 | | |
---|
19 | Stage Co | #3 | Minivan | 8.30.14 | 9.17.14 | 168 | | |
---|
20 | Production- Art Deco | TBD | | | | #NV | | |
---|
21 | Overseas crew | TBD | Minivan | 8.31.14 | 10.4.14 | 300 | | |
---|
22 | Overseas crew | TBD | Minivan | 9.10.14 | 10.2.14 | 192 | | |
---|
23 | Overseas crew | TBD | Minivan | 9.9.14 | 10.2.14 | 204 | | |
---|
24 | | | | | | | | |
---|
25 | | | | | | | | |
---|
|
---|
. I am not sure of your exact knowledge of VBA. If you need more help in the Basics of getting started and running the code then get back to me.
Alan