VBA needed

[priscilla_absi]

Hello,I have a worksheet that contains 40 sheets.Those sheets have the same header (column A till Q). The data in those sheets will be added consecutively. An additional sheet was created at the beginning of the workbook, which should serve as a summary one. It should contain the data present in all the sheets' column A till F. I need please some help in creating a vba code for the job to be completed...Thank you,

 

[VoG]

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

 

[MARK858]

Not exactly sure what you mean by

The data in those sheets will be added consecutively

but try the code below on a copy of your workbook.
Change the sheetname Summary to your Summary sheets name.
The code goes in a regular module

Sub Summary()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("A2:F" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Copy _
                    Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
End Sub

Edit: but also have a read of the link VoG posted while I was typing.

 

[priscilla_absi]

Thank you for your posts.

Mark,
your code worked but can you help me change it for the data copied on the summary sheet does not include the first row (meaning all the rows except the header)
What I meant by "The data in those sheets will be added consecutively": Can the code run automatically so whenever I type in one of the rows of a certain sheet, the data appears on the blank row on the summary sheet?

Thanks a lot!

 

[MARK858]

your code worked but can you help me change it for the data copied on the summary sheet does not include the first row

If you want a header row than try the code below, it takes the header from the second tab in so if the second sheet is the summary sheet then you will need to change the number in Sheets(2).

Sub Summary()
    Dim ws As Worksheet
    Sheets("Summary").Range("A1:F1").Value = Sheets(2).Range("A1:F1").Value
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("A2:F" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Copy _
                    Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp).Offset(1)
        End If
    Next
End Sub

If you want the headers copied for every sheet then change the A2 to A1 in this line (but I can't believe that is what you want)

   ws.Range("[COLOR="#FF0000"]A2[/COLOR]:F" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Copy _
                    Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)

Can the code run automatically so whenever I type in one of the rows of a certain sheet, the data appears on the blank row on the summary sheet?

The code below will copy the row when you double click the cell in Column F.
The code needs to go in the worksheet module i.e. right click the Sheet tab and click view code.
The code needs to go in the tab of every sheet bar the summary tab.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 6 Then
        If Target.Cells.Value <> "" Then
            Range(Cells(Target.Row, 1), Cells(Target.Row, "F")).Copy _
                    Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp).Offset(1)
        End If
    End If
End Sub