Get Data from columns from different sheets

[goble]

Hello excel gurus,

I have and excel file with :

Sheet 1:

Col A
data1
data2
<<Empty cell>>

Sheet 2:

Col A
data a
data b
data c
<<Empty cell>>

I have some 20+ sheets like "Sheet 1" and "Sheet 2".

I would like to have a new sheet (or maybe in another excel file) which will gather (pull) all the data from the different column A of all these sheets i.e result :

New Sheet :

Col A
data1
data2
data a
data b
data c

P.S The number of rows are different in each existing sheet.

 

[mumps]

Make sure that you have an existing sheet named "Result" and try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Result")
    For Each ws In Sheets '
        If ws.Name <> "Result" Then
            With desWS
                ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[goble]

Thank you very much.

However for the purpose of simplifying things I posted the examples below. The cells in columns A in sheet 1 and 2 contain formulas. Is there a way that the result display the output ?

Example :

Sheet 1:

Col A	Col B	Col C
=B2&C2	John	Smith
=B3&C3	Joe	Black
<<Empty cell>>

Sheet 2:

Col A	Col B	Col C
=B2&C2	Michael	Smith
=B3&C3	James	Black
<<Empty cell>>

Result Sheet :

Col A
John Smith
Joe Black
Michael Smith
James Black

Thank you again !

 

[mohadin]

Hi
mumps code amendment

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Result")
    For Each ws In Sheets '
        If ws.Name <> "Result" Then
            With desWS
                ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Copy
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[goble]

Hi
mumps code amendment

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Result")
    For Each ws In Sheets '
        If ws.Name <> "Result" Then
            With desWS
                ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Copy
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

This is perfect ! Thank you so much !

 

[mohadin]

You are very welcome
And thank you for the feedback
BUT THE CODE mumps's
All thanks to him