Results 1 to 3 of 3

Consolidation needs to run quicker!!

Threaded View

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Consolidation needs to run quicker!!

    Good morning. I am using the following VBA to take data from multiple workbooks and copy into one master. It works, but it takes ages as I have to watch each of the workbooks open and then go through the copying of data and pasting into the master. Is there a way that I can put in additional code to stop me from seeing this and also to maybe speed up the whole consolidation process?

    Sub consolidate()
    Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
    fPath = "M:\Business Change - Projects\PMO\JAMES - TEST\Demand Management\Resource Forecasts\"
    fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            Set wb = Workbooks.Open(fPath & fName)
            Set sh = wb.Sheets("FORECAST")
                If Application.CountA(ThisWorkbook.Sheets(1).Rows(6)) = 0 Then
                    sh.UsedRange.Offset(5).Copy ThisWorkbook.Sheets(1).Range("A6")
                Else
                    sh.UsedRange.Offset(5).Copy ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
                    
                                  
                End If
            wb.Close False
            fName = Dir
        Loop
    End Sub[/B]
    Last edited by Richard Buttrey; 12-02-2015 at 05:22 AM. Reason: code tags added by RB

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Consolidation several tabs' data into a consolidation sheet via a loop?
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-30-2015, 08:18 AM
  2. A quicker way to use a loop
    By bkeat in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-08-2014, 11:57 AM
  3. Can the following Vba's be improved to run quicker.
    By Toonies in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2012, 06:54 AM
  4. Is there a quicker way to do this
    By jonn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2008, 11:12 AM
  5. Quicker way?
    By Craiig in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-22-2008, 07:32 AM
  6. Quicker VBA
    By sparx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-10-2007, 06:47 AM
  7. Is there a shorter/quicker way to do this?
    By SouthAfricanStan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2006, 11:40 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