Condensing Multiple Worksheets Into One

Copying formulas that reference data in another sheet, or is to be moved to different rows or columns can, and often does cause extreme annoyance.
A simple technique
In a read only version, or a temporary copy of the source workbook, or in a copy 'sheetname (2)' of the intended source worksheet
first search for a string such as "£$£%" in the formulas of the range to be moved -
checking it is not in that range.

Then change all "=" to be that string
Now the data will move without Excel altering formulas.

Paste into a temporary worksheet of the target workbook a similar location
formats colours fonts etc.
But you may not want data validation - so clear that after the copy

Now change the "£$£&" back to "=" and you have the formulas copied without Excel adjustments.

If you were wanting to change some external sheet or workbook references it may be better to do that before restoring the "="
so that the replace will be actioned without Excel verification of formulas and references.
So - you can change a folder name, and then change a worksheet name as separate actions.

Next action - move the data to the appropriate location so that Excel adjusts references

Maybe - depending on the needs -
change the "=" back to the string before the move and then back to being a formula after the move.

Final action - move the copied and adjusted range to the place in the sheet where it is actually needed.

Use copy paste special values to check the effect of data in that new range on the system - alignment such as for charts
then paste special formats, and then formulas to set formats and hopefully get the whole set of data as you need it..

Note maybe more than you need in the above - but hopefully all that you need.

Another option with Excel 365 is VSTACK.

Again your data must have the same structure on each sheet.

On a new sheet, type =VSTACK(

Then go back to the first sheet and highlight the data to include. On this first sheet, select the headings as well as the data
Type in a comma, go to second sheet and highlight data (this time not including headings)
Repeat the last step for all other sheets, close the brackets and press enter.

Formula is: =VSTACK(first set of data including headings, second set of data, third set of data....)

An advantage of this method is that if you edit the data on one of the original sheets, it will update the data on the stacked sheet.

A drawback is that the stacked data is a spilled range, so there are restrictions on what you can do with it.

Hello Amita

You can refer to a worksheet by its tab number , ( also known as its item number or Index).
In Allen Wyatt’s macro, you can see that he is looping all worksheets via this code section

For Each s In ActiveWorkbook.Sheets
'
'
'
'
'
'
Next s

That code section is responsible for looping through all worksheets.
If you were to modify that code section something like the following, then you would only be looping from the 7th tab

For Each s In ActiveWorkbook.Sheets
If s.Index > 6 Then
'
'
'
'
'
'
Else
End If
Next s


Alan Elston

Can this be used to only combine sheets after a certain number? For example I have a workbook, where the number of sheets after sheet 6, can vary in number which are the sheets I want to combine. I essentially want to use a greater than sheet 6 function....

This macro worked really well to combine the worksheets. I was trying to copy the data in the combined worksheet into another workbook but I keep getting the message "The information cannot be pasted because the Copy area and the Paste area are not the same size and shape." I have been trying to paste cell values and only have my cursor in a single cell. Any advice?

Thank you very much for the help!

Thank you very mich for that makro! Very helpful! Could you maybe help me with one question? My heading contains 5 instead of 1 row. How could I adjust that?

Thank you very much in advance!

Best

Hi Andrea
It is a bit different what you want.
It is a very common thing to do, what you want. But giving you a full macro here will look a bit messy in the confines and format limitations of a Comment section. It is a complete new macro you need , not just a few changes to Allen Wyatt's above.
A Google search with like “VBA combining multiple workbooks into one” will flood you with examples.

If you then need further help it would probably be best to ask at a help forum, such as excelforum com or mrexcel com
You will get a quick answer there. If you are not in a rush I would do it for you over at excelfox com when I have time

Alan Elston

Hello,
This is exactly what i need except that the source is from different workbooks/files called FC_1, FC_2,FC_3... stored in the same directory.
The sheets is called "Account" for all files and have the same format. please can you help me?
Thanks
Andrea

Edit should be
Dim Cnt As Long

Hello Tina Glynn
The macro from Allen Wyatt is looping for each worksheet by virtual of these two lines
For Each s In ActiveWorkbook.Sheets
Next

s is Dimensioned ( declared ) as a worksheet

You can do it a bit differently in your case:
_ You can ignore the s
_ In VBA you can reference a worksheet by its Item number, which is its Tab number counting from the left.
_ Allen Wyatt’s macro adds a new worksheet at tab number 1, so then your first 3 tabs will shift to the right by one place and will then have the item numbers of 2 3 and 4

An alternative Looping section for you would be something like:

Dim Cnt
For Cnt = 2 to 4
Application.GoTo Worksheets.Item(Cnt).[a1] ' This will go to the second, third and Forth worksheet, Items 2, 3 and 4
Selection.CurrentRegion.Select
' Don't copy the headings
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)
Next Cnt



Alan Elston

Can this be used to only combine 3 worksheets and then stop? I have a workbook with 15 sheets, but I only want this to work for the first 3 sheets.

I understood, thank you, gentlemen, for your time!

Hi Peter…….The full syntax of what Allen Wyatt is using is like …….Cells(Rows.Count, 1).End(xlUp).Item(2) ……..….Item(2) will give us the cell just below the cell given by…….Cells(Rows.Count, 1).End(xlUp) ………….Cells(Rows.Count, 1).End(xlUp) is the same as …….Cells(Rows.Count, 1).End(xlUp).Item(1) ……………….It is not to easy to explain how the items are assigned for a range……See the demo that I have done for you here:…….…. ----- http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13070&viewfull=1#post13070 ----- ….As you will see, item numbers are not restricted to the range given. They keep going. …Cells(Rows.Count, 1).End(xlUp) returns just one cell…..But item(2) is the cell beneath…. Item(3) is two cells beneath ….etc…. ……As you will see, Item numbers are not restricted to just the range itself. The item numbers keep going. They go in a sequence of ... all columns in a row, ... then the next row ... etc........... The column count is determined by the original range, but the rows are not limited.…………Alan Elston

Peter
If Cells(Rows.Count, 1).End(xlUp).Row gives the last row number then Cells(Rows.Count, 1).End(xlUp)(2) gives an offset of 1 row. You can test this in some data with the line Cells(Rows.Count, 6).End(xlUp)(2).Select

Thank you, I come here ofter for the useful information. Can you please tell what digit "(2)" means in the end of

Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)

I can't figure out..

Thanks,