Hi There,
Ive been asked to organize some daily data and it comes in large bulks of about 6000 rows a day. I need to organize this data into filtered worksheets so as I can send it to each specific area dealing with the reservations. I have been doing it manually for a while now but I know if I can get assistance in creating a VBA module / code to simplify this it would make it a lot easier.
What I am trying to achieve is I have 1 workbook which lists the branches (Column "GpBr") within various areas of business. I would like to use this workbooks list to filter the daily of bulk data I get.
Branches & Areas.xlsx
Area |
GpBr |
A |
U100 |
A |
U101 |
B |
U102 |
B |
U103 |
C |
U104 |
D |
U105 |
So the above is an example of a few branches (Column "GpBr") / Areas they are in the above table is in a workbook called "Branches & Areas.xlsx". So I need a VBA module /button to open up the daily bulk of data, filter the results by branches (Column "GpBr") listed in the above table, sort into individual Areas matching the branches (Column "GpBr") in the above table and copy each area to an individual worksheet in a new workbook. The only tricky bit here is in the daily bulk data there isn't an Area column so I will have to use an "IF" Statement possibly to achieve this. But im really not sure how.
My Daily Bulk Data Source is in a file called "All Data Source.xls" and contains the below headers with about 6000 rows of data.
Example Data Source.xlsx
Status |
Group |
GpBr |
Bill To Name |
Ticket-Reservation |
C Status |
R Created Date |
D Last Name |
C Vehicle Reg |
C Number |
P Comment |
R Comment |
S Name |
P Number |
Extension |
Preferences |
|
|
U100 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
U101 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
U102 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
U101 |
|
|
|
|
|
|
|
|
|
|
|
|
|
So when I get this daily data I would like to be able to just save it to my hard drive. Open up my first workbook (the 1st example) - click a button and it pulls all the information from the above workbook into new workbook, filtered by Branches (Column "GpBr") that ONLY appear in my "Branches & Areas.xlsx" (top example) and then save the filtered branches (Column "GpBr") into their specific Area which will be put into Area specific worksheets.
I have tried to find various modules and I found a button module which uses a text box input. Like this.
but the above code only works if I have the code in the daily bulk file. I don't know how to get it to search another workbook. But I also found this code.
I believe the above code is more suitable but again I don't know how to put it into my "Branches & Areas.xlsx" workbook to filter the bulk data from "All Data Source.xls" by branch and then create a new Workbook with branches (Column "GpBr") put into their rightfull areas into individual worksheets.
Any help would be grateful.
Thank you kindly
Sara x
Bookmarks