CODEWITHSUNDEEP
vbaexcel
Yigit Tanverdi
Yigit Tanverdi

Reputation: 161

merging worksheets into one

I have a masterworkbook, which includes variable amount of Worksheets, which have Name as table1 and then the rest of the Sheets are called data, data(1), data(2) etc. I want to copy all the column&rows of the Sheets which has Name starting with "data" and paste this to worksheet called "Table1".

Can someone help me with this?

Upvotes: 1

Views: 302

Answers (2)

kadrleyn
kadrleyn

Reputation: 384

I made some additions to the codes and added the ability to take the subtotal of the desired column:

Application.DisplayAlerts = False
    ActiveWorkbook.Worksheets("Grand_Table").Delete
    Application.DisplayAlerts = True

Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Grand_Table"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Last = FindLastRow(Sheets(1))
Selection.Copy
With Sheets(1).Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
  End With
  Next
  'Application.CutCopyMode = False
Sheets("Grand_Table").Activate

Sheets("Grand_Table").UsedRange.Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

Upvotes: 0

Dawid SA Tokyo
Dawid SA Tokyo

Reputation: 376

Based on the information, you could try something like this: 

Sub getDataFromSheets()

'loop throug all sheets in workbook
For Each sh In ThisWorkbook.Worksheets

'check sheet name
If Left(sh.Name, 4) = "data" Then

    With sh
        'get last row on data sheet 
        '***** CHANGE THE COLUMN LETTER IF REQUIRED
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'get last row on table sheet
        lRowTB = Sheets("Table1").Cells(Sheets("Table1").Rows.Count, "A").End(xlUp).Row + 1
        'copy the data from data to table sheet
        '***** ADJUST THE COLUMN LETTERS TO YOUR NEED *******
        .Range("A1:E" & lRow).Copy Destination:=Sheets("Table1").Range("A" & lRowTB)

    End With

End If

Next sh

End Sub

Upvotes: 1

Related Questions