Copying all the data from first column from 5 tabs and pasting it all in one colmun

[Imran Azam]

Hi Guys

how is everyone

I have an excel task where I need to automate a task which involves copying data from the first columns of 4 tabs ( column A:A from sheet1,sheet2,sheet3 and sheet4) and pasting all this data in to the column A on sheet5

Stacked one after another,

is this possible, if so how can I do this?

thank you for any help

 

[footoo]

Dim Ws As Worksheet
For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    Ws.Range(Ws.[A1], Ws.Cells(Rows.Count, 1).End(xlUp)).Copy _
    Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp)(2)
Next

 

[Imran Azam]

thank you for this , how to I implement this?

 

[footoo]

Sub MyMacro()
Dim Ws As Worksheet
For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    Ws.Range(Ws.[A1], Ws.Cells(Rows.Count, 1).End(xlUp)).Copy _
    Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub

1. Copy the above macro.
2. Open the workbook in which you want to add the code.
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor.
4. Choose Insert. | Module.
5. Where the cursor is flashing, choose Edit. | Paste.
6. Run the macro.

 

[Imran Azam]

thank you , this almost works , 2 issues , 1) I don't one cell a:1 to be copied ( that has the title which I don't need) 2) every time I run the macro I want the pervious data on sheet deleted and the new data to be pasted, can this be done?

 

[footoo]

Sub MyMacro()
Dim Ws As Worksheet, rng As Range
For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    Set rng = Ws.Range(Ws.[A2], Ws.Cells(Rows.Count, 1).End(xlUp))
    rng.Copy Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp)(2)
    rng.ClearContents
Next
End Sub

Or perhaps you mean this :

Sub MyMacro()
Dim Ws As Worksheet
Sheets("Sheet5").Range([A2], Cells(Rows.Count, 1)).ClearContents
For Each Ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    Ws.Range(Ws.[A2], Ws.Cells(Rows.Count, 1).End(xlUp)).Copy _
    Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub

 

[Imran Azam]

thank you so much for your help , I think I might not have been fully clear its very good what you given me but its not fully working, sheet 5 when the macro is run needs to take from column all colon A ( excluding the A1 as that is the title) sheet1 -sheet4 and paste it in column Asheet5 stacked one on top of the other, each time the macro is run sheet 5 needs to be deleted ( incase there is new data in the other sheet ) and the new data needs to pasted . this almost works , do I make any sense?

 

[Imran Azam]

this clears the sheets1-sheet4, I need it to clear sheet5 first and then paste the data I don't want sheet1 -4 cleared, thank uyou for your help almost there

 

[footoo]

thank you so much for your help , I think I might not have been fully clear its very good what you given me but its not fully working, sheet 5 when the macro is run needs to take from column all colon A ( excluding the A1 as that is the title) sheet1 -sheet4 and paste it in column Asheet5 stacked one on top of the other, each time the macro is run sheet 5 needs to be deleted ( incase there is new data in the other sheet ) and the new data needs to pasted . this almost works , do I make any sense?

Have you tried the second macro in post #6 ?

 

[Imran Azam]

perfect how do I change it os it pastes from cell A1 on sheet 1?