Combining select columns from multiple worksheets into one new worksheet

I need to know where you want the Data put on the master sheet. Add it to columns A,B,D,G,K or make new columns. Or put the Data in columns A,B,C,D,E

Renny said:

What I want to do seems frustratingly simple ....... Macro?

Click to expand...

Hi,
. It probably is so simple that even a beginner like me could have a go for you as a bit of practice some time over the next couple of days. There are also probably lots of different ways to do it so it is difficult to give you exact instructions, especially as we do not know exactly what you want, or how your sheets look.

. It would be most useful to have an example copy of your Spreadsheet Tables / Sheets showing the data to be pulled into the overview sheet and a manually made short example of how you want the overview sheet to look like finally.

To do this:-

. 1 Either type this info to us in a reply manually, (or use some sort of “screenshot”)

or if you are able try any of the following:

.2 Follow the instructions for downloading the Excel Table / Spreadsheet maker program (It is free) “MrExcel HTMLMaker20101230” – the second link in my Signature below. The download (again it is free) is the last link in my signature below.

.3 My Favorite: You can upload your workbook to FileSnack | Easy file sharing (Again that is free after registering), mark the workbook for sharing and then copy and paste to us the link given for your workbook. Again it would be useful if you include in your workbook a short manually written overview sheet showing how you want it to finally look like. ( Note: I use FileSnack | Easy file sharing a lot and find I can upload anything if I am using Internet Explorer 9. If you use Google Chrome Internet Explorer you may need to “Zip” the file before uploading.
. It does not need to be a full workbook, but should have an example list of all the different sorts of sheets and data you have.
. Once I have that file I could have a go at writing the code and then send the final workbook with code back to you.
. (There are other file sharing things out there I think, that you may prefer to use, for example https://www.box.net/de_DE/home/ but I have not tried them yet. I noticed that some experienced users of this board use FileSnack | Easy file sharing )

Alan

I want to put the data in columns a,b,c,d,e. Thank you!

Renny said:

I want to put the data in columns a,b,c,d,e. Thank you!

Click to expand...

Hi,
. I am still not quite sure what you want.

. But just to demonstrate the basic idea:-

. Say you have a Workbook with 3 Sheets. Initially the first sheet we will call, that is to say give it thhe name "OverviewSheet". initially it is empty so looks simply like this:




Sheet2 and Sheet3 are typical for your Tables and have typical data in them, such as this:





and this:







. After running the macro that I have just written for you for demonstration purposes, the first sheet, "OverviewSheet" changes to the following.





. Here is the code:


<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> CreateOverviewSheetForRenny()<br> <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'limits table length and width to 255</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> WkShtCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' limits sheets to 255</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> WkShtCount = Worksheets.Count <SPAN style="color:#007F00">' Work out the number of worksheets you have</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> TypicalRange <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">' Give name and all Methods and Properties of Range object for a typical table</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> TypicalRange = Worksheets("Sheet2").Range("A1").CurrentRegion <SPAN style="color:#007F00">' Give the size of a typical table (Here Sheet2)</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> TypicaltableLength <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Limit table Length to 255</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> TypicaltableLength = TypicalRange.Columns.Count <SPAN style="color:#007F00">' Work out your typical table Length</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> TypicaltableLength <SPAN style="color:#007F00">' For each of your table colums</SPAN><br>  Worksheets("OverviewSheet").Cells(1, x + 1).Value = Worksheets("Sheet2").Cells(1, x).Value <SPAN style="color:#007F00">' Write in Overview Sheet the headings</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> x<br>  <br>  <SPAN style="color:#00007F">For</SPAN> y = 2 <SPAN style="color:#00007F">To</SPAN> Worksheets.Count <SPAN style="color:#007F00">' For every Sheet....</SPAN><br>  Worksheets("OverviewSheet").Cells(y, 1).Value = Worksheets.Name <SPAN style="color:#007F00">' ...Put the sheet name in column 1, then...</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> TypicaltableLength <SPAN style="color:#007F00">' for every entity....</SPAN><br>    Worksheets("OverviewSheet").Cells(y, x + 1).Value = Worksheets.Cells(2, x).Value <SPAN style="color:#007F00">' Write in the entity under the appropriate heading and corresponding Sheet Row</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> x<br>  <SPAN style="color:#00007F">Next</SPAN> y<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'CreateOverviewSheetForRenny()</SPAN></FONT>


. Hope that gives the general idea.
Alan
Bavaria.

Hi,
After seeing the reply from Fluff in a thread today requesting a simliar solution ( http://www.mrexcel.com/forum/excel-...data-multiple-worksheet-columns-into-one.html ), I was able to write an improved version of the code:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CreateOverviewSheetFluffForRenny()<br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet <SPAN style="color:#007F00">' Give WS Methods and Properties through dot . thing</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LineCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Limit lines in overview to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> LineCount = Sheets("OverviewSheet").Cells(Rows.Count, 2).End(xlUp).Row <SPAN style="color:#007F00">' This goes to the end of your sheet in column 2, then goes back up to the last cell with someting in it, then gives you the number of that row. In other words it gives you the number of the last Row used in the second Column</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'limits table length and width to 255</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> WkShtCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' limits sheets to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> WkShtCount = Worksheets.Count <SPAN style="color:#007F00">' Work out the number of worksheets you have</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TypicalRange <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">' Give name and all Methods and Properties of Range object for a typical table</SPAN><br><SPAN style="color:#00007F">Set</SPAN> TypicalRange = Worksheets("Sheet2").Range("A1").CurrentRegion <SPAN style="color:#007F00">' Give the size of a typical table (Here Sheet2)</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TypicaltableLength <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Limit table Length to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> TypicaltableLength = TypicalRange.Columns.Count <SPAN style="color:#007F00">' Work out your typical table Length</SPAN><br>  <br>  <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> TypicaltableLength <SPAN style="color:#007F00">' For each of your table colums</SPAN><br>  Worksheets("OverviewSheet").Cells(1, x + 1).Value = Worksheets("Sheet2").Cells(1, x).Value <SPAN style="color:#007F00">' Write in Overview Sheet the headings</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> x<br>  <br>  <SPAN style="color:#00007F">Dim</SPAN> SheetNameAndDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">' Define a string variable fo the Sheet name and current date</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> y = 2 <SPAN style="color:#00007F">To</SPAN> Worksheets.Count <SPAN style="color:#007F00">' For every Sheet....</SPAN><br>  <SPAN style="color:#00007F">Let</SPAN> SheetNameAndDate = Worksheets.Name & " " & Date <SPAN style="color:#007F00">'Put it's name and the current date in variable SheetNameAndDate</SPAN><br>  Worksheets("OverviewSheet").Cells(y + LineCount - 1, 1).Value = SheetNameAndDate <SPAN style="color:#007F00">' ...Put the sheet name and date in column 1, then...</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> y<br>    <br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets <SPAN style="color:#007F00">'Go through every Worksheet</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Ws.Name = "OverviewSheet" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' If it is not the Overview sheet.</SPAN><br>        LineCount = LineCount + 1 <SPAN style="color:#007F00">'Increase the line Count</SPAN><br>        Ws.Range("A2").Resize(1, TypicaltableLength).Copy <SPAN style="color:#007F00">'The resize Property here gives a way of increasing the size of the line from the first cell to more cells to include all your data on a line. That data is then copied</SPAN><br>        <br>        Sheets("OverviewSheet").Cells(LineCount, 2).PasteSpecial Paste:=xlPasteValues <SPAN style="color:#007F00">'This pastes the line in the Overview Sheet</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> Ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'CreateOverviewSheetFluffForRenny()</SPAN></FONT>

. The Code is bit more proffesional and allows you to update your OverviewSheet and keep track of the changes. For example if you run this code today, assuming your overviewSheet looks as it did in my reply yesterday, then you get







. I am learning that the possibilities with VBA are almost endless.
. Hence it is always good to know exactly what you want, that is to say how your sheets look now, and an example of how the Overview sheet should finally look.

Alan

Wow- this is great! I'm new to VBA, so this is a great wait to learn some new tricks. I have 15 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. Here are some screenshots of my workbook: FileSnack | Easy file sharing and FileSnack | Easy file sharing.. 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.

Renny said:

Wow- this is great! I'm new to VBA, so this is a great wait to learn some new tricks. I have 15 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. Here are some screenshots of my workbook: FileSnack | Easy file sharing and FileSnack | Easy file sharing.. 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.

Click to expand...

Hi,
. Thanks for the extra info. I see a bit better now your requirement.
. If you want some more suggestions for a code to bring data into your overview sheet, than Post us an Excel Workbook over FileSnack. (Or use the MrExcel stuff I indicated earlier if you are able. Screen shots are of limited use once actual data is involved as one can not copy the data and anyone trying to help you would have to enter the data manually. )
. The workbook does not have to have all 13 department pages in. - 2-3 with a good spread of typical data in them is sufficient as the code can easilly be written to automatically determine how many sheets you have and process all of them. (So you could continually add sheets and the code would still take care of that). It would be useful if the Overview Sheet was also included in the workbook with the correct values you wish typed in manualy to assist in testing the code which would be bringing (Hopefully!!) those exact values jn automatically.

FileSnack wasn't working too well for me, but here's a Dropbox link to a mock-up:
https://www.dropbox.com/sh/doafueslvmvogjh/AABmznWQtdmD5j9a8ozMeOICa
This is way beyond my level of VBA, so thanks for taking the time to help me.

Renny said:

FileSnack wasn't working too well for me, but here's a Dropbox link to a mock-up:
https://www.dropbox.com/sh/doafueslvmvogjh/AABmznWQtdmD5j9a8ozMeOICa
........

Click to expand...

Hi,
. I have the File with Code more or less done for you.
. Please let me know what version of Excel you are using.
Alan