Merge lot of sheets in one sheet without copy paste

[shahidraiaruj]

Dear All,
i have 100 different sheet in one excel file with same colums. how can i merge these sheets in one without copy paste activity.??? please eny one help me.

 

[sandy666]

Re: How to merge lot of sheets in one sheet without copy paste

try PowerQuery aka Get&Transform with Append feature

 

[shahidraiaruj]

Re: How to merge lot of sheets in one sheet without copy paste

thanks sandy. any other option???

 

[sandy666]

Re: How to merge lot of sheets in one sheet without copy paste

Alt+D+P, it will be a PivotTable

or another feature: Consolidate

 

[Stacy Rueda]

Re: How to merge lot of sheets in one sheet without copy paste

Hi I want to share this with you. Someone shared it with me. Just modify the code or cells location. Hope this helps


Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook


With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True


'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"


'The links to the first sheet will start in row 1
RwNum = 0


For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name


For Each myCell In Sh.Range("D3,A7,D7") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell


End If
Next Sh


Newsh.UsedRange.Columns.AutoFit


With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[shahidraiaruj]

Re: How to merge lot of sheets in one sheet without copy paste

thanks dears i will try

 

[Wanderer_geologist]

Re: How to merge lot of sheets in one sheet without copy paste

You can use array or tranpose function in VBA command to aggregate the columns. One way will be to run the array consolidation over all the worksheet and drop it after in the summary sheet. Longest operation is always the write up, not the data pull.

The code given above is a bit of overkill as it's creating and deleting temporary storage. For that type of operations the power transform is the best option if one don't want to run VBA code.

 

[shahidraiaruj]

Re: How to merge lot of sheets in one sheet without copy paste

plz send the example for my understanding. I will be obliged.

 

[Wanderer_geologist]

Re: How to merge lot of sheets in one sheet without copy paste

Hi so find an example with array feeding
It's a bit complex as I'm running multiple loop but the main point
You need to capture the start and end columns of your records it has to be the same. Only advantage is that you don't go back and forth between a worksheet and the intent target but capture all the records then dump it in one place .

Sub CopyColRange()


Dim i, x, rowT, startRow, Cnt, CntProgress, ModOps As Long
Dim ColStart, ColEnd, ArraySize As Integer
Dim mydata() As Variant


ColStart = 4 ' let's assume you have record on the Fourth Column,
ColEnd = 10
ArraySize = (ColEnd - ColStart) -1
startRow = 2 ' let's assume that record start on the second row
Cnt = -1 ' This will be used for an array, first array increment is zero


For i = 1 To Worksheets.Count
    
    rowT = Worksheets(i).Cells(Rows.Count, ColStart).End(xlUp).Row
    For x = startRow To rowT
    Cnt = Cnt + 1
            For z = 0 To ArraySize
            ReDim Preserve mydata(ArraySize, Cnt)
            mydata(z, Cnt) = Worksheets(i).Cells(x, ColStart + z).Value
            Next z


    ModOps = x Mod 100
    
    If ModOps = 0 Then Debug.Print "Worksheet: " & Worksheets(i).Name, Round((i / Worksheets.Count) * 100, 2), Round((x / Cnt) * 100, 2)


    
    Next x
    
    
Next i


' when completed drop eveyrthing in targetr




For x = LBound(mydata) To UBound(mydata)


    For z = 0 To ArraySize
    Worksheets("MyPlace").Cells(x + 1, z + 1).Value = mydata(z, x)
    Next z


 ModOps = x Mod 100
 
If ModOps = 0 Then Debug.Print "Pasting Data in place: ", Round((x / Cnt) * 100, 2)


Next x


Erase mydata


End Sub

 

[shahidraiaruj]

Re: How to merge lot of sheets in one sheet without copy paste

thanks dear