Forum Discussion

notaexcelexpert's avatar
notaexcelexpert
Copper Contributor
Jul 13, 2023
Solved

Moving data from one excel tab to another

Hi,   Is there an easier way of moving data from one excel tab to another other than just copying and pasting. I've got data in 70 tabs and want all that data in 1 tab.   Thanks
  • H2O's avatar
    Jul 13, 2023

    notaexcelexpert  Here is an example of a macro that can help you combine data from multiple sheets into one sheet.

    -------

    Sub CombineSheets()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim i As Long

    ' Create a new worksheet
    Set wsNew = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    wsNew.Name = "Combined Data"

    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
    ' Skip the new worksheet
    If ws.Name <> wsNew.Name Then
    ' Find the last row with data in the new worksheet
    i = wsNew.Cells(wsNew.Rows.Count, 1).End(xlUp).Row + 1

    ' Copy the data from the current worksheet to the new worksheet
    ws.UsedRange.Copy wsNew.Cells(i, 1)
    End If
    Next ws
    End Sub

    -----

    This macro creates a new worksheet called “Combined Data” and then loops through all the worksheets in the workbook. For each worksheet, it finds the last row with data in the new worksheet and then copies the data from the current worksheet to the new worksheet.

    To use this macro, you can press Alt + F11 to open the Visual Basic editor, then right-click on your workbook name in the Project window and select Insert > Module. Paste the code into the new module and then press F5 or click on the Run button to run the macro.

    I hope this helps

Resources