Thread: Pulling data from several workbooks to single Master workbook

Hi stin25,

You might get a better response if you posted this in the "Excel" Programming forum instead of the "Access" forum. Just ask one of the mods to move your post.

Cheers,

hi Stin25,

I agree with ConneXionLost's suggestion - here's a link to the Moderators:
http://www.excelforum.com/showgroups.php

I have made some changes (esp on the second dest address & including a sheet reference*) so hopefully the below will work for you...
(if not, please advise which line causes an error)
* there can only be 1 active sheet in an Excel instance & it is the one that appears when you press [alt + F11] while in the VBE.

Code:

Option Explicit
Sub ConsolidatingWBs()
'Dim Masterbook As Workbook
    Dim MasterbookSht As Worksheet
    Dim Mybook As String
    Dim Pathname As String
    Dim i As Long    'changed from Integer b/c some say there is an implicit conversion to Long
    Dim DestAddrss As String
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False    'why are you using this line of code?
    End With

    'Set Masterbook = ActiveWorkbook
    Set MasterbookSht = ActiveSheet
    'or should it be...
    'Set MasterbookSht = ActiveWorkbook.Worksheets("Book 1")

    For i = 1 To 2
        Mybook = MasterbookSht.Range("M" & i).Text
        Pathname = "C:\Documents and Settings\leaxl49\Desktop"
        Workbooks.Open Filename:=Pathname & Mybook
        If i = 1 Then
            DestAddrss = "B4"
        Else
            DestAddrss = "F4"
        End If
        With ActiveWorkbook
            .Worksheets("Copy Recap").Range("B4:BB4").Copy Destination:=MasterbookSht.Range(DestAddrss)
            .Close False
        End With
    Next i
    Set MasterbookSht = Nothing
    'Set Masterbook = nothing
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

hth
Rob