Get Data from columns from different sheets

goble

New Member
Joined
Oct 7, 2010
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Make sure that you have an existing sheet named "Result" and try:
VBA Code:
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
 
Upvote 0
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 ACol BCol C
=B2&C2JohnSmith
=B3&C3JoeBlack
<<Empty cell>>

Sheet 2:
Col ACol BCol C
=B2&C2MichaelSmith
=B3&C3JamesBlack
<<Empty cell>>

Result Sheet :
Col A
John Smith
Joe Black
Michael Smith
James Black

Thank you again !
 
Upvote 0
Hi
mumps code amendment
Rich (BB code):
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
 
Upvote 0
Solution
Hi
mumps code amendment
Rich (BB code):
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 !
 
Upvote 0
You are very welcome
And thank you for the feedback
BUT THE CODE mumps's
All thanks to him
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top