Moving Items from Sheet to Sheet

Phishdawg 

One way to move data from multiple sheets to a single sheet in an Excel workbook is to use a formula.

You can use the INDIRECT and ROW functions to reference the data on each sheet and copy it to the first sheet. Here’s an example formula that you can use:

=INDIRECT("'Sheet" & CEILING(ROW()/50000,1) & "'!A" & MOD(ROW()-1,50000)+1)

This formula will reference data from Sheet1 through Sheet10 and copy it to the first sheet in sequence. You can adjust the formula to match the number of rows and sheets in your workbook.

You can also use VBA (Visual Basic for Applications) to write a macro that will automate this process for you. This method requires some knowledge of programming and may be more complex than using a formula.

Here is an example of a VBA code that you can use to move data from multiple sheets to the first sheet in an Excel workbook:

Sub MoveData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Set the starting row for the data on the first sheet
    lastRow = 1

    ' Loop through all sheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the first sheet
        If ws.Index > 1 Then
            ' Find the last row with data on the current sheet
            i = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            ' Copy the data from the current sheet to the first sheet
            ws.Range("A2:A" & i).EntireRow.Copy Destination:=Sheets(1).Cells(lastRow, "A")

            ' Update the starting row for the next sheet
            lastRow = lastRow + i - 1
        End If
    Next ws
End Sub

This code will loop through all sheets in the workbook, except for the first one, and copy the data from each sheet to the first sheet in sequence. You can adjust the code to match your specific needs.

To use this code, open the VBA editor in Excel by pressing Alt + F11.

Then, insert a new module and paste this code into it.

You can then run the macro by pressing F5 or by selecting it from the Macros menu.