Create a data table from Multiple Tabs

[Grimm127]

Hi Guru's

I have a question. I have 10 Tabs. Each with 2,387 Rows with Columns A:BW. I want to merge all those tabs into One separate tab. I only need to extract Columns A:F; I-U;W-AH and all the rows. How do I set this up?

I would guess it would be something like

For each pnlTAB in Worksheets (Array ("Tab1","Tab",..ect)
go to tab 1 copy rows 1 thru 2,387 and go to Tab_Merge and paste at row 1
go to tab 2 copy rows 1 thru 2,387 and go to Tab_Merge and paste row 2,388
go to tab 3 copy rows 1 thru 2,387 and go to Tab_Merge and paste row 4,775

Something along those lines. Any help would be great as I am sort of stuck.....

Thanks in Advance

 

[My Aswer Is This]

Try this:
This script will add a sheet to your Workbook named "Master"
Then copy all the data you asked for into sheet named "Master"

Sub Copy_All_Sheets_To_Master()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = 1
Sheets.Add(Before:=Sheets(1)).Name = "Master"
    For i = 2 To Sheets.Count
        Sheets(i).Rows(1 & ":" & 2387).Copy Sheets("Master").Rows(Lastrow)
        Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next
Sheets("Master").Range("G:H,V:V,AI:BW").Delete
Application.ScreenUpdating = True
End Sub

 

[vcoolio]

Hello Grimm127,

You could also try the Union method:-

Sub CopyThings()

    Dim ws As Worksheet
    Dim lr As Long

Application.ScreenUpdating = False

For Each ws In Worksheets
     If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            Union(ws.Range("A2:F" & lr), ws.Range("I2:U" & lr), ws.Range("W2:AH" & lr)).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This code assumes that you already have a main sheet(Sheet1 in the code) set up with headings and each of the source sheets has headings in row 1 with data starting in row 2.

I hope that this helps.

Cheerio,
vcoolio.

 

[Grimm127]

I forgot to add when it copies it should ne saved as values since what it is copying has formulas.

 

[vcoolio]

Hello Grimm127,

A slight amendment to the code (post #3 ) as follows should take care of that for you:-

Sub CopyThings()

    Dim ws As Worksheet
    Dim lr As Long

Application.ScreenUpdating = False

For Each ws In Worksheets
     If ws.Name <> "Sheet1" Then
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            Union(ws.Range("A2:F" & lr), ws.Range("I2:U" & lr), ws.Range("W2:AH" & lr)).Copy
            Sheet1.Range("A" & Rows.Count).End(3)(2)[COLOR=#ff0000].PasteSpecial xlValues[/COLOR]
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

A similar addition to M.A.I.T's code should work also.

Cheerio,
vcoolio.

 

[Grimm127]

I am trying it this way since I don't want to copy every single tab in the spreadsheet but it doesn't seem to work. It goes to the first tab in the array and copies the the selected columns but it doesn't copy to sheet1 not does it go to the next tab.

Private Sub Workbook_Master_Build()




Dim wsMasterLoad As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

For Each wsMasterLoad In Worksheets(Array("NL_FTM_TRK", "NL_JAX_TRK", "NL_MIA_TRK", "NL_ORL_TRK", "NL_TAM_TRK", "NL_TRAN_OH", "RT_JAX_TRK", "RT_TAM_TRK", "BYRN SUMMARY"))

lr = wsMasterLoad.Range("A" & Rows.Count).End(xlUp).Row
Union(wsMasterLoad.Range("A2:F" & lr), wsMasterLoad.Range("I2:U" & lr), wsMasterLoad.Range("W2:AH" & lr)).Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues


Next wsMasterLoad

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[vcoolio]

Hello Grimm127,

I'm not sure what you are trying to do. The code in post #3 does as you describe in your opening post as does the code that M.A.I.T. supplied.

What do you mean by:-

every single tab in the spreadsheet

Do you actually mean "every single worksheet in your workbook?"

How many worksheets do you have in your workbook including the Summary sheet?

How many sheets do you wish to exclude from the procedure and what are their names?

Cheerio,
vcoolio.

 

[Grimm127]

No, I am saying I don't want to run this on every single tab. I have a total of 15 tabs. I only want to copy and paste to sheet1 10 of those tabs.

 

[vcoolio]

Thus, you have 15 worksheets, five of which you wish to exclude from the procedure. As per the second part of my last question, can you please supply the names of the five sheets you wish to exclude.

Cheerio,
vcoolio.

 

[Grimm127]

Thus, you have 15 worksheets, five of which you wish to exclude from the procedure. As per the second part of my last question, can you please supply the names of the five sheets you wish to exclude.

Cheerio,
vcoolio.

Exclude
2017A, 2016A,2015A,OH Allocations, OH Allocations Scale