I have monthly sales data (for example) in different sheets, say in Sheet2 and Sheet3. In Sheet1, I have a column named Region that has few regions. I wish to copy all monthly sales figures from Sheet2 and Sheet3 and show the figures next to each region. See the above image.
Check out this post also... How to read Data from a closed Excel file or Workbook without actually Opening it.
Here's how it is done.
Option Explicit Private Sub Workbook_Open() Call copyAllSheetsToSheet1 End Sub Sub copyAllSheetsToSheet1() On Error GoTo ErrHandler Dim myWs As Worksheet ' Read all the sheets in this WorkBook. For Each myWs In ThisWorkbook.Sheets ' Ignore Sheet 1, since data is in sheets 2 and 3. If myWs.Name <> "Sheet1" Then If myWs.Name = "Sheet2" Then ' Read data range from source and copy it to its destination. Sheets(myWs.Name).Range("A1:B10").Copy Destination:=Sheets("Sheet1").Range("B1") Else Sheets(myWs.Name).Range("A1:B10").Copy Destination:=Sheets("Sheet1").Range("D1") End If End If Next ErrHandler: Debug.Print Err.Description End Sub
The above code will run when you open the Excel file, since I have written the code inside the Workbook_Open() event.
Now, every time you update the figures in Sheet2 and Sheet3, it will automatically reflect the changes in Sheet1.
Call the Procedure using a Button
You can call the above procedure by clicking a button. Simply, add a button control in one of your active worksheet, and call the procedure from inside the button's click event like this.
Private Sub CommandButton1_Click() Call copyAllSheetsToSheet1 End Sub
That’s it. It’s a very simple, yet useful code. Now you can quickly and efficiently copy data from multiple sheets to a single sheet. Remember, the data structure is important. Since, you are trying to extract data based on specific named range. If you don't know what a named range means in Excel, read this post.
Thanks for reading. ☺