pulling data from other spreadsheets

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
I have several spreadsheets that I want to be able to pull data from to another sheet. Say I have sheets A.xls, B.xls, and C.xls, I'm trying to make D.xls where I can press a button or whatever I need to and pull a series of cell data out of A, B, and C.xls. But I have no clue where to even start, could I get some code snippets or something that would help me get started? Thanks for your help!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello donnyp02,
Lacking some specifics (such as which sheet(s) and range(s) you want to copy from each workbook - and the destination sheet/ranges in workbook "D.xls" you want to use), here's a rather generic example that might help get you started.

It assumes the following:
(1) You're running the code from workbook D.
(2) Your workbooks "A", "B" & "C" will already be open when you run the code.
(3) You want to leave workbooks "A", "B" & "C" open when your done and be left in workbook D.
(4) The ranges you want to copy from each workbook is the contents of sheet1, column A.
(5) The destination in workbook D is sheet1: column A for Book A, column B for Book B and column C for Book C.

With all of the above in mind, maybe this will help.
Code:
Sub CopyFromOpenWBs()
Dim Wb As Workbook
Dim RngToCopy As Range
For Each Wb In Application.Workbooks
    Wb.Activate
    Select Case Wb.Name
        Case "A.xls"
            Set RngToCopy = Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
            RngToCopy.Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
        Case "B.xls"
           Set RngToCopy = Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
            RngToCopy.Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp)(2, 1)
        Case "C.xls"
            Set RngToCopy = Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
            RngToCopy.Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp)(2, 1)
    End Select
Next Wb
ThisWorkbook.Activate
End Sub
Hope it helps.
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
There are about 90 different cells I need to pull from each wb and put them in diff places on the master wb, If you could break down your code there as to where I would put the different cell ranges and whatnot in, I'm sure I could figure it out. Now you said that you assumed the wb's would be open, is there a way to do this keeping all the referenced wb's closed?
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
I'm trying to get your code working and I'm getting an error. Here is the code I have:

Code:
Private Sub CommandButton1_Click()
Dim Wb As Workbook
Dim RngToCopy As Range
For Each Wb In Application.Workbooks
    Wb.Activate
    Select Case Wb.Name
        Case "TimeCardBeta09.xls"
            Set RngToCopy = Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
            RngToCopy.Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    End Select
Next Wb
ThisWorkbook.Activate
End Sub


The error its giving me is Run-Time error '1004':
Application-defined or object-defined error.

And the error is in this line:

Code:
Set RngToCopy = Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))

Any ideas?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

Is the sheet name you need "Sheet1"...you may need to change that.
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
I created a column of data on Sheet1 of a blank workbook and the Set statement worked fine as it is written above..
Code:
 Private Sub CommandButton1_Click()
Dim Wb As Workbook
Dim RngToCopy As Range
For Each Wb In Application.Workbooks
    Wb.Activate
    Select Case Wb.Name
        Case "TimeCardBeta09.xls"
        Set RngToCopy = Wb.Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
            RngToCopy.Copy ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    End Select
Next Wb
ThisWorkbook.Activate
End Sub

Otherwise I would try this
Code:
Set RngToCopy = Ws1.Range(Ws1.Cells(1, 1), (Ws1.Cells(Rows.Count, 1).End(xlUp)))
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
I dont know what I could possibly be doing wrong. I made a new workbook with numbers in column A, made another blank workbook with a button with the code on it, and it still gave me the same error, is there any king of plug-in or anything that I need?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
If the button is on the second or destination sheet, you may try:

Dim Wb1 As Workbook
Dim Wb2 as Workbook

Dim RngToCopy As Range
Set Wb2 = ThisWorkbook

For Each Wb In Application.Workbooks
Wb.Activate
Select Case Wb.Name
Case "TimeCardBeta09.xls"
Set Wb1 = Workbooks("TimeCardBeta09.xls")

Set RngToCopy = Wb1.Sheets("Sheet1").Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))
RngToCopy.Copy Wb2.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
End Select
Next Wb
Wb1.Activate
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,796
Messages
5,574,361
Members
412,589
Latest member
ArtBOM
Top