Copying multiple tabs in a workbook to one master sheet

[gretti24]

Hi

I have a workbook that contains numerous tabs. Each tab is a new vendor and contains information in a table format. Every tab/table is the same.

I would like to find an easier way to get all of the data into one sheet rather than copying the table from sheet 2 paste into cell A1 of sheet 1. Going to sheet 3 copying the table and pasting it below the table in sheet 1 and so forth.

I am assuming a macro is needed for this?

Thanks!

 

[VoG]

Look here Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel

 

[mrmmickle1]

Try This:

[COLOR=#0000ff]Sub[/COLOR] Combine()

   [COLOR=#0000ff] Dim[/COLOR] J[COLOR=#0000ff] As Integer[/COLOR]
    
[COLOR=#0000ff]    On Error Resume Next[/COLOR]
        Sheets(1).Select
        Worksheets.Add
        Sheets(1).Name = "Combined"
        Sheets(2).Activate
        Range("A1").EntireRow.Select
        Selection.Copy Destination:=Sheets(1).Range("A1")
  [COLOR=#0000ff]  For[/COLOR] J = 2 [COLOR=#0000ff]To [/COLOR]Sheets.Count
        Sheets(J).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
[COLOR=#0000ff]    Next[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

Found on THIS WEBSITE

 

[gretti24]

Worked like a charm! Thanks.

 

[gretti24]

Back to the same question I posted awhile back. While this did achieve what I was looking for, it did require a bit of cleaning up before I could actually create a usable pivot table.

This time, I am trying to again copy all of the tabs into one sheet (like before to the combine tab) but now there are some additional things. First, on each tab at the end of the table in the last 2 rows is a summary line with totals of the costs and another row with the current date. When the table is copied over to the master sheet (combine tab) I would like it to neglect copying over the summary line and the date line.

Also, column A contains merged rows on the left side of the table with a vendor number in the first row of the merged section. Im trying to also find a way to unmerge and copy the vendor number to each row, or line item, that was previously empty and part of the merged section.

For example: A2:A20 is merged
A2 contains a vendor number 12345
trying to get 12345 on each row from A2:A20

 

[mrmmickle1]

Use something like this to Unmerge the Cells:

[COLOR=#0000ff]Sub[/COLOR] UnMergeAllCells()
Cells.UnMerge
[COLOR=#0000ff]End Sub[/COLOR]

As far as removing the Summary Rows you should like at the Resize Property.

Change this line:

 Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

To This:

 Selection.Offset(1, 0).Resize(Selection.Rows.Count [B][COLOR=#ff0000]- 3[/COLOR][/B]).Select

So the new code looks like this:

[COLOR=#0000ff]Sub [/COLOR]Combine()

  [COLOR=#0000ff]  Dim[/COLOR] J [COLOR=#0000ff]As Integer[/COLOR]
    
[COLOR=#0000ff]    On Error Resume Next[/COLOR]
        Sheets(1).Select
        Worksheets.Add
        Sheets(1).Name = "Combined"
        Sheets(2).Activate
        Range("A1").EntireRow.Select
        Selection.Copy Destination:=Sheets(1).Range("A1")
   [COLOR=#0000ff] For [/COLOR]J = 2 [COLOR=#0000ff]To[/COLOR] Sheets.Count
        Sheets(J).Activate
   [COLOR=#ff0000][B]     Cells.Unmerge[/B][/COLOR]
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count [B][COLOR=#ff0000]- 3[/COLOR][/B]).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
[COLOR=#0000ff]    Next[/COLOR]
[COLOR=#008000]   'Reset error handling[/COLOR]
[COLOR=#0000ff]   On Error Goto 0[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]