Pulling data from multiple excel spreadsheets into one workbook

[lilbamboo]

Hi,
I'm new to Macro and VBA, I need help with the following task.

I wanted to pull data from multiple excel spreadsheet into one workbook based on the department code, account, account description, and month. Instead of going through hundreds spreadsheet copy and pasted, is it possible to use Macro or VBA to perform this task? I'm using Excel 2007. Attached you will find the master file and the two spreadsheets where i want the data to pull from. Any help would be greatly appreciated.

thanks,

[AB33]

I wanted to pull data from multiple excel spread sheet into one workbook ?
Is it multiple excel spread sheet from multiple books, or one book? I.e., Are these sheets on the same single book?
The reason I want to be clear is if it is one book with many sheets, it is easy to code, but if it is many sheets from many books, it requires opening the files and folders.

[lilbamboo]

The data is from multiple workbooks and each workbook have multiple sheets/tabs but there is only one specific tab that i want to pull the data from. Hope this make sense?

For example:

Workbook 1 - sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Workbook 2- sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Workbook 3- sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Workbook 4- sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Workbook 5- sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Workbook 6- sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5
Master

I want to pull the data from sheet 3 of all these workbook into the master file.

[Red Spot]

I have this code available at work, it can be done without opening a single other file though, even if you wanted to pull data from several sheets.
If I dont forget I'll post it tomorrow.

[lilbamboo]

Thanks in advance. Can you please post it? I really need it to wrap up my forecast cycle.

[lilbamboo]

So I borrow this code from Jerry Beaucaire in the forum and test it out for my request above but it doesn't work. I got the error message say the code it broken. Can someone please help?


Sub CollectData()
'Author: Jerry Beaucaire, ExcelForum.com
'Date: 11/23/2010
'Summary: Open all the files in a specific folder and add key data to database
' moves imported files to "imported" folder to preclude repeats

Dim fPath As String, fDone As String
Dim fName As String, fDepartment As String
Dim wsPXimport As Worksheet, wbMaster As Workbook
Dim dRow As Long, ErrMsg As String

'Setup
Application.ScreenUpdating = False
Set wsPXimport = ThisWorkbook.Sheets("PXimport")
fPath = "C:\Documents and Settings\Tpham\My Documents\Macro\"
fDone = "C:\Documents and Settings\Tpham\My Documents\Macro\"

fName = Dir(fPath & "*.xlsx")
On Error Resume Next

'Collect data
Do While Len(fName) <> 0
fDepartment = Format(Left(fName, InStrRev(fName, ".") - 1), "Department")
If Isdepartment(fDepartment) Then
dRow = wsPXimport.Range("A:A").Find(fDepartment, LookIn:=xlValues, LookAt:=xlWhole).Row
If dRow <> 0 Then
Set wbMaster = Workbooks.Open(fPath & fName)
With Sheets("2013 Operating template")
.Range("c7:q7").Copy wsPXimport.Range("D" & dRow)
.Range("c8:q8").Copy wsPXimport.Range("D" & dRow)
.Range("c9:q9").Copy wsPXimport.Range("D" & dRow)
.Range("c10:q10").Copy wsPXimport.Range("D" & dRow)
End With
wbMaster.Close False
Name (fPath & fName) As (fDone & fName)
Else
ErrMsg = ErrMsg & vbLf & " " & fName
End If
Else
ErrMsg = ErrMsg & vbLf & " " & fName
End If

fName = Dir
dRow = 0
Loop

If ErrMsg <> "" Then MsgBox "The following files were not processed:" & vbLf & ErrMsg
Application.ScreenUpdating = True
End Sub

[AB33]

Please use code tags with your code.
In which line of the code do you get the error?

[lilbamboo]

I believe the error start at the code listed in red below. I want to change his code to look at the department from my spreadsheet 1 and pull the data from this sheet to the master workbook. Whereas his code looking at the date and pull the information to the master workbook. I just don't know how to change the code around it.


'Collect data
Do While Len(fName) <> 0
fDepartment = Format(Left(fName, InStrRev(fName, ".") - 1), "Department")
If Isdepartment(fDepartment) Then
dRow = wsPXimport.Range("A:A").Find(fDepartment, LookIn:=xlValues, LookAt:=xlWhole).Row

Please Login or Register  to view this content.

[AB33]

Although I have not tested it, this line

Please Login or Register  to view this content.

appears to extract a data from the worksheet names

[lilbamboo]

so how should I fix my code? I'm not familiar with codings so this really doesn't help me much.

[Red Spot]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

What the above does is check for some variables in the current workbook and use those to open a number of files.
It than creates a formula in the workbook towards the closed workbooks, it than copies the formula and pastes the values in the same cells. Its more or less the same as putting direct links in the workbook.

[AB33]

I stripped out some of the lines

Please Login or Register  to view this content.

[AB33]

Redhot,
S is missing from your sub

[lilbamboo]

thanks you both. I'll test it out and let you guys know how that goes. Much appreciated!

[lilbamboo]

AB33, I tried out your codes and I got an error msg "Subcript is out of range". i'm not sure what this mean...

[AB33]

In which line of the code do you get the error? It is most likely to do with the sheet name is not find.

[AB33]

Try this code and let me know in which line you get an error?

Please Login or Register  to view this content.

[lilbamboo]

There is no error with the code you provided except it didn't work the way i want it to. The code just basically tell it to look at the folder an open 130 files and not copy any of the data from those files into my master workbook.

[curiosity1990]

Hey..

Although m sure there are better ways, I would do it as follows:

Please Login or Register  to view this content.

Continue this code for the remaining sheets.

A for loop can also be used if the number of sheets is very large.

Further, for each copy, the column A1 in your code will be filled with your department code. So you will need to add statements corresponding to the same accordingly!

Hope this helps!

[Red Spot]

Redhot,
S is missing from your sub

Mr Spot, Red, Red Spot or Sir will do fine
Fixed!

[lilbamboo]

Curiosity1990,
I tested out your codes but received error msg "400", what is that mean?

Please Login or Register  to view this content.

[lilbamboo]

Mr. Red Spot,

Should I use both of the codes tag you provide or either one should work?
thanks,

[lilbamboo]

I thought the codes below would work but when i ran it, I got the error mgs "object doesn't support this property or method", can someone please help? I'm running out of time to get this task complete. I spent so much time researching and refining the code but everytime an error pop up then i got stuck and feel like i'm not going anywhere with it.

Please Login or Register  to view this content.

[curiosity1990]

hey lilbamboo..

The subscript out of range error was dere coz probably you didn't save your master sheet as Macro enabled workbook and hence 'Master.xlsm' as opposed to 'Master.xlsx'??

Also, I don't understand the line you have changed to:

Please Login or Register  to view this content.

Most likely this is giving you the "object doesn't support this property or method" error. I don't think this is the correct way of defining the code; i may be wrong though!

If '2013 Operating template' is the sheet name, write it as follows:

Please Login or Register  to view this content.

Also, I noticed that you are not copying your Quarter Data from spreadsheet1 etc, only the month data. In this case, I have modified the macro to further first delete the quarter data from your excel spreadsheet before copying. Don't worry, the changes will not be saved.

Also, I realised that the panes are frozen in your spreadsheet, in addition to some blank cells, so the following code will not work.

Please Login or Register  to view this content.

As a result, your macro should look like:

Please Login or Register  to view this content.

Try and let me know!

If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

[lilbamboo]

I tried out the codes you provided, the spreadsheet 1.xlsx is open but the error msg "400" pop up on the screen. No data get copy to the master.xlsm workbook. What should I do now?

[curiosity1990]

Hi

Are you using the same sheets as you have attached in your query? Are there any hidden sheets in your Spreadsheet1?

The first thing you need to do is to add an Error trap to find out what the Error description is (if it can tell you that is)
Add this as the first row of code after the "Dim wb_1 As Excel.Workbook"

Please Login or Register  to view this content.

and at the end of the code put

Please Login or Register  to view this content.

I am using the following spreadsheets. Please run the code in master attached here and check if it works for you!

Spreadsheet 2.xlsxMaster.xlsmSpreadsheet 1.xlsx

---

If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

[lilbamboo]

Yes i'm using the same worksheet as attached and there is no hidden sheets in neither of the spreadsheet.

I added the error trap, got an error msg "Compile error: Label not difined"

I saved the master.xlsm you attached to test it out but the code didn't work the way i wanted it. Instead of copying the data from spreadsheet 1 and spreadsheet 2 to the master file, it's created a nother sheet in the master workbook and only copy sheet 1 data. I want the code to look at the department on column A and account column B to pull the data from spreadsheet 1 into the master file. I have 130 files so i don't want this code to pull 130 sheets in the master file. Hope this make sense. Thanks for you for time.

[curiosity1990]

Hey..

Sorry, I attached the wrong file for you.

Master.xlsm

Please check this one. It will copy all the data from Spreadsheet1 and 2 and paste into your master sheet. It will not be populating your department numbers.

As I had mentioned in my first post, the code for the same needs to be added!

Also, i dont understand:

I want the code to look at the department on column A and account column B to pull the data from spreadsheet 1 into the master file. I have 130 files so i don't want this code to pull 130 sheets in the master file.

What do you mean by this? Do you want to compare the department number and account number from your Master sheet with Data in Spreadsheets and update the Jan to Dec data?
If that is the case, your macro will be widely different!

[lilbamboo]

The codes work now but is it possible for you to help me twist the code to compared the department number and account number from the master sheet with data in the spreadsheet and update the Jan to Dec data?

Because each of the spreadsheet is saved as Department number, I want the code to look at the department and account on the master and pulling the data from each individual department into the master sheet.

Is there a way to do this without having to copy and paste the codes 100 times to make it pull the data from all the sheets into the master file?

[curiosity1990]

Because each of the spreadsheet is saved as Department number

Does this mean that the name of your sheets is the department number?

Also, from what I understand, your master sheet will have Department number and Account number and the remaining columns will be completed?
So your row number is static? Is this correct?

Also,

The codes work now

Can you tell what was the error in your code? Can help someone facing the same problem!

---

If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

[lilbamboo]

Does this mean that the name of your sheets is the department number? yes
Also, from what I understand, your master sheet will have Department number and Account number and the remaining columns will be completed? Yes
So your row number is static? Is this correct? yes
The code you provide work now, before i have wb_1.["2013 Operating Template"].Activate instead wb_1.Sheets("2013 Operating Template").Activate.

Now i just need your help with enhancing the code

[lilbamboo]

Also,
I tried to deleted the uncessary rows by adding the code below in the your code but got the error msg "Run-time error "1004: Application-defined or object-defined error. Any idea how to fix it?

Please Login or Register  to view this content.

[curiosity1990]

Hey

Your code to delete should be:

Please Login or Register  to view this content.

In such cases, I sdvice you first try the record macro feature before anything else, as that always give you the correct code!

---

If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

[lilbamboo]

Thanks!

DO you have a solution for my other questions?

[curiosity1990]

yea.. its possible, bt I'l need some time before I can get back to you..

Its EOD for me.. il have a look and get back to you by tomorrw..

What you essentially need to do is first open the file corresponding to dept no and use either a vlookup or some similar search fn to get the data of the account numbers.. Also a for loop, taking dept no from rows should do the trick to avoid writing the code 100 times or making a function!

I'l try and get back with some sample code tomorrow!

[lilbamboo]

Sounds good. I'll give it a try today and see if i can get something to work.

thanks again for all the help!

[jednified]

Sorry for butting in with question. I am curious as to where to put the code you guys provided. I am currently learning VBA on Excel as well and currenlty working on a project.

Thanks,

[Red Spot]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Mr. Red Spot,

Should I use both of the codes tag you provide or either one should work?
thanks,

The first bit of code eventually calls the 2nd bit of code.