Public Sub consol WS() Dim data Sht Nm As String 'the sheet name of source data Dim consol Sht Nm As String Dim consol Last Row, looped Sht Last Row, looped Sht Last Col As String Dim msgbox Rslt As Integer consol Sht Nm = Input Box("Enter the worksheet name that you want to consolidate data in") If consol Sht Nm = "" Then msgbox Rslt Dummy = Msg Box("Action cancel", vb Information) Exit Sub Else data Sht Nm = Input Box("Enter wildcard conditions for worksheet name that you want to consolidate data from" & vb Cr Lf & vb Cr Lf & "For example, type data* to combine all worksheet with name starts with 'data' (case sensitive)" & vb Cr Lf & vb Cr Lf & "Type * to conslidate all worksheets except the consol sheet iteslf") If data Sht Nm = "" Then msgbox Rslt Dummy = Msg Box("Action cancel", vb Information) Exit Sub Else If Worksheet Exists(consol Sht Nm) = False Then Sheets(consol Sht Nm).
Select Sum from the dropdown and then use the button in the References field to access your spreadsheet so you can select the data you need.
Do this for all the data sets that you want to merge.
Either way, why not join the conversation in the comments section below?
Power Query can be of great help when you want to combine multiple workbooks into one single workbook.
Unfortunately, this process isn’t suitable if you want to merge cells with text in them — it only works with numerical data. This will come in especially handy if you perform this task on a regular basis. This fact should illuminate one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.
First, make sure that all the workbooks you want to combine are in the same folder on your computer. Open Filename:=Path & Filename, Read Only:=True For Each Sheet In Active Workbook. Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time.
If you have these workbooks in different locations/folders, it’s a good idea to move all these into a single folder (or create a copy and put that workbook copy in the same folder).
So to begin with, I have four workbooks in a folder (as shown below).
Instead, consider some of the smarter ways to accomplish the same task.