Hi there. This is my first post and I have limited vba experience but my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. Apologies in advance for the cobol style go tos and the declaring of some unnecessary variables as I've been copying, pasting and deleting all sorts of solutions over the last couple of days! I'm at the point where it all works fine until it hits Consolidate. Can anyone tell me what the error message means (and consequently what I need to do to fix it!) as I've been unable to find it anywhere. Thanks in advance!
Option Explicit
Sub ConsolidateExport()
'
' ConsolidateExport Macro
'
Dim wrk As Workbook
Dim sht As Worksheet
Dim rng As Range
Dim k As Integer
Dim Vector() As String
ReDim Vector(1)
Dim NoVector As Integer
Application.ScreenUpdating = False
Set wrk = ActiveWorkbook
Set sht = wrk.Worksheets(1)
NoVector = 0
For Each sht In wrk.Worksheets
If sht.Name = "Summary" Then
GoTo Nextsht
End If
If sht.Name = "Instructions" Then
GoTo Nextsht
End If
If NoVector = 0 Then
Vector(0) = sht.Range("B3", sht.Cells(3, 55).End(xlDown)).Address(, , , True)
End If
If NoVector > 0 Then
ReDim Preserve Vector(NoVector)
Vector(NoVector) = sht.Range("B3", sht.Cells(3, 55).End(xlDown)).Address(, , , True)
End If
NoVector = NoVector + 1
Nextsht:
Next sht
Sheets("Summary").Select
Sheets("Summary").UsedRange.Offset(1).Clear
Range("A1").Select
Selection.Consolidate _
Sources:=Vector, _
Function:=xlSum, _
TopRow:=True, _
LeftColumn:=True, _
CreateLinks:=False
Application.ScreenUpdating = True
End Sub
Bookmarks