Automate Multiple Email Report.
Jun 7, 2009
Each week our department receives a single Excel report that contains ~15,000 rows of data.
Column A of this report is populated with a store number.
Every time the report is received, we go through a manual process in which we have to break the report down into individual store numbers and then email the data to the branch managers for their particular store only (~40 emails).
I have attached two spreadsheets (1) Invoice Report and (2) Email List.
To clarify:
Send data for branch 124 to mike@xyz-company.com (column header + row 2 only)
Send data for branch 126 to susan@xyz-company.com (column header + rows 3-9 only)
Skip branch 140 as there is no data
Send data for branch 159 to tim@xyz-company.com and d.ortiz@xyz-company.com (column header + row 10 only)
And so on.
I want to do is to (1) create a report for each branch in the list (2) email the report to the specified recipients and (3) delete the created report.
View 5 Replies
ADVERTISEMENT
Jul 24, 2013
I have the task of pulling results of an Access query into Excel, formatting them in a "standard" way (the data will be formatted the same everytime in every report), then AUTOMATICALLY emailing them at a pre-determined time everyday.
1. I have tried using the record macro function, and it works as far as pulling in query results and formatting them
2. Then, using the macro i recorded, i added onto it so that when I manually run this new macro, the whole thing gets emailed, but it is not automated. My issue is not having to manually run these macros and have them run as scheduled tasks.
View 2 Replies
View Related
May 21, 2014
where to start with automating a monthly report that I put together.
My report is generated from two or three tables of data - roughly 25,000 records.
Currently, I copy and paste this table of data into one worksheet, and then have pivot tables that are written to take data from this table (this is a typical marketing sales funnel - calls, raw leads, qualified leads, customers, revenue). Then I have another worksheet that is the display to the client, with the proper formatting, mathematical calculations, totals etc. On this client facing worksheet, I pull data from the pivot table (using =GETPIVOTDATA formulas).
It seems that I have constructed this report in a very inefficient way because I have a ton of manual work to do every month to make the client view presentable as changes come about in the raw data. For instance I segregate the client facing report by lead sources, but when new lead sources get added in month by month, I have to edit the client facing report at length.
where to start, or what steps to make this more automated
View 1 Replies
View Related
Jun 17, 2009
I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44
and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
View 4 Replies
View Related
Aug 8, 2014
I have this table that I put together everyday describing the activity of my department. There is the date displayed as "Num-Date", the day of the week and multiple columns of data. Every day I have to add a new row and update the date, and the data, etc. I have a macro that inserts a new row and auto-updates the date and weekday, I also have a web query set up to fetch the data I want and display it right next to the table, but what I want to do is make the table more intuitive and automated.
View 1 Replies
View Related
May 7, 2007
I am using the below code to save & email a report that is needed for a variety of different locations. I would like to be able to automate sending to multiple branches a branch specific report with some sort of a loop but I am not sure how to do it. So far, I have the below code that works for saving & emailing to one branch, however I was wondering if someone has an idea on how to loop the procedure and send to multiple branches? For example, if I have 3 branches 3 different managers:
Here is the code so far:
Private Sub emailreport_Click()
Password = "nohs1"
ActiveSheet.unprotect Password
On Error Resume Next
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb ....................
View 9 Replies
View Related
Oct 13, 2009
I am using the following code and it works great the only problem is that when I have more then one email address in the same cell it will not send the email. Even if I seperate it with a semicolon. It work fine if I have just one email address in the email field. How can I get it to send the same info to different email addresses.
View 4 Replies
View Related
Jul 13, 2008
I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.
I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.
View 12 Replies
View Related
Apr 30, 2008
I am using solver to get a list of values that make up mulitiple known values. I would like to make the code easier to apply instead of copying it and changing it for every cell. Auto Merged Post Until 24 Hrs Passes;
Sub Macro()
SolverReset
SolverOk SetCell:="$F$28", MaxMinVal:=3, ValueOf:="0.002", ByChange:= _
"$F$31:$F$37"
SolverAdd CellRef:="$F$31:$F$37", Relation:=1, FormulaText:="100%"
SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="$E$31:$E$37"
SolverSolve userFinish:=True
SolverReset
SolverOk SetCell:="$G$28", MaxMinVal:=3, ValueOf:="0.008", ByChange:= _
"$G$31:$G$37"............................
View 5 Replies
View Related
Mar 28, 2008
I am trying to automate text for a document. How can I combine Greek symbols and normal typing in one cell?
View 9 Replies
View Related
May 29, 2008
I am new to VBA coding. My boss recently handing me a task where I have to take data which is stored in thousands of fwd files and import it into spread sheets. This can be read with notepad as plain text. This data has about 35 lines of heading info i need to skip. The data i need transfered is stored in columns and each data row is tagged at the beginning with the letter D and comment lines begin with the letter C. The heading info has no such tags.The data is space delimited. Further, some of the columns of data need to be omitted in the spreadsheet. Each data.fwd file gets its own spreadsheet meaning each time i import data i start with a fresh template.xls to import into. The format of the spreadsheet is the same as the format the data is presented in the text file with ten columns, except the text has twelve and two of them need to be omitted.
One more thing, I need to be able to push a button and query the file to import.
View 7 Replies
View Related
Aug 6, 2014
I'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
View 2 Replies
View Related
Apr 3, 2013
My problem is automating a process that takes information from a single column with a range of D7:D77 in 22 spreadsheets and places this information in a summary spreadsheet as rows corresponding to the names of each spreadsheet. This is within the same workbook.
For example say spreadsheet A1 has a column from D7:D77 I would like this information in a summary spreadsheet with the row titled A1 and D1:D77 transposed. This would then be repeated for the remaining spreadsheets
View 4 Replies
View Related
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Mar 27, 2014
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
View 8 Replies
View Related
Jan 29, 2008
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
Tasks List:
Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes
Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............
View 9 Replies
View Related
Sep 19, 2007
I have a project that is quickly growing out of control.
I workbook made up of 14 worksheets. Oct - Sep, Summary and main.
I have been entering all my data in main, which is A - AB. Various types of data, dates, dollars, names, etc. I have been trying to sort the 'main' worksheet into the separate months based on a date in column B.
For example if the date in column B is 1 Aug, I would like the entire row copied to Aug 07 worksheet. Then in Aug 07 worksheet is the formulas to calculate the data needed for Summary page.
Pretty simple, except I cannot get it to do that. I have tried to pivot, auto sort, and a few various VBAs to no avail. One of my Googles turned up this site and many pointers have been found and are close, but most are focused on combining. The workbook is a tad over 1mb so I did not post it yet, but can if needed.
View 9 Replies
View Related
Apr 5, 2013
Below is a simplified/truncated version of a data set that I am using in a pivot table:
Client Asset Return
1 Port1 10%
1 Port2 12%
1 Port3 11%
1 Port4 13%
1 Port5 10%
[Code] .....
I have created a pivot table and I'm using report filters for both Client and Asset (obviously there are a lot more data points). When I filter on Client 1 I would expect to only see Port 1 - Port 7 available in the Asset filter however, I see every asset in the data set. I need to pick and choose using the Select Multiple Items check box without having to scroll through every single asset. Is there a way to easily do this?
View 1 Replies
View Related
Apr 21, 2014
I have to track how well location managers are keeping track of skill competencies.
We rate skills as...
0 – know nothing ,
1 – read training manual,
2 – check off by manager.
I need the managers to provide these ratings then I have to report what locations need more training. When an new employee is entered into the system, the manager should give them a target of 2 for each skill the employee needs to gain. If the target skill is 2 rating higher than the current skill level, I have a column named Delta that records a 2.
I have learned how to use the pivot table feature. I can make separate pivot tables using filters that show…
a) how many 2s each employee has for a target,
b) how many 2s each employee has for deltas,
a) how many 2s each employee has for a current level and I can show the date the 2s where achieved
What I need is to learn how to combine all of this information on one pivot table (or report), but the filters really seem to make a mess of it all when it is all together. adding multiple sheets to one Pivot table.
Screenshots...
Count of Targets
Count of Deltas=2
Count of Current Skills
!What I need to produce!
[Code].....
View 1 Replies
View Related
Dec 15, 2007
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
View 4 Replies
View Related
Jan 13, 2008
I came across a code on your website that looks across numerous sheets and stops at the first match. I have a similar need but only i do not want the code to stop when it has found the first match. I want the code to lookup from 1 column in 1 sheet to another workbook with 20 odd sheets, then want it to return a findings report or show all matches.
View 3 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jan 21, 2014
This is a sample of what I am trying to accomplish (file attached). I have information in one worksheet (called MasterList) and a second worksheet called (RecordList). I want to take information from MasterList and RecordList and combine them to produce a report (Results). Assume the user does not have access to MasterList or Results.
The user would enter the UID in RecordList, which then populates information from MasterList. There can be multiple entries for each UID and there is no set number of entries (could be 1, could be 500)
The user enters the UID into RecordList, which populates information from MasterList. The user then adds in the additional information into the fields.
What I want is all the information from the Master List must be reported whether anything exists in RecordList or not. If there is information in the RecordList, display it and on a seperate line for each entry. Each UID then needs to be totalled (which can be done through pivot table later).
View 1 Replies
View Related
Aug 3, 2014
I have to create a report that captures the work of 2 different resources on each day of the month, the sheet i am working with has 3 tabs - Main (this houses the main report, with identical fields for each resource), ABC - for details pertaining to work done by the resource ABC ... and a tab called XYZ for details of work performed by XYZ. A resource can work on multiple projects and 3 different modules in one day.
So for each date, i need to bring in the details for columns .. Project Name upto the column # of FB's, for each resource that is for ABC and XYZ.
I have attached a file with sample data, it has the main tab with what it looks like before the data is pulled from the ABC and XYZ tabs ... and what it should like after the required data has been pulled from the ABC and XYZ tabs.
What i am looking for is the formula that i got to enter in the main tab in order to pull the required data from the ABC and XYZ tabs for each date mentioned in each of the columns for each resource (that is ABC and XYZ) in the main tab.
View 3 Replies
View Related
Sep 25, 2013
I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data
Pay Categories:
Lead
Assistant
Admin
Pay Rates
Lead-$75/hr
Assistant - $40/hr
Admin - $20/hr
Sessions and Session Dates sample:
Session 1 - 10/8/2013 to 11/8/2013
Session 2 - 11/15/2013 to 12/15/2013
Session 3 - 01/06/2014 to 03/15/2014
*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.
Employees (Sample)
Jane Doe
Jack Doe
John Doe
Desire goals of the report:
1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list
2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.
3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.
View 1 Replies
View Related
Jan 9, 2007
Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
View 3 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Jun 6, 2014
Average.xlsxHi
I have changed the attachment to use CSV's
I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.
What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.
Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.
Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1
I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)
View 5 Replies
View Related
Feb 16, 2014
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
View 4 Replies
View Related
Jun 19, 2013
I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
View 2 Replies
View Related
