In this article we will look at how to copy values from multiple worksheets to other worksheets within the same file based on a condition using Excel VBA.

Consider you have monthly sheets (named Jan, Feb, Mar and so on) that contain stock prices as shown below:

Now, you want to copy the prices only for a particular stock, say AAA, to corresponding monthly sheets that we have names Jan_AAA, Feb_AAA, Mar_AAA and so on. Let’s have a look at the code. Here we have assumed that the sheets are in the sequence, that is, Jan to Dec followed by Jan_AAA to Dec_AAA.

Sub copyData()

    Dim i As Integer, sheetOffset As Integer

    sheetOffset = 12

    'For each month sheet
    For i = 1 To sheetOffset

        'Function to filter and copy data from the source sheet
        stockFilter (Sheets(i).Name)

        'As the sheets are in sequence, the corresponding stock sheet will be 12th sheet from the source sheet
        Sheets(sheetOffset + i).Activate

        'Select Cell A1 of the destination sheet
        ActiveSheet.Range("A1").Select

        'Paste the data copied from the filter function
        ActiveSheet.Paste

        'To remove the marching ants around the copied data
        Application.CutCopyMode = False

    Next
End Sub

Sub stockFilter(sheetName)
    Dim currSheet As Worksheet
    Dim colNo As Integer

    'Set the month sheet (source)
    Set currSheet = ThisWorkbook.Sheets(sheetName)

    'Column No on which filter is to be applied (Stock Name)
    colNo = 1

    'Set the filter on data range (A:E) to get only the required data
    'Condition is column 1 is equal to AAA
    currSheet.Range("A:E").AutoFilter Field:=colNo, Criteria1:="AAA", VisibleDropDown:=True

    'Select the filtered data starting from A1 to the last cell that contains data
    currSheet.Range("A1").Select
    currSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    'Copy the selection
    Selection.Copy
End Sub


For each month, the output will look similar to this

As we know that the names of the sheets are fixed, we can remove the dependency on the position of the sheets, by storing the name of the sheets in an array.  For that, change the code in the copyData function as below:
Sub copyData()

    Dim i As Integer, sheetOffset As Integer
    Dim sheetNames

    sheetNames = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

    'For each month sheet
    For i = 0 To UBound(sheetNames)

        'Function to filter and copy data from the source sheet
        stockFilter (sheetNames(i))

        'Append name of the stock to the month name to get the destination sheet name
        ThisWorkbook.Sheets(sheetNames(i) & "_AAA").Activate

        'Select Cell A1 of the destination sheet
        ActiveSheet.Range("A1").Select

        'Paste the data copied from the filter function
        ActiveSheet.Paste

        'To remove the marching ants around the copied data
        Application.CutCopyMode = False

    Next
End Sub

The stockFilter function remains the same. This code will also give the same output as before.
Let us look at another example. In this, we will consolidate data in all the worksheets within a workbook into a summary sheet. Again work through the comments to understand what the code does, including looping through the worksheets.
Sub copyDataFromAllSheets()
    Dim sourceSheet As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    'Delete the Summary sheet if it exists.
    'If the sheet does not exist, error will be thrown and execution will resume from the next step
    On Error Resume Next
    ThisWorkbook.Worksheets("Summary").Delete
    On Error GoTo 0

    'Add a new summary worksheet.
    Set summarySheet = ThisWorkbook.Worksheets.Add
    summarySheet.Name = "Summary"

    'Loop through all the worksheets
    For Each sourceSheet In ThisWorkbook.Worksheets

        'Copy data only if the current sheet is not the summary sheet
        If sourceSheet.Name <> summarySheet.Name Then

            'Find the last row on the summary sheet
            lastRow = summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Row

            'Get the entire data range on the source sheet
            Set dataRange = sourceSheet.UsedRange

            'Copy the data from the source sheet
            dataRange.Copy

            'Paste the values and formats to the summary sheet after the last row
            With summarySheet.Cells(lastRow + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With

        End If
    Next

    'Go to the summary sheet
    summarySheet.Range("A1").Select

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub


In this way, we can copy part of the data or all the data from multiple sheets into one or more sheets in the same workbook.
For further information on copy pasting data, refer to “Why is the VBA Range.copy Method Useful?” And to see how to use PasteSpecial, click here.
We also use autofilter here — you can read more in this article.