Combining Multiple Workbooks

  • Thread starter Thread starter yolanda.silva
  • Start date Start date
Y

yolanda.silva

I'm hoping I can get some assistance... I'm truly not familiar with
much VB (almost none) and I'm trying to get excel to do something
that, based on what I'm reading, is possible... I just need some
assistance.

I'm trying to combine a lot of one sheet workbooks into one master
workbook. I've found this reference to help:

http://www.rondebruin.nl/copy3.htm

But, I can't seem to make it work (because I don't understand VB
enough to know if I'm entering the stuff correctly?)

To try and simplify... what I'm trying to do is this:

I've created a tracker for about 300 employees to keep record of phone
calls that are received. The columns that matter for data are:

Account Number / Disposition / Reason / Other Reason

These are the only things the agents will be entering that matters...
of these 4 boxes... 2 are drop down boxes with prefilled information.
The information they will be entering will begin in row 7... columns
F, G, H and I. The tracker only has rows for data up to row 49 - same
columns. (Not all rows would be filled - most likely only about 20
rows or so but I provided extra, just in case).

Each rep will receive a blank tracker to save - which can be saved
into one folder so they're all together in the same location - but,
they'll still be a bunch of indivdual files. What I need to do is
have one database that houses all the information that people collect
- so that I will be able to determine how many calls were received
and, of those calls, how many were each "reason" code. The auto-
filtering I can figure out... but, can someone help me with figuring
out how to make these workbooks all compile into one with the range
that I need?

Oh - if you're going to just put the code - can you tell me where it
is that I need to "title" the page or whatever - because I'll end up
just copying it exactly as you have it - so that's where I get messed
up. I don't know what is supposed to be what I call it and what
should be left alone. If you tell me what I should title the pages,
I'll gladly do that!!

Thanks for any assistance!!!! :)
 
Yolande,
Indeed, as per http://www.rondebruin.nl/copy3.htm, but I guess you want to
hear a little bit more.
In the above reference, find the subroutine that starts with Sub
Basic_Example_1() which contains the full routine.
Copy the full routine to a VB workbook :
- open a new workbook
- <Alt><F11> to go the visual basic editor
- "Insert", "Module"
and copy the while subroutine in this module (ie up to and including the
first "End Sub"

Things you need to change:
1) MyPath = "C:\Users\Ron\test"
change the "C:\Users\Ron\test" to the directory where all workbooks are
stored.
2) With mybook.Worksheets(1)
Set sourceRange = .Range("A1:C1")
End With
change to
With mybook.Worksheets(1)
Set sourceRange = .Range("F7:I49")
End With

Now close the VB editor and save the workbook (the way the code works is
that it opens a new workbook and copies all the ranges F7 to I49 underneath
eachother in the new workbook).
Run the code through "Tools" "Macro's" "Macro" and select "Basic_Example_1"
and run....
Good luck

rdwj
 
WOW!! Yeah!! This worked!! I'm thrilled :) SOOOO excited!!

Two LAST teeny, tiny things... because MOST of the rows will likely be
blank... is there some way to have it filter out the empty ones so
that it doesn't put it into the spreadsheet? If not, it's not a HUGE
deal - but... it'd definitely make it cleaner looking!!! Also... is
there a way to have this macro automatically run when the one
spreadsheet is opened? That way anyone can do it?

THANK YOU!!! Even for someone as incapable of VB - you made this
simple enough!!

THANKS!!!! :)
 
It is easier to delete the empty rows after you merge the data.
Can you check one column for empty cells ?

there a way to have this macro automatically run when the one
spreadsheet is opened? That way anyone can do it?
Click to expand...

You can run the macro in the open event of the workbook
Copy this in the Thisworkbook module of the workbook

Private Sub Workbook_Open()
Call Basic_Example_1
End Sub

See
http://www.cpearson.com/excel/events.htm
 
I go to bed now but if you can check one column see this example
http://www.rondebruin.nl/specialcells.htm

Example for column A

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("A") ' You can also use a range like this Range("A1:A8000")
CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count
If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End With

On Error GoTo 0
End Sub


If you want to check more columns there is code in the loop example to do it
http://www.rondebruin.nl/delete.htm
 
I think maybe you're my new favorite person... ever :) It worked!
Yes, if column "c" is blank, the whole row can be deleted so I used
that to filter... works like a charm!! FANTASTIC!

I'll also use the macro to make it run on open - but, one other
question (I know, I ask too much!)... can you tell me the code to make
both of these macros run on start up?

Since you seem to be the expert... I have another question elsewhere
about having it sort data - as you're familiar with what I have
here... I'm hoping that, once all the data is in one combined workbook
- that I can have separate pages that will have each of the "reason
codes"... (i.e. one worksheet that has all of the "language barrier"
calls and one for all "already in contract" etc. I know I can
autofilter the info - but that leaves it on the same page. Someone
also showed me how to make the info copy to another page so I could
then auto filter - but, it won't let me auto filter from another page
(this was without any macros).

So, is there a macro that could be created (or that already exists?)
that would allow me to use what data I have now sorted and make
worksheets to separate all the different "options" that would be in
column D?

Thanks again!! You've been an absolute joy!!! :)
 
Hi Yolanda

Add a second Call in the Open event


Private Sub Workbook_Open()
Call Basic_Example_1
Call DeleteBlankRows_2
End Sub


See this page to create seperate worksheets or workbooks
http://www.rondebruin.nl/copy5.htm

You can use this one for worksheets
http://www.rondebruin.nl/copy5.htm#3)

Read the information good above the macro
***********************************

Check if the information in these lines is correct before you run the macro.

Name of the worksheet with your data table
Set ws1 = Sheets("Sheet1") '<<< Change

Data range: A1 is the top left cell of your filter range and the header of the first column,
and D is the last column in the filter range.
Set rng = ws1.Range("A1:D" & Rows.Count)

You can change the filter column to another column, my range starts in A so the 1
in my example is the A column(2 = B, 3 =C,...............)
rng.Columns(1).AdvancedFilter _
 
I responded via email so hopefully that will make things easier
because I'm just a little lost at this point! :) If, for some reason
that's not a possibility, just respond here and I can try to explain
my confusion... :)
 
Yes! It's fantastic! I feel bad... poor guy spent all day trying to
explain it to me - finally just took my database and made it work :)
Much simpler that way!! :)

But... it works! Data is being collected and reports being run!!

You guys are the best!!! THANKS!!!! :)
 
Back
Top