Move all cells from all tabs into one column

[customesp]

Hello All!

Is there a macro to take every cell value from all my tabs (about 40) and send it to Sheet1, for example, in a single column?

Thanks,

Neil

 

[mumps]

It would be helpful to know how the data in your 40 sheets is organized. If it is multiple rows and columns, do you want the values copied to the column in Sheet1 by rows or by columns? Please clarify.

 

[customesp]

The data in the 40 sheets is strictly cell values randomly placed in an area that doesn't exceed row 100 or column CV (100x100) and I need all these cells moved to Sheet 1 column A. Let me know if you have any further questions. Thanks in advance.

 

[mumps]

Try:

Sub CopyCells()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            For Each rng In ws.UsedRange
                If rng <> "" Then
                    Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0) = rng
                End If
            Next rng
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[customesp]

Try:

Sub CopyCells()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            For Each rng In ws.UsedRange
                If rng <> "" Then
                    Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0) = rng
                End If
            Next rng
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Thanks for the response but I get an error stating "Can't execute code in break mode".

 

[customesp]

I got the macro to work! Thanks again! Much appreciated.

 

[mumps]

You are very welcome.