How to copy values from multiple worksheets to other worksheets in Excel VBA
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 SubFor 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 SubThe 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 SubIn 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.