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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,440
Messages
5,831,643
Members
430,079
Latest member
lucasabreueng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top