VBA To Automate And Email Sales Report
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
ADVERTISEMENT
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
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Feb 2, 2008
I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.
The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).
For the current Year- period (month) and YTD (only up to the period displayed).
For the last year- period and YTD (only up to the period displayed).
The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales
I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.
I would also like to be able to select which period I am viewing but this is secondary.
I can upload an example if necessary.
View 5 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
Mar 16, 2014
I am trying to do my homework for college and the below excel grid was given to us to complete. I do not understand where to get the information it is asking. the first grid is the numbers we are suppose to use to input in the other grids. We are suppose to put a formula in on the last to two columns on each grid but I do not even know where to start.
Budget
Actual
Product
SaleUnits
$/Unit
View 7 Replies
View Related
Jul 24, 2013
I need taking thousands of line items of raw data and creating a model that can present the totals in an easy to read format. I've attached a sample of what my data dump looks like....though the actual dump is thousands of line items.
Data.xlsx
View 1 Replies
View Related
Jun 11, 2009
I have multiple customers in a list that I would like to create individaul tabs for each, with customer name and store #, and at the same time utilizing my sales sheet template for all customers. Is there a way to do this without copying and pasting one by one.
View 9 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
Mar 5, 2012
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
View 3 Replies
View Related
Jul 29, 2014
I have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35%
Total $ of sales-45%
Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
View 3 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
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Jul 2, 2009
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.
View 3 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
Oct 17, 2013
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
View 6 Replies
View Related
Jan 5, 2010
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies
View Related
Jan 4, 2005
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
View 9 Replies
View Related
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Oct 3, 2012
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
View 9 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 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
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
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
May 21, 2009
I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?
View 9 Replies
View Related
Nov 7, 2013
I would like to search an outlook folder for a variable in the subject line and return the date when that email was received. Here is the code i have so far...
Code:
Sub Get_pos()
Set olApp = CreateObject("Outlook.Application")
Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder
Dim olMail As Variant
Set olApp = New Outlook.Application
[Code] ........
View 2 Replies
View Related
Apr 29, 2014
I have specified the email addresses in the code to whom the email will be sent when the user presses "Send Email" button but now I want to add all the addresses in the Access table and write down the code that will send an email to those people whose addresses are in the Access table.
[Code] .....
View 5 Replies
View Related
Jul 23, 2014
I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:
Code:
If Range("B" & a) = "Y" Then
If citChev "" Then .Attachments.Add citChev
End If
If Range("C" & a) = "Y" Then
If citMits "" Then .Attachments.Add citMits
End If
If Range("D" & a) = "Y" Then
If citToyo "" Then .Attachments.Add citToyo
End If
If Range("E" & a) = "Y" Then
[code].....
Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.
Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.
View 2 Replies
View Related
