HOW TO: Combine identical tabs

[Josel]

I hope someone can help. This is my problem:

I have one file with 24 tabs containing identical tables with same number of columns and column labels. How do I combine all 24 tabs containing the tables into one such as the illustration below.

Tab 1



Tab 2



Tab 3




Hoping to get this from the 3 identical tables in those 3 tabs:




The tables are all in one file.

Thanks for the time reading and help.

 

[hiker95]

Josel,

You are posting a picture(s) . This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.


Is title Fruit in the same cell in each of your worksheets?

If so, what cell?



In the future:

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[Armando Montes]

This can help:
http://www.mrexcel.com/forum/showthread.php?t=437823

 

[hiker95]

Josel,

I have a macro ready to go, but:

Is title Fruit in the same cell in each of your worksheets?

If so, what cell?

 

[Josel]

Josel,

I have a macro ready to go, but:

Is title Fruit in the same cell in each of your worksheets?

If so, what cell?

Thank you. Yes the "Fruit" is in A1 in all the worksheets, and all the other titles are in B1, C1 as well.

My problem is that I have identical tables but they have been separated into several worksheets. I want to combine and see them all in one table.

I am using Office for Mac 2011.

I will try Box.net next time as it is more convenient.

 

[hiker95]

Josel,


Sample raw data worksheets:

Excel Workbook
	A	B	C
1	Fruit	Unit Cost	Qty
2	Orange	2.15	15
3	Mango	3.65	0
4	Grape	1.85	40
5
Sheet1

Excel Workbook
	A	B	C
1	Fruit	Unit Cost	Qty
2	Orange	2.15	10
3	Mango	3.65	1
4	Grape	1.85	5
5
Sheet2

Excel Workbook
	A	B	C
1	Fruit	Unit Cost	Qty
2	Orange	2.15	43
3	Mango	3.65	56
4	Grape	1.85	78
5
Sheet3

After the macro in a new worksheet Summary:

Excel Workbook
	A	B	C
1	Fruit	Unit Cost	Qty
2	Orange	2.15	15
3	Mango	3.65	0
4	Grape	1.85	40
5	Orange	2.15	10
6	Mango	3.65	1
7	Grape	1.85	5
8	Orange	2.15	43
9	Mango	3.65	56
10	Grape	1.85	78
11
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CreateSummary()
' hiker95, 05/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=551416
Dim wS As Worksheet, w As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"
Set wS = Worksheets("Summary")
wS.UsedRange.Clear
wS.Range("A1:C1") = [{"Fruit","Unit Cost","Qty"}]
For Each w In ThisWorkbook.Worksheets
  If w.Name <> "Summary" Then
    LR = w.Cells(Rows.Count, 1).End(xlUp).Row
    NR = wS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    wS.Range("A" & NR).Resize(LR - 1, 3).Value = w.Range("A2").Resize(LR - 1, 3).Value
  End If
Next w
wS.UsedRange.Columns.AutoFit
wS.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CreateSummary macro.

 

[Josel]

Josel,

I have a macro ready to go, but:

Is title Fruit in the same cell in each of your worksheets?

If so, what cell?

Here's the actual file I am working on:

http://www.box.net/shared/e04o8jqqx8

As you can see, I have 24 worksheets of identical columns but with varying number of rows--some long, some short, one has none.

Thanks for your time.

Your VB code worked by the way, but I realized it worked only for the first three worksheets.

 

[hiker95]

Josel,

Thanks for the workbook. I am not going to display any screenshots because some of the information may be sensative.

The macro has been adjust to compensate for no data in a worksheet except for the title row.

When you run the macro on the attached workbook you should end up with the last row of data in worksheet Summary is row 268.

Of the 268 rows, 14 are blank becuase some of the worksheets contained hidden rows that were blank. I can add to the macro so that any blank rows, actually those cells in column A that are blank, their rows will be deleted.




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CreateSummaryV2()
' hiker95, 05/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=551416
Dim wS As Worksheet, w As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"
Set wS = Worksheets("Summary")
wS.UsedRange.Clear
With wS.Range("A1:J1")
  .Value = [{"Date","Name","Race","Sex","Position Applied For","Referral Source","Disposition","Job Title","Date of Hire","EEO Cat."}]
  .HorizontalAlignment = xlCenter
  .Font.FontStyle = "Bold"
End With
For Each w In ThisWorkbook.Worksheets
  If w.Name <> "Summary" Then
    LR = w.Cells(Rows.Count, 1).End(xlUp).Row
    If LR > 1 Then
      NR = wS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wS.Range("A" & NR).Resize(LR - 1, 10).Value = w.Range("A2").Resize(LR - 1, 10).Value
    End If
  End If
Next w
wS.Range("A2:A" & NR).NumberFormat = "m/d/yyyy"
wS.UsedRange.Columns.AutoFit
wS.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CreateSummaryV2 macro.

 

[Josel]

Josel,

Thanks for the workbook. I am not going to display any screenshots because some of the information may be sensative.

The macro has been adjust to compensate for no data in a worksheet except for the title row.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CreateSummaryV2()
' hiker95, 05/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=551416
Dim wS As Worksheet, w As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"
Set wS = Worksheets("Summary")
wS.UsedRange.Clear
With wS.Range("A1:J1")
  .Value = [{"Date","Name","Race","Sex","Position Applied For","Referral Source","Disposition","Job Title","Date of Hire","EEO Cat."}]
  .HorizontalAlignment = xlCenter
  .Font.FontStyle = "Bold"
End With
For Each w In ThisWorkbook.Worksheets
  If w.Name <> "Summary" Then
    LR = w.Cells(Rows.Count, 1).End(xlUp).Row
    If LR > 1 Then
      NR = wS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wS.Range("A" & NR).Resize(LR - 1, 10).Value = w.Range("A2").Resize(LR - 1, 10).Value
    End If
  End If
Next w
wS.Range("A2:A" & NR).NumberFormat = "m/d/yyyy"
wS.UsedRange.Columns.AutoFit
wS.Activate
Application.ScreenUpdating = True
End Sub

B

The macro worked! Thanks a million, hiker95. I didn't know there were hidden blank rows but I can take it from here.

Can I use this as a standard macro and just edit Value "= [{"Date","Name"..." and "Resize(LR - 1, 10)" to say 9 or 8 column?

I am also curious if it will include hidden rows with information on them.

Thank you again.

 

[hiker95]

Josel,

You are very welcome. Glad I could help. Come back anytime.

I didn't know there were hidden blank rows but I can take it from here.

The macro will copy all rows from row 2 to the last row of data in column A, even if there are hidden rows.

Can I use this as a standard macro and just edit Value "= [{"Date","Name"..." and "Resize(LR - 1, 10)" to say 9 or 8 column?

Yes.