If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.
I know there are about a thousand of these floating around on here but I cannot seem to find one that helps. Here is what I have so far
Code:
Sub Book1()
Application.ScreenUpdating = False
Dim Masterbook As Workbook
Dim Mybook As String
Dim Pathname As String
Dim i As Integer
Application.DisplayAlerts = False
Set Masterbook = ActiveWorkbook
For i = 1 To 2
Mybook = Range("M" & i).Text
Pathname = "C:\Documents and Settings\leaxl49\Desktop"
Workbooks.Open Filename:=Pathname & Mybook
If i = 1 Then
ActiveWorkbook.ActiveSheet.Range("B4:BB4").Copy Destination:=Masterbook.ActiveSheet.Range("B4:BB4")
ActiveWorkbook.Close
Else
ActiveWorkbook.ActiveSheet.Range("B4:BB4").Copy Destination:=Masterbook.ActiveSheet.Range("F4:G")
ActiveWorkbook.Close
End If
Next i
Application.DisplayAlerts = True
End Sub
This is the error that I get when I try to run it
Quote:
Run-time error '1004':
Application-defined or object defined error
I am trying to pull data from "Copy Recap" B4:B44 and put it in "Book 1" B4:B44 of the other workbook. I have no idea if the names are right in that code or not, it is just something that I found on here from February of last year.
Re: 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.
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
__________________
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Linear Mode
