pulling data from other spreadsheets

[donnyp02]

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!

 

[HalfAce]

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.

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]

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]

I'm trying to get your code working and I'm getting an error. Here is the code I have:

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:

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

Any ideas?

 

[XLGibbs]

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

 

[donnyp02]

It's Sheet1

 

[donnyp02]

Any ideas?

 

[XLGibbs]

I created a column of data on Sheet1 of a blank workbook and the Set statement worked fine as it is written above..

 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

Set RngToCopy = Ws1.Range(Ws1.Cells(1, 1), (Ws1.Cells(Rows.Count, 1).End(xlUp)))

 

[donnyp02]

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]

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