All Excel Sheets data copy and paste into one sheet

[harinsh]

Hi Team,

I need your help in order copy and paste the data from different excel sheets to one main sheet.

I will get a file which may contain 10 sheets or 15 sheets depends on data and rows also not sure it varies from sheet to sheet. Now, I would like to consolidate all the sheet data except first row (heading) for first instance I need heading and second sheet onwards not required the headings hence, macro should copy from second row onwards.

The data should get paste one by one if first sheet contains rows till A100 then in main sheet for second sheet data paste should happen from A101.

Please help in the above requirement and let me know if you need further clarification .

Thanks

 

[Misca]

This copies all the sheets in the active workbook to the active sheet:

Sub ConsolidateSheets()

Dim MainSheet As Worksheet
Dim WS As Worksheet
Dim c As Range
Dim Rng As Range


Application.ScreenUpdating = False


'MainSheet=current sheet:
Set MainSheet = ActiveSheet 'Feel free to change


For Each WS In Worksheets
    With WS
        If .Name <> MainSheet.Name Then 'No need for this if MainSheet is in another workbook
            Set Rng = .UsedRange
        
            With MainSheet
                Set c = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)  'The first empty row
                    If c.Row = 2 Then
                        Set c = c.Offset(-1)    'c moves to the first row
                    Else
                        With Rng
                            Set Rng = .Offset(1).Resize(.Rows.Count - 1)    'Removes the first row
                        End With
                    End If
                    
                    c.Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value 'Writes the Rng values to c
            End With
        End If  'Remove or comment this one as well is MainSheet is in another workbook
    End With
Next WS


Application.ScreenUpdating = True


End Sub

 

[harinsh]

Thanks for your code. I need few changes here....please note that everysheet first two rows not required (need to delete while copying the data). As per heading I will manually paste it so, heading also not required to copy from the sheets. The reason for this in my sheets I have format issue in two beginning rows.

Another thing is before copy the sheet data it should select entire cells and should "De-merge" the cells and also paste special values to main sheet.

Thanks you very much

 

[Misca]

Try something like:

Sub ConsolidateSheets()

Dim MainSheet As Worksheet
Dim WS As Worksheet
Dim c As Range
Dim Rng As Range




Application.ScreenUpdating = False




'MainSheet=current sheet:
Set MainSheet = ActiveSheet 'Feel free to change




For Each WS In Worksheets
    With WS
        If .Name <> MainSheet.Name Then 'No need for this if MainSheet is in another workbook
            
            .Cells.UnMerge  'Unmerges the cells
            
            Set Rng = .UsedRange
                        
                With Rng
                    Set Rng = .Offset(2).Resize(.Rows.Count - 2)    'Removes the first two rows
                End With
                
                Rng.Copy
                
        With MainSheet
            Set c = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
            c.PasteSpecial Paste:=xlPasteValues
            c.PasteSpecial Paste:=xlPasteFormats    'If you only want to copy the values it's better to just the write the values (=see my previous code) instead of using the PasteSpecial
        End With
        
'Uncomment the next three lines if you want to delete the copied sheets:
 '       Application.DisplayAlerts = False
 '           .Delete
 '       Application.DisplayAlerts = True
        
        End If  'Remove or comment this one as well is MainSheet is in another workbook
    End With
Next WS


Application.ScreenUpdating = True


End Sub

 

[harinsh]

Thank you very much ....it is working perfectly however, in first sheet it is not taking data from A3 and taking from A4 and missing one row here....can you please fix it...Note that other sheets it's taking correctly i.e from A3 cells.

Thanks,

 

[Shrikant]

Thanks. It worked for me