Computer Support Forum

Send emails based on Dates in an EXCEL sheet.

Question: Send emails based on Dates in an EXCEL sheet.

HI

I am looking at a partial automation requirement on Excel and Outlook 2016.

I have an database of my team in excel.

their date of birth is on b2, (EG : 23rd March 1973)
their email id is on h2

today being : 23rd March 2016, i would like an automated mail to go to their id and the rest of the organisation with a Happy Birthday Message and a random Birthday Pic.

The better if the Birthday message can be different for different colleagues.

any help from any of you will be credited on the sheet with your name.

More replies
Relevance 100%
Preferred Solution: Send emails based on Dates in an EXCEL sheet.

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevance 105.78%

I know variations of what I want to do exist but I have not had success in implementation. I am using Excel 2007 and MS Outlook.

After opening the file, I would like the macro to look at all the dates in the sheet and send an email (.Display is preferred) each time a date gets within 1 year, 6 months (180 days) and 90 days of the current date.

The headers in the columns are:
A-First Name
B-Last Name
C-email address

D, E, F, G, H, I, J-Certification 1, 2 ,3 etc. (All contain various expiration dates)

K-1 Year email sent. I've gathered from reading that another column indicating if an email was sent is a good idea and may be needed to trigger the other events.
L-180 Day Email Sent
M-90 Day email sent
 

More replies
Relevance 105.78%

Mr. Database created the macro in this spreadsheet and it works great. I need to know how to update the macro when I need to add rows or columns.
 

More replies
Relevance 103.32%

I manage a small database of paid memberships for a non profit. We are keeping up with membership dues and renewal dates in an excel spreadsheet. I would like to automatically generate an email through outlook 30 days before the renewal date, again 14 days before the renewal data (if they haven't renewed), again 5 days before renewal date (if they haven't renewed), and again 5 days after it has expired. Furthermore, I would like to customize the message of the emails if possible, including my signature that is saved in outlook.

In my limited knowledge of VBA/Macros, I would assume that I will need a column that is checked if a membership has been renewed, so that the macro will know whether to generate an email. Also, I am assuming that this would be easier if I had multiple macros instead of just one.

I am attaching a excel sheet that has the columns we will be using with dummy data.

Thank you.
 

Answer:Automate Renewal Emails To Members Based Off Excel Dates

11 more replies
Relevance 91.43%

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Answer:How can I auto-populate dates in Excel based on other dates?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 85.28%

Hello All,

This is my first time here, and I would be real grateful if someone could solve my problem.
I have a problem related to the following forum, but it goes a step further:
https://forums.techguy.org/threads/solved-automatic-mail-delivery-based-on-date-time.1138132/

Since I am new to programming, and have never done VBA, can you please guide or provide the Excel VBA code for my requirement. I could not find a relevant forum to add comment to, since all the related ones I visited are closed, so I had to create a new one.
Actually, it is for planning purposes at my end. I need to send emails based on holidays in different offices in different regions to a specific email addresses, so, people in all the offices can plan better.

I have attached a file named "Sync", having three different sheets, one having India Holidays and the other two having our US & UK office holidays.
For each of the holiday, in each of the three regions, I want to send an email 7 days prior (to the upcoming holiday) to an email address, say, [email protected]. This will help me plan the week and consider which regions will be working, and accordingly assign work.

Help will be greatly appreciated.

Many Thanks & Warm Regards
Tejeshwar
 

More replies
Relevance 81.59%

Hi,
If I have a column A with dollars in it, and column B with dates,
how could I write a formula to give me the total of column A
only when the date in B falls between 8/1/02 and 8/15/02?

I can get it to work if I say "<8/16/02" or ">7/31/02", but I can't get the range syntax....

Thank you!
kimmer
 

More replies
Relevance 81.59%

I have 2 excel files. I need them linked because in file 1 I have different activites that are on our books for a specific date. I want those activities, based on today's date, to automatically come over to file 2 so that when I open file 2 everday, the proper activites come over to that file. Any suggestions? thoughts?
 

More replies
Relevance 80.77%

I would appreciate help with an excel formula. I am not a techie so I would appreciate if responses are as transparent as possible. Column J lists participant start dates and Column K lists participant end dates. There are a myriad of start and end dates and many participants so the sort function does not suffice. I am hoping that it is possible to write a formula in which I can check total participants onsite on any given Monday (or week) of the year. Any help would be great. Thanks very much.
 

Answer:excel: participant numbers based on start and end dates

6 more replies
Relevance 80.77%

Hello,I am trying to create a column on my excel tracker that will give me the last date due before something is late. For example, I have Report A which is due to me 30 days before my suspense date, but Report B is due to me 60 days prior. Is there a way to make a formula that if Column A has the suspense date and Column B has the report type, Column C will calculate the date due based off the above conditions? Thanks in advance for any help.

Answer:excel formula that will compute dates based off of deadlines

=IF(B1="Report A",A1-30,A1-60)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 80.77%

Is it possible to send an automatic email as a reminder on chosen dates from a field of a table with a message from another field from the table? If so how?Example: automatically email birthday dates as a reminder from an access database to outlook.

Answer:Access database to send emails on chosen dates

You can ..You will need to write a select query to extract the fields you want in the reminder from a table where the date (e.g. birthday) is today: Datefield =Now()Then use a macro to do a send object command a send the output of the select query via e-mail. Hope this helps.

4 more replies
Relevance 80.36%

I get sent a spreadsheet, which has loads of data in, which is used, but i want to add a new sheet into the workbook called say "ratio", based on a sheet I can create "sales "and uses one of the existing sheets "customers" sent to me. with the minimum work

So i have attached an example of the one sheet I get sent (remember this is in a workbook with lots of other sheets and data - which needs to be kept) "customer"

I then have a sheet of sales with a similar layout

column A will have some of the same names in my sales sheet
so I update my sales into a "sales" sheet for the latest week, and copy the sheet into the workbook i have been sent

i now want a new sheet created which will
look at the names in columnA of "sales" and where they match the name in columnA of "customers " then on a new sheet called "ratio"do a calculation for the rows that have the same dates in

customer/Sales

I need to update and repeat this process every week.

Any help please
 

Answer:excel creating a new sheet based on results from two sheets

12 more replies
Relevance 79.95%

Hi All.

Hi i have a excel file which has Serial No , Task and date of completion as 3 ccolumns.
I keep on missing the tasks. I heared from somwwhere that we can create automatic reminder in outlook based date picked from excel. I want the same. Example on 1, april, 2010 - if i have to go to my lawer, the in outlook on 1st-Apr-2010 a reminder comes , visit to lawer Mr. XXXXXX.

If any one can help that would be of great help.

Regards,
sam
 

More replies
Relevance 79.95%

Hi, I am really new to excel so I am hoping to get some help here. I have searched through the forum and tried several codes, but I was not able to run it successfully.

Basically in my excel file, i have a long list of documents with their respective expiry dates. I want excel to send me an email when it is 6 months before the expiry date.

I have uploaded a sample file on what my worksheet will look like.
I am using Microsoft Excel 2010.

Thanks!
 

More replies
Relevance 79.95%

Hi Everyone
I'm needing to automate a process that notifies a group of users to complete specific data on a shared Excel template file. The process is date driven. For example; row 1 has data due on 4/25/2017 by Bob Smith, while row 2 has similar data due on 4/29/2017 by Rose Hill, etc. We are in a Windows 7 environment, Outlook 365 mail clients and recently updated to Office 2016 from 2010.
Anyone have any ideas on how to best approach a solution for via Excel macros?
 

More replies
Relevance 79.54%

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

Answer:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

3 more replies
Relevance 79.54%

I hope someone can help me with some VBA code. Sheet1 has 500+ rows and 30+ columns of data, sorted by text in column G. I want to create a data input sheet to manually key in data. I need help to create a macro to cut and insert the row into Sheet1 - the first row after it finds a match in column G. Thanks
 

More replies
Relevance 79.54%

Hi All,

Firstly, thanks to everyone reading, and greater thanks to those replying!

Here is my best attempt at explaining my problem...

In Sheet 1 I have data of "invoice number, "name", "invoice date","status", "amount", and some other headings. Everything is straight forward, except "status" will be either "outstanding", "paid" or "donation".

The headings are on the top horizontally, and the data is all vertical from that.

On Sheet 2, I need to return all the rows (the entire row's data), but only for those for which the status is "paid", and then on sheet 3 I need to return all the rows (the entire row) for those in which the status is "outstanding", and then the same applies for "donation" on sheet 4.

Currently I am using this very simple formula, =IF(Invoices!D3="outstanding",Invoices!A3,"") and then =IF(Invoices!D3="outstanding",Invoices!B3,"") in the next field, and the formulas go on like that en every cell to the right for each column on sheet1.

I have been using this just to get by, but as you can well imagine it is not very elegant, and since the rows on sheet 1 keep on being added downwards, I keep on having to copy my formulas down on sheets 2, 3 and 4, or I have to copy them down to like row 2000 just to keep ahead.

Is there a more elligant way of doing this - I have looked at vlookup, but unless ... Read more

Answer:Excel - Return Data on seperate sheets based on value in first sheet

6 more replies
Relevance 79.54%

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

More replies
Relevance 79.13%

Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.
Example:
1. Employees receive their regular check every Friday as well as a check for any time sold.
2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.
3. If itís greater than 7 days then it would be the Friday after next before they received the sold time check.
4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.
5. This pattern continues throughout the year. Note that weekends are included.
6. I think the attached will better explain what I am trying to do.
 

Answer:Formula in Excel for a Date to Remain Constant Based on a Range of Dates

I think there is something wrong in the way you explain it but, could be my interpretation though
I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.
 

2 more replies
Relevance 78.72%
Relevance 78.72%

Hello,
What a great help this forum is.
I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/
But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.
Is it possible?
 

More replies
Relevance 77.9%

Hi, this is my first post here and I will preface this by saying I am not familiar with VBA/VBScript language at all (only that it exists and perplexes me), which is why I'm requesting your help!
I have an Excel spreadsheet that I'd like to have send me an Outlook email reminder 6 months after the specified start date, without having to open the Excel file so the macro can run. The reminder email can be simple, just a short message like "Referral Alert: Check Spreadsheet"

I've attached a test file with the columns needed highlighted in yellow.

If this is too tall of an order, I understand!
 

Answer:Send Outlook reminder on specified dates from Excel

As far as I know, this is impossible in the given scenario. Macros only run once a file is loaded; therefore, not having the file loaded means any code won't execute.
 

2 more replies
Relevance 77.9%

Thanks for reading, first of all.

I am not the most savvy person when it comes to programming. I can manage my way around formulas pretty well, but when it comes to VBA and macros I am utterly lost. I believe I need a macro to do what I want, but I have no clue where to begin. I have searched forums but what I need is so complicated that I don't know where to look to find it so I thought I would ask for help.

I have a spreadsheet which tracks projects I am working on. Right now the sheet I am working on is "2013" for example. The sheet has several columns, but the only columns of significance to this macro are:

Account Name
Requested Date
Urgency
Due Date
Completed Date
Request
Receive
Complete

Account and Requested Date are currently manual entries.
Urgency is a data validation dropdown with: NORMAL, RUSH and OTHER.
Due Date is a formula which calculates a due date based on the Urgency selected. (Except Other, in which case I override the Due Date manually.)
Completed Date is also a manual entry.
The 3 stages are data validations which default to a blank cell and then have a Square Root sign (which looks like a checkmark) as the only other option.

Now that I have explained the source data, allow me to go into detail what I would like this to do.
I would like to populate the first sheet of the spreadsheet with seven lists. These lists would, ideally, tell me at what stage each project is at (by account and due date). The lists I have set up are in seven bo... Read more

More replies
Relevance 77.49%

Hi all,
I've spent a good few days reading posts on this site it seems between everyone here there a whole host of knowledge!

I'm here as I am having trouble with an excel sheet and I'm unsure how to solve it.
I am using excel 2007 on my laptop.

I've searched both this site and google for a few days now but I haven't found anything that seems to be what I need.
There is a thread on here that was helped by Keebellah and that is the closest I can find to what I am trying to do however I can't seem to edit the code in such a way that gets it working in my sheet.

Let me explain what I am trying to do.

My sheet is a training tracker which has a column of names of employees and some dates.
Specifically the dates are: Ideal WK4 date, Ideal WK8 date, Ideal WK12 date. These all have dates inserted.
There is also a column next to each of these that either says completed, or is empty.

I am trying to get Excel to email me when one of these dates is 7 days away or less, unless the column says completed. Then I don't want an email.
I want the email to basically say:
EMPLOYEE NAME is due for their WK? meeting within 7 days. Please schedule this in.

The employee name and WK4/8/12 should be pulled from the sheet.

Logically speaking, this is what I am looking for...
IF M(ideal date column wk4) = less than 7 days, send email with row data. If O=Complete don't send

IF Q(ideal date column wk8) = less than 7 days, send email with row data. If S=Compl... Read more

Answer:Solved: Emails from Excel using various dates

16 more replies
Relevance 77.08%

The sheet named, 'Data' has a row for each item. Dates of each item are identified in column B. I would be very greatful if someone could assist me in writing an excel 2010 macro that, when you press a button to exercise it, it has a dialogue box that asks for the start date and end date of the date range search you would like to run. Once the dates are entered, it then needs to troll through the sheet named 'Data', copy any rows where the date falls within the date range and pastes them into the sheet named 'Reports'. Please test in Excel 2010. Thankyou in advance :)

Answer:Reports Sheet to copy rows between 2 dates from Data Sheet

Private Message.Click on my user name, then click on it again on the page that opens.You should end up in the Private Message section with a very basic text entry field. Just be aware that I won't be responding until the moring, EST.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more replies
Relevance 77.08%

Good Afternoon all,

I have been trying to make my register e-mail when an expiry date is approaching. Now I've made a code to try and get this to work however I get a error 400 message.
The e-mail it needs to go to is in (A1)
The expiry dates are in row (P3:R164)
I would like it to also send the persons name in (J3:L164) within the e-mail as I havent worked this out yet.

Attached is the example workbook.

The code I am using is:

HTML:

Sub Email()
Dim objExcel
Dim objOutlook
Dim objMail
Dim objWB
Dim objWS
Dim vCell
Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("C:\Test.xls")
Set objWB = objExcel.ActiveWorkbook
Set objWS = objWB.ActiveSheet
For Each vCell In objWS.Range("P3:R164" & objWS.Cells(objWS.Rows.Count, "S").End(-4162).Row).Cells
If FormatDateTime(vCell) <= FormatDateTime(Date) Then
If vCell.Offset(0, 1).Value <> "YES" Then
Set objMail = objOutlook.CreateItem(olMailItem)
objMail.To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
objMail.Subject = vCell.Offset(0, -8).Value & "ChemAlert Password About to Expire"
objMail.Body = "COURSE TITLE - " & vCell.Offset(0, -8).Value & vbCrLf & _
"APPROVING STATE - " & vCell.Offset(0, -7).Value & vbCrLf & _
"APPROVING BODY - " & v... Read more

Answer:Excel coding to send an e-mail on expiry dates.

Hi, welcome to the forum.
Have you run a search on this forum, there are many posts with similar questions and answered too.
All you will need to do is probably edit it for your needs.
See if you can find these, no need to reinvent the wheel
 

3 more replies
Relevance 76.26%

Okay - here goes... I know I have seen a few questions similar to mine but no final answers.

I am trying to send a mass email to my distributors - approx 100 of them. I have their names, log in ID's and email addresses in an excel spreadsheet.

What I am trying to do is have the email for letter pull the info from the spreadsheet, put it in the email, and send it out but personalized to each person/company.

Fro example, I need it to pull XYZ co from the list, use their email address to send it to them, insert their contact name in the "Dear so & so" part of the letter, pull their ID for the log in from excel and place into the email, and send it out personalized with each companies info.

PS - If you give me programming info like some of the other posts showed - I need to know where do I put it/enter it etc? I'm not all that knowledgeable on this stuff but need to figure out how to make it happen.

Thanks in advance!
irishki
 

Answer:How to use Excel Sheet to send personalized mass email

http://spreadsheetpage.com/index.php/tip/sending_personalized_email_from_excel/
 

3 more replies
Relevance 76.26%

I found this code in this forum.
i want to add recipient as CC or BCC. What is the correct code for that?
Thanks in advance!

Code:
Public Sub email()

Dim SubJ, Recip As String

SubJ = "Enter your suject"
Recip = "[email protected]"


ThisWorkbook.SendMail Recip, SubJ

msgbox "Email Sent"

End Sub

 

Answer:Send excel sheet ( email) through macro with recipient and cc

6 more replies
Relevance 76.26%

I have read this thread http://forums.techguy.org/business-applications/775756-how-use-excel-sheet-send.html. I am looking to do the same thing but withh Outlook. What must I do differently?

"Okay - here goes... I know I have seen a few questions similar to mine but no final answers.

I am trying to send a mass email to my distributors - approx 100 of them. I have their names, log in ID's and email addresses in an excel spreadsheet.

What I am trying to do is have the email for letter pull the info from the spreadsheet, put it in the email, and send it out but personalized to each person/company.

Fro example, I need it to pull XYZ co from the list, use their email address to send it to them, insert their contact name in the "Dear so & so" part of the letter, pull their ID for the log in from excel and place into the email, and send it out personalized with each companies info.

PS - If you give me programming info like some of the other posts showed - I need to know where do I put it/enter it etc? I'm not all that knowledgeable on this stuff but need to figure out how to make it happen.

http://spreadsheetpage.com/index.php/tip/sending_personalized_email_from_excel/

Thanks for the info - that looks like exactly what I need ! Your awesome!
One more question tho ( please don't laugh me out of here)
Where do I enter the VB programming to make it happen - in Outlook?
In the email itself? In Excel?

With the workbook open in Excel, press ALT+F1... Read more

More replies
Relevance 76.26%

Hello, I have been having trouble parsing out the code for an automatic notification system that will send out an email when a certification is about to expire. I keep getting error messages on one or two lines of code. I have attached the code that I have so far and a dummy spreadsheet of what I am trying to accomplish. The goal is to explore the automatic email notification option for the LEOSA Expiration date (column D), the Concealed Carry Permit Expiration (column G). For column ?D? and ?G ?, when an expiration is within 90 days the box turns yellow and sends a notification email. When the expiration is within 30 days it turns RED and an email notification is distributed. At each of these milestones (90 and 30 Day) the request is to determine if an ?auto send? type process can be utilized.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Enterprise, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz, Intel64 Family 6 Model 42 Stepping 7
Processor Count: 4
RAM: 3984 Mb
Graphics Card: Intel(R) HD Graphics, 1800 Mb
Hard Drives: C: 465 GB (400 GB Free);
Motherboard: Hewlett-Packard, 1495
Antivirus: Symantec Endpoint Protection, Enabled and Updated
 

More replies
Relevance 76.26%

I'm in HR and I have a spreadsheet that incorporates staff information commencing, with each month in a new sheet. Unfortunately, department managers are forgetting to do staff reviews at 3mth, 5mth or the 6mth probation. I've entered formula to calculate these dates from the staff commencement date.
Now I need to find out if I can have some sort of Macro or VBA coding to email me a reminder to contact the managers a week prior to the the review/probation dates.

Please help! I have no idea with coding/programming etc.
 

Answer:Excel 2016 to send Outlook email reminders on various dates

Try the attached, one thing to note that you had the probation dates in the wrong place

6mth, 3mth and 5mth

so I changed it to 3\5\6

when you open the workbook the macro will run and generate an email IF any dates is below or equal to 7 and above or equal to zero. Meaning that there is a week until the review is required. This code will fail if the review date is in the past, this can be changed to tell you that a review date has been exceeded.
 

1 more replies
Relevance 76.26%

Hi I would like to get VBA/macro codes to send an automated email to the email IDS mentioned in the file when the invoice due date is less than 2 days of current date. please help me
 

Answer:Excel 2016 to send Outlook email reminders on various dates

Here's a similar thread on the forum. If you can follow the code, then you can adapt it to suit your needs.
 

1 more replies
Relevance 75.44%

Hi Everyone!

I need your help in sending automated email and text message, when the due date of a PO is a week away from the current date. The script should preferably run automatically every time the PC is running without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. E), with subject "PO (Col. A) is due on Delivery date(Col. C)", and body "Vendor (Col. D), please update your project status".

Also, the script should put a check mark on Reminder sent column (Col. G) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found most of threads using Outlook only (my default email is Mozilla thunderbird),I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,

Thanks
 

Answer:Send email reminders thro Thunderbird from Excel sheet

16 more replies
Relevance 74.62%

I have created "IF" formula in excel 2010, based on a date it will create a send due in column "E", =IF(D5=$A$2,HYPERLINK(mailto:"&$K$1&"?subject="&A5&-B5&"&body="&$C$3,"sendworks great but, I have to go thru 86 rows in column "E" and hit "Send Due" then hit send again on the email, can we automate this some how, like a macro that engadges when I open my outlook every morning

Answer:send email from excel based on

This should be in the Office forum here: http://www.computing.net/forum/offi...

2 more replies
Relevance 72.98%

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!
 

Answer:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.
 

2 more replies
Relevance 72.16%

Hi,

I have multiple Excelsheets where in I use it for day today activites & tracking.
I have attached one of the simple one so that I can know the codes for sending mails & I can do it my self for the rest of the workbooks.

There is a sheet(dash board) where in all the details get updated.
When there are any changes to the value in column F, a mail should automatically sent to me giving the detials of the row. The file will be always live in the server.

I am very poor in coding & I need someone to help me in doing this.

Thanks in advance.
Rgds
Ganesh Hassan
 

Answer:Solved: Automatically send email from Excel based on the conditions

8 more replies
Relevance 72.16%

I am working with the attached spreadsheet in Excel 2010 and am trying to figure out how to code certain parameters that will make Excel send myself, my client or other individual an email (with text in body) if certain dates have not been entered into particular cells, or if a cell has exceeded a certain number of days in a particular cell. I have attached a sample spreadsheet and have listed at the bottom 8 points in which I need an email sent, what the trigger is and what the action (email sent to) is.

I just know enough to be very dangerous with Excel but have found that there is a way to code in Excel to send emails which would greatly help my business but I just don't know that much about codes at all.

Can anyone please help me??

Thanks!!
 

Answer:Excel Coding to Send Email based on Cell Entry

Hi, welcome to the forum.

I suggest you do a find in the forum, there are many posts that gao about this and there are many answers, I'm sure there is one that will help yu and of course one of us can help you if you're still stuck
 

2 more replies
Relevance 72.16%

I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

Conditions:
1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
 

Answer:Excel Generate emails based on conditions of cell.

10 more replies
Relevance 72.16%

I'm currently trying to figure out how to send emails to specific people based off data entered into an excel spreadsheet. Some basic information is that I'm on a mac, use excel 2004, and I'm using the basic mac mail program (have a .comcast and .edu email account). Hopefully what I'm trying to achieve is pretty simple. I would like to do the following:
Daily send an automatic email reminder every morning to specific people.
This email needs to send recipient x an email that reminds them to do chore y on the current day z.
So when the current day z is today I want the email sent out in the morning.
I would like this to be done automatically but if that is really complicated some type of macro would be awesome.

So basically the excel sheet is going to have column 1 with the due-date, column 2 with chores, column 3 with names, column 4 with emails.

I know basic computer knowledge.

I would really appreciate help on this! Thanks in advance!
 

Answer:Microsoft Excel - Sending emails based off data

Try this code:
Sub xlDialogSendMailWithMessage()

'Note: This sends the ActiveWorkBook as an attachment
'using the default email program. If the workbook to be
'sent is not the active workbook, then insert code to
'open and activate it before running this...
If MsgBox("Send message now?", vbYesNo, "") = vbNo Then Exit Sub
Dim N As Long

With Application

'scroll down to the message field
For N = 1 To 5
.SendKeys "{TAB}", Wait:=True
Next

'insert text (replace 'Email text here...' with your message)
.SendKeys "Email text here...", Wait:=True

'replace [email protected]; [email protected]; with your recipients
'replace 'Insert subject here...' with your subject
.Dialogs(xlDialogSendMail).Show _
"[email protected]; [email protected];", _
"Insert subject here..."
End With
'
End Sub

You will have to look at the code and do some coding.
 

3 more replies
Relevance 71.75%

Using Macro: How to create a new sheet on a work book based on the characters on another sheet like 'Y/Yes'?

Answer:How to create a new sheet based on a char on another sheet?

We need more details.Where are these characters? How did they get there?When do you want the new sheet created? As soon as the Y or Yes is entered in a cell or as a column is scanned by the macro or something totally different?Like I said, we need more details related to what you are trying to do.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 71.34%

Hi:
Using excel 2010
I have a master list of items,product code, and price.
I would like to be able to use a separate worksheet and be able to use a drop down menu/list to select and add individually to my worksheet.
Example, there mmight be 1000 items in my master list and I might like to select/import as such 11 items into my worksheet.
Any suggestions?
 

Answer:Solved: Importing selected rows from Excel sheet one to Excel sheet two

6 more replies
Relevance 69.7%

I have a sales report that automatically updates daily. Is there a way to setup automated emails from excel to different individuals? It would save so much time and effort. Any help is appreciated!
 

Answer:How to send automated emails from Excel?

There are quite a few similar threads with sample files and more.
Check these out.
There are two I helped with, one on October 13 was asked by vasu0505 and one from June 22 placed by truec
Search for posts by these posters and you'll come across them,

With the information you have given (Excel version missing) and no data explaining it's an open guess and guessing is no option

When you open this post similar threads are displayed below so you can directly click them.
Happy hunting
 

1 more replies
Relevance 69.29%

Hi,

I have been searching the internet all day and found similar things but nothing that works.

I am good with excel but not with VBA and I have a list that I will update every day filled with rows of information that needs to be emailed to the emails i have that change every day as well as the information.
I was planning to use the mailto formula to send them but seeing as its hundreds if not sometimes thousands of emails every day i wanted something that would send it all automatically.

I figured surely if I can just click a link and hit send there would be something out there that would do it for me but I have found plenty for sending the same information to multiple emails and things that will send multi emails to the same email but nothing to send a row from a spreadsheet to the email address listed in the same row automatically to multiple different people.

Thanks in advance.

I'm writing this in a rush as i have to go so let me know if you need any more information to help me.
 

More replies
Relevance 69.29%

Hi everyone I found the forum searching for a solution to my need to send reminder emails to a number of members in my work group on upcoming deadlines. I have attached the excel file that I use to keep track of upcoming deadlines. I have the file open quite a lot so I don't think I would need to automate it's opening daily. It gets closed and opened more than once a day so the creation of emails shouldn't occur each time it is opened. It would be great if I could create a button that I could just click once a day. I assume that if I ever wanted to automate the process I could just use windows scheduler with a file set up with a button? I tried the CDO method and keep getting ""Send Using" configurations is invalid".

Help?! Thanks!

Specifics
Outlook 2007 on Exchange Server
Column C will contain email address minus @domain.com so that will be need to be concatenated on (all addressees are on the same domain).
Columns F through Q are the contain the actual due dates, so the emails should be sent exactly one week before that date.
Example output:

To: 'firstname1.lastname1' + "@domain.com"
Subject: 'project 1' + "-" + 'deadline 1'

Body:
"Just a reminder that your" + 'deadline 1 name' + "for" + 'project 1' + "is due one week from today."

Example output using cell references:

To: 'C2' + "@domain.com"
Subject: 'A2... Read more

Answer:Excel to Outlook to send deadline emails

16 more replies
Relevance 69.29%

I am trying to set up a email distribution document in Excel. I want to be able to select a "cycle" on the first worksheet and then based off that I would like the macro to send emails on the corresponding worksheet. I would also like to only send the emails to the recipients that have "Yes" in the distribute column. Currently, I have a very messy method of doing this and it would save me an incredible amount of time if I could click on one button and all of the emails that meet the criteria be sent. I have attached a copy of my document with comments to further explain what I am trying to achieve.

I have done research but I haven't found anything that really relates to my project.

Thanks to anyone that can offer any help or advice. I greatly appreciate your time and help!
 

Answer:How to send emails via Excel with multiple criteria...

See this thread for a list of Threads about emailing using VBA.

http://forums.techguy.org/software-development/836212-send-mail-automatically-value-comes.html
 

1 more replies
Relevance 69.29%

Have just updated to Office 2003, I now do not have the button to send once I click on send to email.
I am using Lotus Notes as my email, I have checked to make sutre it is default and changed the setings to reflect this. However when I send my workbook to email it shows outlook settings and address book. My question would be how do I default it to Lotus Notes?

 

Answer:I am unable to send emails from Microsoft Excel

Found out the problem, it was in the settings for internet options, under programs, email has to be put onto Lotus Notes and Contact list also put onto Lotus Notes not Outlook.
 

1 more replies
Relevance 69.29%

I got some amazing help from bomb #21 to put together this awesome sheet.

I would like to now implement it on a much larger scale. Currently it produces emails when the button is pushed (macro is run) and on each email you need to click the send button. Is there any way to automate this so it sends the mail directly without needing to click the send button? I would prefer to bypass the visible generation of emails at all vs. having a macro that presses the send button.

There will likely be 40-100 emails generated each day.

Or is there even some Outlook 2007 function that will automatically send all emails that are currently open?

Thanks for any help!
 

Answer:Excel send emails automatically without prompts

16 more replies
Relevance 68.47%

Hello friends,

Please can someone help me out in creating a macro that can save me from many troubles with my bosses . I work as a Procurement & logistics coordinator and I need Excel 2003 to send an email to the supplier or the forwarder at a specified condition. I have lots of orders pending and it's difficult to follow them up

I have attached the Excel file with a formala that displays the followup action that could be taken in "column D". For exemple if in D6 there is "Supplier", Excel will send an email to the email address in H6, and with a subject the Purchase order Reference, E6, and content of email for exemple: "Please provide us with the status of Subject PO"

Many thanks my friends

Kaki1956
 

More replies
Relevance 66.42%

Hi,

I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.

Thanks,
mihaufo
 

Answer:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

8 more replies
Relevance 66.42%

I need to copy n number of rows from a sheet in my local machine to a sheet in fileserver.
My requirement is - if I select last 2 rows in the sheet in my local machine I need the rows to be copied after the first blank cell in the sheet in fileserver.
Also it would be helpful if the macro code is only in the local sheet and not in the fileserver sheet.

I am a newbie to macros pls help....
 

More replies
Relevance 66.01%

Hi,

I need to paste data from one worksheet that is unfiltered (not hidden) onto a worksheet that is filtered. I need to paste onto visable cells only, ignoring the hidden rows. Currently, my data is being pasted onto both visible and hidden rows.

I know its possible to paste only visable data, ignoring hidden cells, but I need to do the reverse of this. Pasting non hidden data onto only visible cells. And I hope I am explaining this well.

Please do not suggest a vlookup because this would take entirely too long due to the nature of my data and this project. Also, I am not skilled enough to do code, so keep it simple...

Suggestions please?
 

Answer:Solved: Pasting in excel from unfiltered sheet to filtered sheet

6 more replies
Relevance 65.6%

hi guys...i posted regarding this file a few times...and is still coming back to haunt me!

i need some help here....especially now that the ppl at the office need the information to be drawn out differently.

ok. here's the scenario:

i have an excel file with 8 sheets

"Questionnaire" sheet is to be filled out by a rep and has some drop down lists to populate the "POtemp_NY" sheet.

ampaper, edmar, sterling, stoneglo, strauss and uneeda are all venders with lists of their products...with their prices.

"POtemp_NY" is the master sheet where all the information must show on

The problem is, i need to have the "POtemp_NY" sheet populate the description, units, and price when someone enters a value in the "Units" column on a vendors sheet. Also, i would need the "POtemp_NY" sheet to continue to list the products on the next lines if there are multiple products with values in their "Units" cell.

Any help would be GREAT!!!!

Thanks in advance!

the file is attached for reference! thanks!!!

- mark
 

Answer:Solved: excel: populate sheet from list (fr another sheet)

16 more replies
Relevance 65.6%

Hi,
I have written the below code to check the column in sheet "A" and if it matches any of the conditions, I copy it to sheet "B". But I also want to delete the whole row (not just clear its contents) from sheet "A" after copying it to sheet "B". I tried a lot of codes for that, but none worked!
Please help..

Sub a()

Sheets("A").Range("A1:J3").Copy Sheets("B").Range("A1")


Dim i As Long, iMatches1 As Long, iMatches2 As Long
iMatches1 = 3
For Each cell In Sheets("A").Range("F:F")
If (cell.Value = "x") Or (cell.Value = "y") Or (cell.Value = "z") Or (cell.Value = "u") Or (cell.Value = "v") Or (cell.Value = "w") Then
iMatches1 = (iMatches1 + 1)
Sheets("A").Rows(cell.Row).Copy Sheets("B").Rows(iMatches1)

End If
Next
 

Answer:Hw to delete a row from an excel sheet after copying its contents to another sheet

hi
after
Sheets("A").Rows(cell.Row).Copy Sheets("B").Rows(iMatches1)
you type
Sheets("A").Rows(cell.Row).Delete

i hope that i've answered you
 

3 more replies
Relevance 64.37%

Hi,

I have an application called BT Billing Analyst. This application has an option to open a report in Excel.

When I used Excel 2007 the report data opened in sheet 1 of an Excel workbook and some information on the report was put on sheet 2.

I recently upgraded to Excel 2013 and now when I open the report in Excel the report data is populated on sheet one but after that the first 25 lines get overwritten by the information that should go to sheet 2.

Is there any way I can fix this?

Thanks
Paul
 

Answer:Excel - Sheet 2 data getting written to sheet 1

You may have to go to the BT Billing vendor to resolve this. Perhaps an updated version is available. 2007 is xml based like 2013 so I am surprised there is a conflict. But I don't think there is anything in Excel you can do to resolve.
 

2 more replies
Relevance 64.37%

at work i'm looking to create a weekly stats sheet with each sheet tab representing a week. i'm looking to have 52 sheets.each week will have a carry over figure that i want to automatically populate into the next week.e.g. cell a1 from sheet 2 is copied from cell b1 in sheet 1, cell a1 from sheet 3 is copied from cell b1 in sheet 2.i can of course repeat the reference to the previous sheet manually but i was wondering if there was a quick way of setting up the sheets so that the reference to the previous sheet and specific cell is done automatically?is there also a quick way to set up all 52 sheets rather than copy 51 times?tia.

Answer:excel figure carry over from sheet to sheet

Yes it is possible using a macro. Is it just those cells that need to be copied. What are the sheet names? Do you need to copy the FORMULA or the VALUE in A2?In other words perfectly doable but more info needed.

1 more replies
Relevance 64.37%

I have a little calculator of sorts that hides rows and information based on entries in cells A1 and B1 on sheet 2. Problem is things get messed up when another user returns to this sheet and cannot see data previously entered in these 2 cells due to conditional formatting which hides needed rows. If those 2 cells would always revert to zero when saving or going to another sheet all will be great. I'm not an expert so please go easy on the lingo.What I'm doing with the spread sheet is to figure our serial numbering and labeling requirements will is based on the factory location and product qty required. Sheet one is where the serial numbers are entered by the used based on sheet 2 selections (which factory and how many widgets required.)This one still has us stumped so I'll clarify my issue. I have a 2 sheet excel file; sheet 1 users may make perm changes on but sheet two only temp changes may be made. Ideally when a user leaves sheet 2 anytime their changes (like a check box that was checked or the qty of a product desired) return to their original values (unchecked or 0.)Hopeful.  J

More replies
Relevance 64.37%

Hi,

I want to copy the original sheet into another sheet.

I have sheet1 tab.

if I use this code:
ActiveWorkbook.Sheets("Sheet1").Copy after:=ActiveWorkbook.Sheets("Sheet1")

it created sheet1(2). but I want to specify my tab name.

if I chnage it and run:
ActiveWorkbook.Sheets("Sheet1").Copy after:=ActiveWorkbook.Sheets("helloworld")

It fails.. error comes up.

How do I make it work?

Thank you
 

Answer:Solved: EXCEL: copy sheet to another sheet

Hi sk0101,

rename it after copying:-

ActiveWorkbook.Sheets("sheet1 (2)").Name = "helloworld"

lol
 

2 more replies
Relevance 62.73%

Hello,

My new problem is this:
I have calendar dates entered into a table that has records of transactions. In the reporting I need to create, the business months are different than the calendar months. For example, for July in the reports, I should do a summary based on the business month, July 5 to Aug 1.
My idea is to create another table where I would enter the start and end date for all business months, plus two other fields for the correspoding business year and business month (Business_Year, Business_Month, From_Date, To_Date; From and To are calendar dates in date format, Business Year and Business Month are numeric).
What I need is a query that would look at every record in the transaction table, then would look that calendar date up in the business months table, looking up which business year and business month it falls into, and return the value of the business month and business year for every record in the transaction table.
I was thinking about using DLookUp() function in the query that is based on the transaction table.
The best I could find on the net is something like this:
Year: IIf([Date]>[Business_Months].[From_Date] And [Date]<=[Business_Months].[To_Date],Year([Date]),"9999")
Month: DLookUp("Business_Month","Business_Months","Date > #" & [Business_Months]![From_Date] & "#") & DLookUp("Business_Month","Business_Months","Date <= #" & [Business_Months... Read more

Answer:Dlookup based on two dates

8 more replies
Relevance 61.91%

This seems to be a day of problems.
I gave a simple Oracle select query

select * from readertable where STATUSDATE='17-MAR-07';

It didn't return any records eve though I know there is a record. The reason I know this, is because when I searched with another query on the same table

select * from readertable where READERNO=17;

it showed the STATUSDATE as '17-MAR-07'!!!

Here's another interesting quirk . When I run a date query on another table it shows the result!!!

What am I to make of all this ????? Where is the prob???

Pleeeeeeeeeeease Help!

Thanks.
 

Answer:Oracle Does Not Select Based on Dates!!

I tried giving the query like

select * from <tablename> where STATUSDATE<='17-MAR-07'

and it worked ! It gave all the rows before 17-MAR-07 but at least it gave a result !
Don't ask me why , it just did .
 

1 more replies
Relevance 61.91%

I am looking to create a macro that when run goes through a sheet and adds all cells that are in the same row as a certain date and places them in a cell on another sheet. I want to macro to cycle through all the days in a month. I am doing this to calculate daily expenses and income. I am figuring that I will have to use a loop and calculate the number of rows that have data. Use this number to cycle through every row in a column for a certain date, and use an if statement to add data out of the other column to a variable and at the end of that date write variable to cell. Change data and clear variable and start the loop over again. Just not sure how to write this. Any help would be appreciated

Answer:Adding cells based on dates

Please explain your task in a little more detail.Perhaps an example of your sheet layout would help.I'm not convinced you need a macro, but even if you do, you haven't given us enough info to work with.If you use the pre tags above the comments box you can line up your data like this to make it easier to read:
A B C D E
1 2/3/2010 Lunch $10 Pay $20
2 2/4/2010 Lunch $ 7 Pay $20
3 etc.

6 more replies
Relevance 61.91%

[timestamp=1173309904] :-?I have an excel spreadsheet that has about 30 sheets. I have separate accounts on each sheet but often need to see the running balance of each individual sheet. I would like to take the formula from sheet 2, 3, 4 etc and have it show up in sheet 1. This is what I tried: I want the total amount on sheet 2 in cell E13 to show up on sheet 1 in cell D1. And whenever I make changes to the totals on sheet 2, cell E13, I would like the new total to automatically show up on sheet 1, cell D1. I went to sheet 1, cell D1 and entered =2!E13 and hit enter and another window popped up browsing my files. I'm totally lost as to how to do this.Help???

Answer:Excel help regarding formulas from sheet to sheet

The formula in cell D1 on Sheet1 should be =Sheet2!E13.  One way to enter such formulas is as follows: Click cell D1 on Sheet1 to select it.  Hit the = sign.  Now, click on the Sheet2 tab and then on cell E13 on Sheet2, and then hit Enter.

1 more replies
Relevance 61.5%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 61.5%

Hi All,I am amazed at the willingness of people to help out and I would be so grateful if someone could point me in the right direction. I am keeping my description deliberately generic as it may help others apply this solution to their own (there are many answer out there, but many are too specific to a particular data set for me to adapt them myself)...I have data with columns A to Q and in rows 1-50. (Row 1 is headers and column A is a label).I have several worksheets, called WorkSheetX (X = 1, 2, 3 etc.,)Based on the numeric value (in my case a date - specified as a numeric not a date format) in a specific column I would like to copy the corresponding row (which is a mix of text and numeric) into a new worksheet called "Summary 201X" (where X = 1, 2, 3 etc.,). For simplicity let's assume these work sheets exist and I have already copied Row 1 the header column to them Here's an abbreviated example of the data in worksheet 1, 2, 3 etc.,: A B C D .... QWorkSheetX Row1 2012 12 txt 4 xWorkSheetX Row2 2011 12 txt 4 xWorkSheetX Row3 2011 12 txt 4 xWorkSheetX Row4 2015 12 txt 4 xIn Worksheet 1, if column A = 2011 that row will be copied to "Summary 2011": A B C D .... QWorkSheet1 Row2 2011 12 txt 4 ... Read more

Answer:Copy row to new sheet based on a cell value

After much searching I was able to adjust the following macro to select and copy the entire rwo from sheet A to sheet B based on a certain cell value. Next I managed to do this for each of the discrete criteria i.e. date = 2011, 2012 and copy to corresponding sheets.Next step is to do this automatically for all work sheets, so all of the data is present in each of the date tabs.
Sub SearchForString()
Application.ScreenUpdating = False
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row x
LSearchRow = x

'Start copying data to row y in Sheet2 (row counter variable)
LCopyToRow = y

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column A = "xxxx", copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = "xxxx" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

... Read more

2 more replies
Relevance 61.5%

hey im trying to do hobby project and i have this really big problem: im trying to merge two spreadsheets with one sheet each into a new one. they should be merged based on matching ids in column A1:A1000, so the outcome would not have duplicated ids.

im assuming this is a common need but i couldn't find anything for excel 2013

please advise. link to a good video guide would be helpful and good.

Answer:~ merge two spreadsheets with one sheet each into a new one based on m

Excel 2010 has a "Remove Duplicates" in the Ribbon Data tab.
If Excel 2013 has this feature you should be able to copy the data from each sheet into a new single sheet.
Then use the "Remove Duplicates".

4 more replies
Relevance 61.5%

I need to create a macro in excel 2007 which cuts the entire row and paste into another workbook based on a set of criteria.The formula looks up sheet2 collum A:A if the attribute can be found in sheet1 A:A then copy the entire row to sheet 3, repeat this process until all you reach a blank in sheet2 A:AI hope I have provided enough infromationthanksIan

Answer:cut and paste into new sheet based cell value

How about this:
Option Explicit
Sub CopyToSheet3()
Dim lastSht2_Row, sht2_Row, nxtSht3_Row As Integer
Dim c As Range
'Determine length of data in Sheet 2 Column A
lastSht2_Row = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Sheet 2 Column A
For sht2_Row = 1 To lastSht2_Row
'Search for data from Sheet 2 Column A in Sheet 1 Column A
With Worksheets(1).Range("A:A")
Set c = .Find(Sheets(2).Cells(sht2_Row, 1), LookIn:=xlValues)
''If found, copy entire row to Sheet 3
If Not c Is Nothing Then
'Incremement Row counter for Sheet 3
nxtSht3_Row = Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy/Paste Row
Sheets(1).Cells(c.Row, 1).EntireRow.Copy _
Destination:=Sheets(3).Cells(nxtSht3_Row, 1)
End If
End With
Next

End Sub

5 more replies
Relevance 61.5%

I have two columns of dates, and I need to apply a conditional formatting rule so that;If a date in Column B falls within 0-3 months of the date in Column A (same row), it highlights GreenIf a date in Column B falls within 3-6 months of the date in Column A (same row), it highlights YellowIf a date in Column B falls within 6-12 months of the date in Column A (same row), it highlights RedI have searched on a lot of forums, but cannot find anywhere. Any help very much appreciated! Thanks :)

Answer:Conditional formatting dates based on another cell value

Try this How-To and see if it works for you:https://www.computing.net/howtos/sh...MIKEhttp://www.skeptic.com/

2 more replies
Relevance 61.5%

I am not sure if what I want to do is possable or not. I have a spread sheet with three colums, date, total, daily total. First of all the dates in will very and have multiple entries for the same day or just one depending on service. I want to make one entry in the daily total column on the last entry of the day with the total for the day. So i need to check dates in column date and if the same add the corrosponding row in time for one entry. And I am working with Excel 2003. Is this possable? If so can someone please help me!!

Answer:adding columns based on dates in another colu

Try =SUMIF()If your data looks like this:
A B
1) 9/30/2010 1.00
2) 10/1/2010 2.00
3) 10/1/2010 2.00
4) 10/3/2010 2.00
5) 10/4/2010 1.00
6) 10/1/2010 1.00
7) 10/6/2010 1.00
8) 10/7/2010 1.00
9) 10/8/2010 1.00

Then to find the total for the date 10/1/2010 use this formula:=SUMIF(A1:A9,"10/1/2010",B1:B9)You could also do it by selecting a date from your date column like this:=SUMIF(A1:A9,A6,B1:B9)MIKEhttp://www.skeptic.com/

12 more replies
Relevance 60.68%

I want to check the value in worksheet "make ready" cell K15 for values from 110 to 122 and go get data from worksheet "codes" depending on value.For example:If Worksheet "Make Ready" cell K15 = 110 then copy cells from worksheet "Codes" Cells B2:B5 to worksheet CU G6:H9.If worksheet 'Make Ready" cell K15 is not = to 110 check for 111 and so on up to 122.If worksheet "Make Ready" cell K15 = 111 then copy cells from worksheet "Codes" cells B9:B13 to worksheet "CU" G6:H10.Sounds like a complex operation but I think it can work.Send me any questions if I wasn't clear.Thank you in advance.Brian

Answer:copy information from one sheet based on valu

How familiar are you with VBA Macros?This sounds like a Worksheet_Change macro that monitors K15 and performs the Copy task based on the latest entry would work.e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
'Was change made to K15?
If Target.Address = "$K$15" Then
'Clear old data
Sheets("CU").Range("G6:G10").ClearContents
'Copy range based on value of K15
Select Case Target
Case 110
Sheets("Code").Range("B2:B5").Copy _
Destination:=Sheets("CU").Range("G6:H9")
Case 111
Sheets("Code").Range("B9:B13").Copy _
Destination:=Sheets("CU").Range("G6:H10")
Case 112
'Etc.
Case 113
'Etc
End Select
End If
End Sub

2 more replies
Relevance 60.68%

Hi,
I have a list of 150 stores of which 20 are designated "gold" which means they get better service for deliveries. In the spreadsheet that contains these stores they are already configured to have the name of the store blocked in yellow (gold). I have to manually retype these onto another sheet, is there a way of transferring this info using the colour as the argument.

Many thanks.
 

Answer:copy rows into another sheet based on colour

10 more replies
Relevance 60.68%

Hello everyone,
I have only little knowledge in excel VBA. Hence I like to have a macro with input buttons for entering start and end date that will copy the entire rows containing those dates and data to another sheet namely sheet 2.My data is in sheet1 and date column is G.it will be nice if the date format is in dd/mm/yyyy.Any help is appreciated.
Thank you
 

More replies
Relevance 60.68%

Hi I'm absolutely struggling with this:

I have an Excel file with 2 worksheets.

In Sheet1 is a drop-down list of countries (A1), and in Sheet2 is the Data.
The Data (Sheet2) has a country field in Column A.

What I want is to ONLY display data rows in Sheet2 (or possibly a new sheet), where the country matches the country selected from the drop down entry on Sheet1.

Any Ideas?

 

Answer:Return Rows in one sheet based on criteria from another

7 more replies
Relevance 60.68%

I'm building a spreadsheet to compare sports statistics where the match-ups are different every week. I'd like to have a sheet which has each team's schedule including date and "week number". Another sheet will have each team's stats. And the third sheet will show me a breakdown of the match-ups based on the current date. I'm stuck on having Excel recognize which information to pull from based on the date.

Answer:How do you show data from 1 sheet to another based on date?

Without some idea of how your data is laid out, it's a little tough to give you a specific answer.A common method for pulling data from one range to another "based on" something is VLOOKUP. If you read up on that function, you may find that it will work for you.If not, or if you can't figure it out, you'll need to post some example data so that we know what we are working with. Please click on the following line and read the instructions found via that link before posting your example data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 60.68%

Hi I have two workbooks. The first one has a range of cells with months formatted as 'mmm'. On the second work book I reference the first workbook e.g =A1 and the month appears in the second workbook Call A1 as 40633 until I re-format that cell as a month. The tab name refelcts the same name (40633) but will not make any changes once I haver reformatted cell A1. I ahve tried formatting the cell forst before linking the two celss but it does not help at all.Is there any advice you can offer me?I am using the following code.Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)On Error Resume NextMe.Name = Me.Range("a1").ValueIf Err.Number <> 0 ThenMsgBox "cannot rename sheet to this name"Err.ClearEnd IfOn Error GoTo 0End SubMany thanks

Answer:Change name of sheet based on cell formula...

The formatting of a cell is not considered a "change" by VBA.Try something like:Me.Name = Month(Range("A1")) & "-" & Day(Range("A1")) & "-" & Year(Range("A1"))Edit:(Not tested)Tested briefly, seems to work.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

12 more replies
Relevance 60.68%

Hi all,I have some code below to create new sheets from a template and name them accroding to a cell range which is specified in the code, perhaps it is because I am asking it to do too many things at once, but the code is creating one sheet based on the template specified, and then just renaming it to the name range, rather than creating a new sheet for each one.Any help would be appreciated.Sub Sheetcreationbycellvalue() Dim Sh As Worksheet Dim shName As String Dim MyCell As Range, MyRange As Range Set MyRange = Sheets("Sheet1").Range("A1:A201") Set MyRange = Range(MyRange, MyRange.End(xlDown)) 'name of the sheet template shName = "WHSCT.xltm" 'Insert sheet template With ThisWorkbook Set Sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _ Before:=.Sheets(.Sheets.Count)) End With On Error Resume Next For Each MyCell In MyRange Sh.Name = MyCell.Value If Err.Number > 0 Then MsgBox "Change the name of Sheet : " & Sh.Name & " manually" Err.Clear End If On Error GoTo 0 Next MyCellEnd Sub

Answer:creating and naming sheet based on template

Hi,Your code starts by setting some ranges then it creates one worksheet with this line:Set Sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
Before:=.Sheets(.Sheets.Count))The code then loops through the cells containing names, applying the names in turn to the same worksheet, here:For Each MyCell In MyRange
Sh.Name = MyCell.Value
...
next MyCellYou need the code to create the worksheet inside the For Each MyCell .... Next MyCell loop.Regards

4 more replies
Relevance 60.68%

Hi all,
I want to generate dates automatically in Access 97 for a contact database. For instance, if the last contact was made on day a, I want to generate in the "next call" column day a + 3 months/90 days (call that day b). If a contact was made before day b, I want the column to generate a new next contact date equal to b + 3 months/90 days. (As for the 3 months/90 days, I can go with either).
Finally, I would like the system to send an e-mail notice, if possible, telling the person here at work that person x is to be contacted on this date. (We don't use Outlook, or I suspect some of this could be done using that).

Thanks for any advice in advance.
 

Answer:Auto-generate new dates based on other data in Access

Use DateDiff function to determine the number of days compared to the current date. If it is >= 90 you can call an email function or module.
 

3 more replies
Relevance 60.27%

I have an excel file that has a sheet with pipeline information on loans (sheet name is "Pipeline"); there is a unique identifier on column D (loan number) in that sheet. In that same excel file but a different sheet called "Projections" that same unique identifier (loan number) is on column "D", there are 4 other columns with data that I would like to copy over to the Pipeline sheet from the Projections sheet based on the loan number match.I would like the macro to match the loan # on column D (range is D2:D600) from Pipeline sheet to column D in Projection sheet (same range) and if found I would like to copy the contents of columns I, J, K and L from the Projections sheet to the same columns in the Pipeline sheet for each loan number that matches.I honestly don't know where to start, I usually use the macro recorder and tweak from there but the macro recorder doesn't really help you create the logic, can anyone help me?

Answer:Macro to copy data from one sheet to another based on match

Why don't you just use VLOOKUP?The basic formula for Pipeline!I2 would be as follows. Drag this down as far as required.=VLOOKUP($D2,Projections!$D$2:$L$600,6,0)For Pipeline!J2 use =VLOOKUP($D2,Projections!$D$3:$L$600,7,0)To expand on this a bit, you could put this in Pipeline!I2 and drag it both down and across to Column L.=VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0)Finally, to eliminate the #N/A error that will occur if a value isn't found on the Projections sheet, put this in Pipeline!I2 and drag it down and across.=IFNA(VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0),"Not Found")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

5 more replies
Relevance 59.45%

I'd like the macro to perform an automatic transfer of multiple cell data to a different multiple cells in a different sheet as new info is entered daily. If in Sheet 'JAN' C32:C90 is equal to "Cash Transaction," transfer the cell data from Sheet 'JAN' (for example) A40, B40, E40, and F40 over to Sheet 'Cash Acct' A9, B9, C9, D9. I need data transferred to the next blank/available row in Sheet 'Cash Account'. The available lines in Sheet 'Cash Acct' begin at line 9 and end at line 145.
Why won't this code work when I paste it into the 'JAN' VB Edit Window?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
For Each Cell In Sheets("JAN").Range("C32:C90")
If Cell.Value <> "Cash Transaction" Then Exit Sub
Next Cell
LastRow = Sheets("Cash Acct") _
.Range("A145").End(xlUp).Row + 1
If LastRow < 9 Then LastRow = 9
If LastRow > 145 Then
x = MsgBox("Can't copy beyond Row 145.")
Exit Sub
End If
Sheets("Cash Acct").Cells(LastRow, 1).Value _
= Sheets("JAN").Range("A9").Value
Sheets("Cash Acct").Cells(LastRow, 2).Value _
= Sheets("JAN").Range("B9").Value
Sheets("Cash Acct").Cells(LastRow, 3).Value _
= Sheets("JAN").Range("E9").Value
Sheets("Cash Acct").Cells(LastRow, 4).Value _
= Sheets("JAN").Range... Read more

Answer:Macro auto transfer data to different sheet based on condition

Hi and welcome to TSG Forums!

What do you mean by "won't work"? Not doing anything at all? Or doing something but not what is expected?
Anyway, my suggestions to you are:

1. Run this code:

Code:
Sub test()
MsgBox Application.EnableEvents
End Sub

If it returns False, then all Event Handler subroutines are disabled, so the code you wrote is not executed at all. Default value of EnableEvents is True, and it should reset at each restart of Excel.

2. Place a breakpoint at this code line:

Code:
For Each Cell In Sheets("JAN").Range("C32:C90")
Placing a breakpoint: set the cursor on the desired lint then press F9.
When the code is executed for the next time, it will stop at the breakpoint. Hit F8 repeatedly to maintain a step-by-step code execution. This way you'll see what is happening really.

3. Declare all your variables.

Code:
Dim LastRow As Long, Cell As Range, x As Long
4. Variable "x" is, actually, not needed.

Code:
use
MsgBox "Can't copy beyond Row 145."
instead of
x = MsgBox("Can't copy beyond Row 145.")

A simple rule which might have confused you: When calling a function as if it was a procedure, you don't use parentheses. E.g.
myFunction Argument1, Argument2, Argument3
vs.
Variable = myFunction(Argument1, Argument2, Argument3)

5. In the code module of Sheet 'JAN' you don't need to write it out explicitly, you can refer to it a Me, or leave reference altogether. ... Read more

2 more replies
Relevance 59.45%

Hi,
I am trying to calculate Total Cost of Labor with fees and rates that change depending on when(date range) they are charged. I have gotten it to work, but I would like it to be more efficient.
Currently I have it set up like this:

tbl_Employee Base Labor Rates_date changes
fields:
Employee Name
Base LR_SD
Base LR_ED
Base LR
Base LR_SD1
Base LR_ED1
Base LR1
Base LR_SD2
Base LR_ED2
Base LR2
Base LR_SD3
Base LR_ED3
Base LR3
Base LR_SD4
Base LR_ED4
Base LR4
Base LR_SD5
Base LR_ED5
Base LR5
Base LR_SD6
Base LR_ED6
Base LR6
Base LR_SD7
Base LR_ED7
Base LR7
Note: LR= Labor Rate, SD= Start Date, ED= End Date
tbl_Hours Charged
Employee Name
DO #
Work Week Ending
Hours Charged

SQL
SELECT
[qry_Labor Hrs Charged Query].[Employee Name],
[qry_Labor Hrs Charged Query].[Labor
Category], [qry_Labor Hrs Charged Query].[DO #],
[qry_Labor Hrs Charged Query].[Work Week Ending],
[qry_Labor Hrs Charged Query].[Hours Charged],
IIf([Work Week Ending] Between [Base LR_SD] And [BaseLR_ED],[hours charged]*[Base LR],
IIf([Work Week Ending] Between [Base LR_SD1] And [Base LR_ED1],[hours charged]*[Base LR1],
IIf([Work Week Ending] Between [Base LR_SD2] And [Base LR_ED2],[hours charged]*[Base LR2],
IIf([Work Week Ending] Between [Base LR_SD3] And [Base LR_ED3],[hours charged]*[Base LR3],
IIf([Work Week Ending] Between [Base LR_SD4] And [Base LR_ED4],[hours charged]*[Base LR4],
IIf([Work Week Ending] Between [Base LR_SD5] And [Base LR_ED5],[hours charged]*[Base LR5],
IIf([Work Week Ending] B... Read more

Answer:Win XP, Access 2007,financial rates based on changing dates for employees

15 more replies
Relevance 59.45%

I'm looking for something as easy as Google workflowy or Microsoft WORD outline mode and includes time and date features such as starting dates and clock.
 

More replies
Relevance 59.04%

Hi all,I have an excel document with 14 sheets. (Twelve individual months, unknown payments, duplicate payments). They all make use of the same headings (A4:L4). Column K is a drop down menu.I am looking to copy a row from the month sheets to either the unknown payments or duplicate payments sheets based on the selection from column K (either Unknown or Duplicate respectively).I don't have any experience using VBA. Is anyone able to help?

Answer:Copy Rows from Month Sheets to Main Sheet Based on Criteria

When you say "Column K is a drop down menu" do you mean that each cell in Column K contains a drop down? (A "column" can't be a drop down menu but a individual cell can contain one.)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 59.04%

cant send reply to all when received an email from anyone else?
please how can i fix this problem

More replies
Relevance 58.63%

Hi all i apologize for my not understandingof VBA and repeatedly asking for working code even tho it has been explained many times before.
It somehow just dont work for me when ever i try to edit a code i mess something and it doesn't work
I tried google doc. add reminder and its great help ,but unreliable somehow and most of all i dont like posting sensitive business info further more since i have a lot of new info added i have to constantly re-upload and reassign reminders.
My job is a middle man between two parties with whom i have to follow contracts on both sides
one is 35 days before second reminder should be 31 day before actual date .

So can someone please post me code where
1 first reminder is sent 35 days before due date based on value in column C
2 second reminder 31 days before due date(column D)

emails should be sent only to one email adres

subject should be reference number (column A) from the same row as date
and body should be entire row containing date in question.

i would much appreciate help with this
,losing track of a single contract is huge penalty and things just pile up

At home i use libre office and at work Microsoft latest office, so since im writing this from home file is saved as xls office 2003 i hope this doesn't make things more complicated.

Thank you all in advance for help

Peka
 

More replies
Relevance 58.22%

Hi

Does anyone know how to change the password for a Excel 2010 spreadsheet?

We have one, we know the password, but need to change it as many people that shouldn't know it, does. But can't figure out how

We can't use Save As, as the spreadsheet is linked to other tables on the network. Just want to change the password that we already know, to a new one.

Looked here already, they just mention Save As:

https://support.office.com/en-US/ar...ions-EF163677-3195-40BA-885A-D50FA2BB6B68#bm4

https://askdrexel.drexel.edu/app/an...:-change-password-of-a-workbook-in-excel-2007

We're used to Office 2003, so this is a big jump at work for us

Thanks

eddie
 

Answer:How to change Excel 2010 password (when in the excel sheet)

We can't use Save As, as the spreadsheet is linked to other tables on the network.Click to expand...

Why not? You can save it with the exact-same filename, even with the Save As command. It's only from here you can choose the Tools drop-down, General Options, and then change the password from there.
 

1 more replies
Relevance 58.22%

Im wondering if it is possible to do this before i start trying...

I recieve an email containing an excel sheet containing several peices of info such as:
Name
Surname
ID
Etc Etc... Now when i open this.. i need the information posted into specified columns in a new excel document where it will have
NAME | SURNAME | ID |
-------------------------------------------
STEVE NORTH 01874
Any help given appreciated.
 

Answer:Excel - Auto export data into specified excel sheet

6 more replies
Relevance 57.81%
Relevance 57.4%

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Answer:converting excel dates in Excel 2003

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

7 more replies
Relevance 57.4%

I friend reported to me that he received an email from my email address with lots of images on it. I did not send this to him. how can I find if I have a trojan.
 

More replies
Relevance 57.4%

MS Office Outlook sending emails but no emails to send? I have added a few new accounts to my outlook and have had nothing but problems since. It is important that I keep these emails in my outlook, because I work from home and need to access these accounts. I have recently noticed, whenever I hit send receive, the number rises everyday, which now has me concerned. Each account has a different amount "sending", but nothing is in the outbox. IE: account A: sending 1 of 135, Account B: sending 1 of 1020 and so on. I also read online somewhere that if I was a torrent user, that "is" my problem, but I have stopped using it for weeks and there is no traffic going in or out, and I've turned the program off completely.

I've used Outlook for years, with many accounts and have never had problems. I've noticed more problems since this 'sending' message has started. I also noticed, I was the ONLY one at work not getting any SPAM for at least 2 yrs, UNTIL, I took a few days off, went back to work and BAM, hit with SPAM. Its a bloody nuisance because my accounts are linked with "rules and alerts", so if one is infected, all others follow behind! Has one or all of my accounts been compromised? If so, can it be fixed?

Klinker
CC
 

Answer:MS Office Outlook sending emails but no emails to send?

6 more replies
Relevance 57.4%

I am running Vista on a Lenovo laptop. I have a ymail account. The date on my computer is correct. When I send emails from ymail, the dates are incorrect, sometimes they are even in the future! How is this possible?

Thanks!
 

More replies
Relevance 56.99%

Hi,

I have a problem in merging two worksheets in excel as the two sheets have different no of rows in it. and i need to merge the two sheets in a new worksheet, and in that work sheet i need to remove the duplicate values present in the both work sheets.

anybody can help me to do that.
 

Answer:Need to Merge two excel sheets in one excel sheet.

Hi,

What version of Excel are you using. If it's 2007 then I think you can copy the data to another worksheet and select the range then use the "Remove Duplicate" option that you find it the "Data" tab.
 

1 more replies