Combining select columns from multiple worksheets into one new worksheet

Renny · Aug 8, 2014

What I want to do seems frustratingly simple. I have about 10 sheets within one workbook, one for each department I am dealing with. Each sheet is structured the same way, with the same column headings. I want to create an overview worksheet that would pull the basics from each sheet and combine into one large table. Ideally, the worksheet name would be in column A on my overview page, and then I'd pull the text from columns A,B,D,G and K from each sheet to create the master list. Macro?

Renny · Aug 14, 2014

I'm using Excel for Mac 2011 - 14.4.3

DocAElstein · Aug 15, 2014

Renny said:
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)

Option Explicit Sub RennyTMW() '......sheet in my workbook, one of which is the Overview, and one is a 'reference page (that's where I keep all my data for lookup functions). 'The other 13 are all various departments for a festival I work for. 'First is the Overview page- fewer columns to condense info a bit. 'Second is the department page- all 13 have one identical to this, 'though they range in size from 1 or 2 rows to more than twenty. On Error GoTo TheEnd 'Not necerssary but a good idea: Tells the computer what to do if an error occour rathe than possibly crashing badly! Application.ScreenUpdating = False 'Not necerssary but speeds thimgs up a bit by not updating the screen every time. Dim MaxDeptRowNumber As Byte, OverviewSheetLastEntryRowNumber As Byte 'last Row Number used Dim RowCount As Byte, OverviewHeadingNumber As Byte, DeptHeadingNumber As Byte 'Integers used as counts in loops Dim NumberOfDepts As Byte 'Number of Departments =.... Let NumberOfDepts = Worksheets.Count - 2 '....number of worksheets - 2 Dim MaxOverviewHeadingNumber As Byte, MaxDeptHeadingNumber As Byte ' ' Number of headings you have Let MaxOverviewHeadingNumber = Worksheets("Overview").Cells(3, Columns.Count).End(xlToLeft).Column Let MaxDeptHeadingNumber = Worksheets(2).Cells(3, Columns.Count).End(xlToLeft).Column Dim wkshtNumber As Byte ' Number of the Worksheet counting from the left For wkshtNumber = 2 To NumberOfDepts + 1 Step 1 'Go througth all your Departments and.... Let OverviewSheetLastEntryRowNumber = Worksheets("Overview").Cells(Rows.Count, 2).End(xlUp).Row ' First check the last entry in Overview Sheet so you know where to start putting new enries in Let MaxDeptRowNumber = Worksheets(wkshtNumber).Cells(Rows.Count, 1).End(xlUp).Row ' get the maximum row number in the department For RowCount = 4 To MaxDeptRowNumber ' Go through every Row in the department and.. Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, 1).Value = Worksheets(wkshtNumber).Name 'Put the worksheet name in the first column For OverviewHeadingNumber = 2 To MaxOverviewHeadingNumber Step 1 ' go through every Overview heading... For DeptHeadingNumber = 1 To 14 'and for each Overview heading look at each department heading... If Worksheets("Overview").Cells(3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(3, DeptHeadingNumber).Value Then 'If a match is found... Worksheets("Overview").Cells(RowCount + OverviewSheetLastEntryRowNumber - 3, OverviewHeadingNumber).Value = Worksheets(wkshtNumber).Cells(RowCount, DeptHeadingNumber).Value 'Put the appropriate value from the department in the Overview Sheet Else ' Otherwise do nothing and.. End If Next DeptHeadingNumber 'Go to the next Department heading until you have been through them all, then Next OverviewHeadingNumber ' start again for the next Overview heading Next RowCount ' When you finish going through the row go to the next row Next wkshtNumber ' when all the rows are finished go to the next Worksheet TheEnd: 'Not necerssary but a good idea to have this: Computer comes here if there is an error Application.ScreenUpdating = True ' Important that this is always done, even in the event of an error: Turns the screen updating back on End Sub 'RennyTMW()

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

Overview

. 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

hiker95 · Aug 15, 2014

Renny,

I'm new to VBA, so this is a great wait to learn some new tricks.

See if something in my most up to date list, in the below link, will help you:

Training / Books / Sites as of 6/26/2014

http://www.mrexcel.com/forum/excel-questions/788128-compare-values-worksheets.html#post3857750

Renny · Aug 15, 2014

That looks like exactly what I want! I put the code in, but I'm getting this error message: Compile error: Invalid outside procedure. Any thoughts?

DocAElstein · Aug 15, 2014

Renny said:
That looks like exactly what I want! I put the code in, but I'm getting this error message: Compile error: Invalid outside procedure. Any thoughts?

As you "put the code in" I expect you used the method of pasting tha code into a module?. That should work. I do not recognise the compile error

Try the alternative method of selecting the code I put in the sheet module in the file I sent you:

To do this

'1. Right-click the worksheet tab that you are interested in (In your case the first sheet, OverviewSheet) and choose 'View Code'. This will open the VBE (Visual basic Editor) window. (Sometimes called the <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> development window)
'2. Place the cursor anywhere in the actual code you want to run. (The only one there in this case!)
'3. Hit F5. The code should run!! (IMPORTANT: always save your program just before you run any program. -Because you cannot use the Excel backward thing to clear any changes made by a macro)
'4. Make sure you have enabled macros whenever you open the file or the code will not run.

Renny · Aug 15, 2014

That's basically how I did it in the first place. . . still getting the error.

DocAElstein · Aug 15, 2014

Renny said:
That's basically how I did it in the first place. . . still getting the error.

. Hi,

Unfortunately, I lack the computer experience to help you with such a problem at this distance. It might be a mac compatibility problem that I do not know about. I have posted about up to now about 30 codes which have all worked up until now.

. You could try the Conventional method for inserting a macro:

- Open a fresh file of yours which has a similar format to the one you sent me. Resave it, but not in the conventional excel workbook format .xlsx. Instead selsct workbook with macros .xlsm

- Copy the code from this Thread

- Press simaltaneously keys ALT and F11 to get into the Visual Basic Editor

- Insert a Module: Either right click in the project Window on your file, then >> Insert >> module ; or select the insert tab above and select module

- Simply paste in the code where the cursor is flashing

- Press keys ALT and q simaltaneously to get back into Excel

- Press keys ALT and F8. Select in the dialog window macro RennyTMW and then run (Or alternatively just double click on RennyTMW)

- Again (IMPORTANT: always save your program just before you run any program. -Because you cannot use the Excel backward thing to clear any changes made by a macro)

. I have to go off line shortly. But let me know how you get on. I will try to help you further if and when I can. Maybe somebody else will read this thread and help in the meantime

Alan

DocAElstein · Aug 15, 2014

P.s. Try both the codes, the full one and the shortened simplified one

Renny · Aug 15, 2014

Sorted! I was having some compatibility issues, figured it out eventually.
Thank you, works great!

DocAElstein · Aug 16, 2014

Renny said:
Sorted! I was having some compatibility issues, figured it out eventually.
Thank you, works great!

Hi,
. Thanks for the feedback. Glad it worked.

. One last thing. Could you elaborate a little on the problem you had: - I have not helped anyone with a mac until now. It would be useful for the future if I do so again to warn somebody of a possible problem.
. Could you also check again your version of Excel. I am still puzzled that there is a 2011 version. Again this could be something to do with mac that I do not know about.

Thanks
Alan.

P.s.
Come back if you have problems in the future with the code. It was a Beginner’s attempt! There are certainly infinite improvement that could be made. Problems could also come up in the practice when using for lots of actual data.

One thing springs to mind: As I have written the code, the last entry is checked only for the First row. So it assumes that if you have an entry at all in a row then there must also be a driver entered. How many of the other columns are filled in or empty is not important

Combining select columns from multiple worksheets into one new worksheet

Renny

New Member

Renny

New Member

Excel Facts

DocAElstein

Banned user

hiker95

Well-known Member

Renny

New Member

DocAElstein

Banned user

Renny

New Member

DocAElstein

Banned user

DocAElstein

Banned user

Renny

New Member

DocAElstein

Banned user

Similar threads

Forum statistics

Share this page

Combining select columns from multiple worksheets into one new worksheet

New Member

New Member

Excel Facts

Banned user

Well-known Member

New Member

Banned user

New Member

Banned user

Banned user

New Member

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock