+ Reply to Thread
Results 1 to 2 of 2

Consolidate in Excel vba for multiple variable length sheets in a single workbook

  1. #1
    Registered User
    Join Date
    06-30-2014
    Location
    New Zealand
    MS-Off Ver
    2007,2010,2012
    Posts
    1

    Consolidate in Excel vba for multiple variable length sheets in a single workbook

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Consolidate in Excel vba for multiple variable length sheets in a single workbook

    Consolidate requires R1C1 style addresses. And your indexing was off. Here is the fixed code, and without the Goto

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 06-30-2014 at 09:28 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Consolidate multiple workbook sheets to one file - subscript out of range error?????
    By Crawfinator1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2013, 06:03 PM
  2. Replies: 20
    Last Post: 06-20-2013, 09:04 AM
  3. Replies: 0
    Last Post: 09-05-2012, 11:57 AM
  4. Help! Macro to consolidate data from multiple workbook sheets!
    By Sainath Krishnan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2012, 08:18 AM
  5. [SOLVED] Consolidate multiple spreadsheets into a single workbook
    By Andy T in forum Excel General
    Replies: 0
    Last Post: 04-24-2006, 08:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1