VBA to consolidate data from selected multiple worksheets in a different workbook


New Member
Sep 26, 2014

I have 2 files. One file is the source of data and the other file is for extraction of the data and analysis. In the first file, I have many worksheets, but I would like to consolidate the data from worksheets, which contains "production" in the worksheet name. The destination of the extracted data will be in the second file, in worksheet "RDBMergeSheet".

How can I modify the macro below to allow me to do this? Would the source file need to be open at the same time as the destination file for the macro to work?

Thank you!

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

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

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

' Fill in the start row.
StartRow = 4

' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
If LCase(Left(sh.Name, 10)) = "production" Then

' Find the last row with data on the summary
' and source worksheets.
Last = LastRow(DestSh)

'Set the range that you want to copy
Set CopyRng = sh.UsedRange

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"summary worksheet to place the data."
GoTo ExitTheSub
End If

' This statement copies values and formats.
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

End If



Application.Goto DestSh.Cells(1)

' AutoFit the column width in the summary sheet.

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


Board Regular
Sep 17, 2014
Yeah, I'm pretty sure they both have to be open. Note- a file is the same as a workbook - everywhere you have ActiveWorkbook, it's referring only to the currently open & active file / workbook. If you want to refer to a different workbook that's open, you need to use workbooks("nameOfWorkbook") instead.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...