Computer Support Forum

Excel 2007 Macro to Send Reminder Email

Question: Excel 2007 Macro to Send Reminder Email

Hi:
I am very new to Excel 2007 and macros. I have a spreadsheet that I am trying to get to send an email reminder to the point of contact [ col b ] 5 days prior to the closure document due NLT date [ col m ]. I am looking for assistance in writing a macro which will accomplish this if it is possible. I have attached the spreadsheet that I referenced.
Your assistance would be greatly appreciated.
Thanks in advance.

desantisj

Relevance 100%
Preferred Solution: Excel 2007 Macro to Send Reminder Email

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.)

Answer: Excel 2007 Macro to Send Reminder Email

desantisj, welcome to the Forum.
There are already 3 or 4 posts on this forum that have the VBA code (Macro) that you can modify for your Workbook if you can read the code. Zack has written the code so it is a bit complicated, but it should be a case of substituting your Cell references that hold the data for the ones that others have used.
Otherwise it is a case of waiting for an Excel guru to come along and help. If none of them come along I can probably help you, but I normally work with Access.

2 more replies
Relevance 101.68%

I would like to ask for an Excel macro which can send an automate email on a certain date every Month.

The dates would be as follows:

1st Monday: Email should be sent to [email protected] automatically
2nd Wednesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically

Subject line: ABC123D5679_Data transfer expected today

Details of the body of the text message:

"Dear All,
This is to remind you that we are expecting the data transfer to be posted in Novartis system today . This is just an advanced intimation.

Kindly let us know if you foresee a delay in the data transfer.

Kind regards,
Rohit"
Many thanks in advance for your help guys. This means a lot.
 

More replies
Relevance 99.22%

This is my first time posting on here so I hope this is the right place.

I have attached a spreadsheet I will need to populate and we would like to send staff members an email reminder before they need to do their task. Maybe a day or the morning of the day is fine, as long as they get the reminder. I was just wondering how I would go about doing that?

As the Excel file would need to be opened in order to work , I was also wondering how I would be able to set it to open on the start up of everyones machine. Even if it can only start up the programme then it will be obvious to people what they need to open.

Could the email or subject include as much info as it can. Like name, company, job title and contact number. and for it to be sent to the Asignee.

We will then change the next contact date once completed.

Any help would be appreciated!

Thanks
 

More replies
Relevance 97.99%

hi !
I have a spread sheet of 100 of employees , i like every time the expiry date come for there id a notification email come to me , i attach the example excel sheet please help me with that, i am just learning VBA not very good in it i am using windows 8
 

More replies
Relevance 97.17%

Hello Friends,I am leading the finance team. I need to create an excel worksheet which tracks all my invoices raised on different clients alongwith the due dates. I want excel to send an auto email to client after 2 days of due date and second reminder after 7 days or so.I am from finance back ground and thus do not have any idea of running any codes or macros.Can any body help me with this on priority basis?Thanks and regards,Manish

Answer:Excel worksheet to send auto email reminder to clients

Try here:http://www.rondebruin.nl/sendmail.htmLook under the section: Add-ins and Worksheet TemplatesMIKEhttp://www.skeptic.com/

2 more replies
Relevance 92.25%

Hi there,

I have a workbook which i would ideally like to send an automated mail when the date is within 30 days of "Todays date" .
I have found something similaar on past posts whichprints certain cells to an email but is triggered by a button press not date, but wondered if anyone could adjust it for me as my excel knowledge is very limited.
I really am struggling.

The password for the spreadsheet is Kalibratedbyme (capital K)

Best regards and many thanks!
 

Answer:macro to allow a date to send an email in excel

The content is different but why are you duplicating a post?
 

3 more replies
Relevance 91.02%

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 88.56%

Hi there - Using MS Access 2007.
I have a FORM with a macro that runs a query to generate ONE excel file and attaches to email using MS Out look to send out. - That Works great.

Problem: Multiple users to send email & attachment.
I would like for the macro to generate excel file (S) - based by the UID.

Can someone suggest a better way of handling this that is not time consuming?

Thanks In advance!
 

Answer:MS Access 2007 - Macro > Excel > Auto Email

Access can open Outlook and send an attachment to mutiple recipients either as a list or as individual emails.
So are the attachments all the same for the recipients?
 

1 more replies
Relevance 84.46%

Hi, I am hoping someone can help me with a macro for Excel 2007. I wish to "reverse" the following macro, so that it sends the contents of an entire column (in this case the second column) back to the previous column as Cell Comments, without interfering with the contents of the cells.

Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i
End Sub ​
 

More replies
Relevance 82%

Hi

This is a tuffy........

I want a macro so that when i type a message into a cell it will put it into outlook as a reminder, or even a simple macro button that opens outlook in the scheduler.

Any ideas?

Thanks
 

Answer:Excel macro links to Outlook Reminder

Try this link.
 

1 more replies
Relevance 79.54%

Using Excel: I need to send one email reminder to participants three months after they sign up.
This is only done once.
I need to mark a field "sent" after they have been sent this reminder.
The next time I check for people needing a reminder those marked "sent" will not be sent another reminder, only those who have not been marked "sent".
Thanks for the help.
 

Answer:Solved: Excel send reminder and mark it was sent

Hi, welcome to the forum:
Code:

Option Explicit

Sub SendUpdate()
Call MailData("subject string", "status file updated", "[email protected]")
End Sub

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = mSubject
.to = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
.Save ' This property is used when you want to saves mail to the Concept folder
' .Display ' This property is used when you want to display before sending
' .Send ' This property is used if you want to send without verification
' .Attachments.Add (Filename) ' Must be complete path'and filename if you require an attachment to be included
End With
Set app = Nothing
Set Itm = Nothing
End Function

You will of course have to add your macro to send the mail and then enter a true or false in the sent column

A handy link to use ... http://www.rondebruin.nl/sendmail.htm
 

3 more replies
Relevance 79.54%

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 78.31%

Hello,

I was able to successfully apply Hans' module and have the spreadsheet send an email when it was opened if something was out of the calibration date.

My question is now if it is possible to have it send 2 weeks in advance, and then again in 1 week later. I know that because the cell will be occupied that it was initially sent, it will not read it again for another send. Is there a way to automatically clear a column so that it just sends an email every time its opened within that range?

Finally, where do I add the column for a CC email?
Code:
Option Explicit

Public Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim EBody As String
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set ws = Sheets("Master Equipment LIST")
Sheets(1).Select
lRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "K").End(xlUp).Row)
If ws.Cells(lRow, "K").Value = "" Then Exit Sub
For i = 2 To lRow
toDate = Replace(Cells(i, "K"), ".", "/")
Debug.Print WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value)
If WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value) <= 14 And Len(Trim(ws.Cells(i, "M").Value)) = 0 Then
' If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 30 Then
t... Read more

More replies
Relevance 77.08%

Excel 2007 Send email "message has not been Sent"
I'm having a major problem with excel sending an attachment as and email.
I go to send email and it opens up a new message with the worksheet attached. Above the send button it says "this message has not been sent."
Next I select the person it is going to...and hit send.
The email box never closes and the message still says, "this message has not been sent."
But the email goes through to the person. The issue is that you really don't know if it sent because the box does not close or say it has sent.
Any ideas on how to fix this?
Thanks
 

More replies
Relevance 77.08%

I'm having a major problem with excel sending an attachment as and email.
I go to send email and it opens up a new message with the worksheet attached. Above the send button it says "this message has not been sent."
Next I select the person it is going to...and hit send.
The email box never closes and the message still says, "this message has not been sent."
But the email goes through to the person. The issue is that you really don't know if it sent because the box does not close or say it has sent.
Any ideas on how to fix this?
Thanks
 

More replies
Relevance 75.85%

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

Answer:Access 2007 VB code to run Excel 2007 macro in active sheet

7 more replies
Relevance 75.85%

I have searched and read the topics on this, however I am apparently below beginner and need someone to walk me through the entire process. I need an email sent to myself and 2 other co-workers one day before an item listed in my spreadsheet expires. I have the expiration dates are listed in column F. I have not entered any email addresses yet.I would like, as I have read in previous threads, for the system to open and check itself daily. Any help is greatly appreciated, and I will need a very basic, step by step hand holding description.
 

Answer:Excel Email Reminder

Hi, welcome to the board.

I suggest the following link, there enough information and samples there too.

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

2 more replies
Relevance 75.85%

I have a word document that includes a form the user must fill in. I want to create a button that will do all of the following:
1. Update all fields
2. Attach the updated word document to an email
3. Enter an address I have stored in the macro into the To field of the email.

I have one module which does the first part:
Sub CommandButton1_Click()
Dim oStory As Range
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
End Sub

I found that if I add this to the before End Sub it attaches the updated Word document to an email:
Application.Options.SendMailAttach = True 'Or False
ActiveDocument.SendMail

However, I can't figure out how have it put a specific address into the To field of the document.

So I tried another tact. I have the email address as a hyperlink in the document, and set a bookmark at the hyperlink. Then I created a module that will go to the bookmark and open the hyperlink. However, this will not add the document as an attachment:

Sub Open_Hyperlink()
Selection.GoTo What:=wdGoToBookmark, Name:="hyperlink"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With

Selection.Range.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

So then I found this bit of coding on the web, and tried imp... Read more

Answer:Word Macro to attach doc to email, then send address email

I found a solution, but I think it is a bit bulky. It seems to work but I'd love to know if there is a simpler solution:
Sub CommandButton1_Click()
Dim oStory As Range
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing

ActiveDocument.Save

MsgBox "Thank you. Your order has been sent", vbOKOnly, "Thank you. Your order has been sent"
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
On Error Resume Next
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End If
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then 'Outlook isn't running
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.To = "[email protected]"
.Subject = "Promo Order" '
.Body = "Thank you for your order"
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
.Send
'**********************************

End With
If bStarted Then 'If the macro started Outlook, stop it again.
oOutlookApp.Quit
End If
Set oItem = Nothing
Set oOutlookApp = Nothing
End Sub
 

1 more replies
Relevance 75.03%

I have a list of task (maintainance schedule) to do... and they are required to be done in a by certain different dates regularly (eg. every 14 days or every 28 days)

I have a specific maintance date for each task within the same row. Is there anyway i can make it to automatically send an email to remind me or another person a day before it's due??

thanks
 

Answer:Set email reminder base on Excel

16 more replies
Relevance 75.03%

If I am doing an automatic email reminder when something is past due:

do I always have to open the Excel document for the macro to run and the reminder to sent out?

Thanks,
 

More replies
Relevance 75.03%

hi there,

I have a list of memberships that need to be renewed at different dates (eg. every two or three years depending on type of membership)

Is there anyway i can make it to automatically send an email to remind me or another person the day it's due to renew?

The list is on excel with expiration date in one of the columns
thanks
 

Answer:Set email reminder based on excel

Hi, take a look at some of the posts, there are quiet a few that do just that, they may need a little editting for your purpose but...
Here's a simple module that does just that, all you need to do is wriet a amcro that fills in the blanks

Code:

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, Sendto As String, CCto As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Function


P.S. You forgot to mention the Excel versio you're using
 

1 more replies
Relevance 75.03%

I have a real simple need. Well, it's probably simple for some. I have a sheet built to track employee hire dates and eval dates. I would like to assign excel to open everyday at a time and then evaluate the cells with the eval and raise dates to see if an email needs to be sent to the supervisor. If so a simple "there are employee items coming due" email is sent to the supervisor. The super will go to the document and take care of the rest. Dta is not real. The email with the employee name isn't the one I'll be sending to. The Team Manager at the end is where the email will be going to for all on the sheet. I would appreciate the help. Running windows 7 and office 2013.
 

More replies
Relevance 75.03%

Hello Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is nearing 3 hours, 2 hours & 1 hour away from the current date & time. The script should preferably run automatically every time the PC is running when the excel file is open.

In the attached excel file, An email should go of to -email address (Col. F), with subject "number (Col. B) is due on Due date and time (Col. E)", and body "Dear Name(Col. F), please finish your project asap".

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 a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Reminder email from Excel

Hi, I suggest you do some searching, there are quiet a few posts with the same questions and the necessary solutions.
I worked on one or two but cannot remember the names, so just do a quick search and you'll find the answres with the necessary code.
Always to glad to help on if you're still stuck
 

1 more replies
Relevance 75.03%

Hi guys,
I have a list of some certifications and their respective expiry dates written in Excel sheet. What I want is to be getting Auto reminder few days before, on and after the expiration date.
Attached is the VBA program that I tired to use but it was reading the whole data instead of being selective. Also attached is an extract of the excel sheet.
Pls I will appreciate any help
Thanks,
CJ
 

Answer:Auto Email reminder from Excel

Have you read the Thread under yours -
http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
 

1 more replies
Relevance 75.03%

Hi,
I'm kinda new to VBA and excel programming and am looking for a way of sending an automated reminder 60 days before the expiry of training already completed. column G contains the email recipients, Column E has a title Asbestos Awareness and the cells in column E the expiry dates for employees.
I would appreciate if anyone has sample code I could try.


Thanks sooooooooooo much for any help or advice.
Regards
Vinny
 

Answer:Automatic Email reminder in excel

Hi Vinny,
welcome to the forum, you're the second one this week with the same question, and just like the other poster I do not think you took the time to search for similar posts.
I suggest you do hat, there are several posts with the similar questions and also with the answres and code provided.
 

1 more replies
Relevance 75.03%

Hello there,

First time posting and very average excel capabilities... Working on generating a code that will send automatic email reminders 7 days prior to the date within rows F-P. Recipients contact information is in column S.

Thanks for your time.

Tim
 

More replies
Relevance 74.62%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 74.62%

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 74.21%

hi there,

I have a list of memberships that need to be renewed at different dates (eg. every two or three years depending on type of membership)

Is there anyway i can make it to automatically send an email to remind me or another person the day it's due to renew?

The list is on excel with expiration date in one of the columns
thanks
 

Answer:Solved: Set email reminder base on Excel

you have another thread here
http://forums.techguy.org/business-applications/1055728-set-email-reminder-based-excel.html
please do not duplicate posts - closing this one
 

1 more replies
Relevance 74.21%

I have a list of jobs (jobs schedule) to do... and they are required to be done in a by certain different dates regularly.

I have a specific jobs date for each task held in a colu. Is there anyway i can make it to automatically send an email to remind me or another person a day before it's due??

Another question - I have a basic dashboard when a job is overdue I want the cell to flash RED

thanks
 

Answer:Set email reminder based on conditions on Excel

10 more replies
Relevance 74.21%

Hi Guys,

I have just joined this forum, I found some solutions given by Zack quite useful to me. Thank you so much.

But also need help in taking it further, as I have created a worksheet with lots of data which will have to be incorporated while sending auto email reminders to various admin officers depending on the dates, especially for Statutary payments like Telephone, Electricity etc.

If any body can help....... Please...............
 

Answer:Auto Email reminder from Excel Sheet

http://forums.techguy.org/business-applications/773873-how-make-excel-send-email.html
 

1 more replies
Relevance 74.21%

Hi everyone,

I have no prior experience with Macro/VBA and would need help in creating an excel spreadsheet where an email reminder would be sent 60 days, 30 days and 7 days respectively if the project is not completed to the email stated in the 6th column with information from its row. If its completed, the checkbox would be checked and no mail will be sent.

The email should look like:

To: <email stated in that row>
Subject: Reminder to complete project within (e.g. 60 days)

Body:
Dear all,
Reminder to complete IN1661234 by 31st Aug 2016.

<personal email signature>

Do I need to create a button where I have to click in order for those reminders to be sent or will it be sent automatically everyday as long as the spreadsheet is opened?

Also, is it possible to create 3 different email template for each reminder days, such as 60 days prior to the deadline, the subject of the email would be First Reminder, 30days would have Second Reminder and 7 days prior would have FINAL REMINDER and a urgent marked on the email?

And how do I change the email where those reminders will be sent from?

Any help with regards to the above would be greatly appreciated. Thank you so much.
 

Answer:Auto Email Reminder from Excel when project is almost due

16 more replies
Relevance 73.8%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 73.8%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 73.39%

Hello all,I have used Excel for years, and run macros, but never written one myself. I have a spreadsheet of archived items that I need to review on a weekly basis to ensure boxes get sent back in a timely manner.The relevant columns are:Box number (an 8 digit number)Date recalledRecalled by (this will be a name)Date sent back (date - if not sent back, this will be empty)Review date (this will have a date 9 working days after the date the box was recalled)My ideal scenario is that I open the s/sheet each week, run my macro which will:Check the Date sent back box, if it is populated - nothing happensIf it is empty, the macro then checks the Review date box and if the present date is beyond the review date - the macro forms and email to be sent to the name in the Recalled by box stating: Subject: Box number: (it gets this from the Box number column)Please note this box was recalled on DD/MM/YY and is still in the office.Please advise if this can now be sent back - if not please respond accordingly.This is a big ask, I realise this - and I don't necessarily want someone to do this for me - I ideally want to understand how I do this, so that I can do it going forward myself.Any help would be greatly appreciated!ThanksEmily

Answer:Need Macro to Send email

I'm not an expert on macros by any means, but the few times I've done basic ones it's been okay after a bit of trial & error. There are a couple of good websites that give mini tutorials on how to create basic macros, these may be enough for your needs. Put 'writing excel macros' into Google & look at the first couple of hits. "I've always been mad, I know I've been mad, like the most of us..."

3 more replies
Relevance 73.39%

Hi lovely people!

I hope you can help me out - I'm new to macros. In regards to the attached excel file, I need to achieve 3 main goals:
Automated pop-up message - For each phone that is overdue for delivery (ie current date is more than due date), I need a pop-up message upon opening the excel sheet. The pop-up alert should say which phone is due (stock #), and how many days overdue it is (current date minus due date)
Automated email reminders - emails to send to various email accounts (individuals in my team) to remind them of overdue phones. The email should include details like the phone, stock #, supplier, due date and how many days overdue. I want the first email reminder to be sent on the due date itself, then 2nd reminder on the next day, and subsequent reminders every 3 days.
Have a separate sheet listing all the overdue items that need to be followed-up.

Lastly, but is it at all possible to create two versions of the same stock list? I.e. one sheet in English, and the other sheet in French. And any edits in either sheet will update the other version.

Cheers!
 

Answer:Excel help! Automated pop-up box and email reminders, and reminder list

Hi, welcome to the forum.
Have you doen a search in this forum.
There are many posts that relate to triggering a mail message based on calculations and expiry dates, etc.
You will of course need to edit some solutions for your purpose, but I do suggest you take a look (search) and probably you'll find what you need.
The question about the two languages is custom macro coding and will take some time.
Your sample contains onle one row of data, but nothing more to go on.
Do some homework (searching the forum) and someone will be able to help
Please don't forget to mention the Excel version, as far as I cansee it's at least version 2007 but it could be 2010 or newer
 

1 more replies
Relevance 73.39%

Hello Everyone!

This is my first post here. I am using Excel 2013 OS win 7. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is nearing 3 hours, 2 hours & 1 hour away from the current date & time. The script should preferably run automatically every time the PC is running when the excel file is open.

In the attached excel file, An email should go of to -email address (Col. F), with subject "number (Col. B) is due on Due date and time (Col. E)", and body "Dear Name(Col. F), please finish your project asap".

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 a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Reminder email from Excel 2013 in Windows 7 pc

closing duplicated post
continue here
http://forums.techguy.org/business-applications/1141421-automatic-reminder-email-excel.html
 

1 more replies
Relevance 72.57%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-applications/710581-solved-automatic-email-alerts-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

Answer:Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread: http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
Thank you.

Closing thread.
 

1 more replies
Relevance 72.16%

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

More replies
Relevance 72.16%

Excel '07 won't open an "excel 4.0" macro (i.e. *.xlm file). I am one of a few users at work using excel 2007, I believe this file was made with excel 2000 or similar. When I had excel 2000 it opened fine, it's a common file on the network that everyone should be able to access, and everyone with excel 2000 can easily. So I know it's not the file. When I try and open it with excel 2007 I get that stupid error about the file being different to the extension.

I've tried registry edits and workarounds after scowering the net but nothing seems to fix it. All it does is display the error, then opens excel in the background without even a new worksheet. Excel isn't hanging, it just does nothing. Any ideas?
 

Answer:excel 2007 won't open excel 4.0 macro

XLM was long ago replaced by VBA (with Excel 5 in 1993) and MS no longer really supports it (although I think they claim that they do, kinda, sorta.) So the only thing you can do, I think, is to make a copy of the xlm file and convert the xlm code in the copied file to VBA. Fortunately, I found a link that tells you how to do that:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
(Look at the link at the bottom, too. But you will need to download that to an older PC, possibly.)
Now, assuming all goes well, this will give you two versions of the same file, but if everyone else has Excel 2000 (or later) then they will all be able to use the new VBA-based file, so you won't end up with two files that would lead to confusion.
Still, it looks to be a lot of work, so good luck!

On a final note, if you need this file done in VBA and you can't figure out how to redo it, make a post describing your needs - there are some amazing VBA coders on here.
 

1 more replies
Relevance 71.75%

I am using the following code to email word document (its actually a form) as attachment and to save it to my "P:/" drive. It seems working but the code will need to modify for the following reasons:
Once I am done filling out the form and hit the command button to run the macro? the document (form) actually sent to email and also saved to my "P:/" drive successfully but its over saved the master file. I need the form to be blank at all time.
Also, I want to have the files to be stamp with date and time (as a file name for the form) that going to be email and save to "p:/" drive.
I will appreciate any help. Thanks!!

The code I am using is:

Private Sub CommandButton1_Click()

Dim OL As Object

Dim EmailItem As Object

Dim Doc As Document

Application.ScreenUpdating = False

Set OL = CreateObject("Outlook.Application")

Set EmailItem = OL.CreateItem(olMailItem)

Set Doc = ActiveDocument

Doc.SaveAs

With EmailItem

.Subject = "Insert Subject Here"

.Body = "Insert message here" & vbCrLf & _

"Line 2" & vbCrLf & _

"Line 3"

.To = "[email protected]"

.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow

.Attachments.Add Doc.FullName

.Send

End With

Application.ScreenUpdating = True

Set Doc = Nothing

Set OL = Nothing

Set EmailItem = Nothing

Flag = True

sPath = "P:\"

ActiveDocument.SaveAs FileName:=sPath & ActiveDo... Read more

More replies
Relevance 71.75%

I use this VBA code in a Excel Macro to send a email. Actually i send a range of cell pasted in the mail but i can´t send the file (it´s was created in the same macro) attached.
Can you help me?

I send the code. How can i do for send the Prueba.doc file attached.

Sub Envia_CVP_LOTUS()

oldStatusBar = Application.DisplayStatusBar

Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim WordDoc As Object
Dim EmbedObj As Object

Application.DisplayStatusBar = True
Application.StatusBar = "Creando el correo..."

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")

If Not NDatabase.IsOpen Then
NDatabase.OpenMail
End If
'Create a new document

Set NDoc = NDatabase.CreateDocument

With NDoc
.SendTo = "[email protected]"
.CopyTo = ""
.Subject = "Previsión Marginal de GN para el " & Date + 1

'Email body text, including marker text which will be replaced by the Excel cells

.body = "Envío la previsión de máquinas Marginales de GN y Combustible Alternativo para el día de mañana." & vbNewLine & vbNewLine & "Donde CVP USD = CVP / ( Fn x Cotización U$S)" & vbNewLine & _
"**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & &qu... Read more

More replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

I need a few macros (I have no experience with VB or writing macros):

To search a column and add a period (.) to the end of any fields that do not have one.
To search a column for any instances of ellipses (more than one consecutive period) and reduce them to a single period.
To search a column for any instances where there is a period or comma followed by anything other than a space, and to add one.

To have one macro that could do all three would be ok, or separated into separate functions.

Thanks in advance!
Joel
 

Answer:Excel 2007 Macro

One to eliminate multiple consecutive spaces would be useful as well.

With the others, the functions can be at cross-purposes: for example, if a field ends with a period, I don't want to add a space; similarly I would want to eliminate ellipses first, because otherwise when I search for periods followed by not-spaces, ellipses will be caught in that and changed.

Forgive my lay-speak. I'm not a dev or anything, just an editor who wants to make his work faster by eliminating these common errors.
 

2 more replies
Relevance 71.34%

Can somebody please help? I have very little VB knowledge. I am trying to create a macro that reads a column of data (column A which is labeled Territory) and creates new worksheets for each distinct Territory. Everything would start from the first sheet of data and then need to be transposed to new worksheets under the respective Territory. There are 11 total columns of data; 10 distinct Territories; and multiple rows for each Territory. Thank you in advance for your help and assistance.

Answer:Excel 2007 Macro - HELP

Hi,This macro takes each of the Territory names from column A on the source worksheet.If there is no worksheet with that Territory name, it creates it.The macro copies the data from each row to the worksheet with the same name (column A)This is a one-time macro, meaning that it creates and copies all the data. If you add data to the end of the source worksheet, all the data on the source worksheet is appended to the existing Territory worksheets data.Depending on how you use this workbook you could add code to clear each Territory worksheet before it is re-run or you could delete all the Territory worksheets before re-running it.To run the macro, I suggest you add a button to your source worksheetFrom the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)In Developer - Controls select Insert and choose the button icon.Draw the button on the worksheetIn the 'Assign Macro' dialog box select 'New'In the code window that opens enter this:Option Explicit

Private Sub Button1_Click()
Dim strSrcWSName As String
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngDestEnd As Range
Dim rngSrcCell As Range
Dim objWS As Object
Dim blnWSPresent As Boolean

On Error GoTo ErrHnd

'turn off screen updating to increase speed
'and remove screen flicker
Application.ScreenUpdating = False

'get name of this worksheet
strSrcWSName = ActiveSheet.Name

'set sta... Read more

3 more replies
Relevance 71.34%

View attachment 212815
Hi,

Please find the attached word document for my query on excel macro. The current data and expected results are mentioned in the attached document. Please help me in creating a macro for my expected results.

Regrads,
Akki
 

Answer:Macro help for excel 2007

6 more replies
Relevance 71.34%

I am pretty much a beginner with using Excel for anything other than simple sums, but I now have a budget spreadsheet to work on. I need a macro that will update a simple sum formula eg =SUM(B2:O2) to include the next adjacent column, eg =SUM(B22). I have recorded a macro that does that, and extends the formula change to the rest of the column, but I need to do this every month, to include the next column.
The spreadsheet is roughly like this:

Jan Feb March April May June Total To Date Projected Total
Salary1
Salary2
Salary3

Every month I have to update the Total to Date column to include the past month. At the moment all my macro does is extend the formula to a specific column (March) and that's it, rendering it useless for next month. Is my only option to record a new macro every month, or is there VB language to indicate the next adjacent column, rather than a specific column?
Apologies if this is a very simplistic question - I just feel sure there's a way of doing this that I just don't know about yet!
Thanks for any advice.
 

More replies
Relevance 71.34%

I have been trying to figure out if it was possible to do a fairly complicated replacement.

Just a description of the file:
- There are columns a-q in the file, with rows going up to about 9000.

Each day I have to upload several hundred files onto an outside website which I then have to copy down by hand the numbers of the files. Those files are organized similar to "5 bay - 99,84,77,33,45 STR 555 - AA.2012.file type". The only unique piece of each file is the number following STR, which is the part that i write down. This number is in column E.

My job after writing down the numbers of each file that fits into a corresponding row in column E, is to go into columns O and P and put my initials in column O and the current date in column P.

I was wondering if it was possible to make this quicker through the use of some macro. I can always type the numbers into a separate excel sheet and feed the macro that list somehow. The columns are not always O and P either. It can also be J and K, or Q and R as well depending on what I had done.

So all in all, I'm asking if it is possible to create a macro that I can feed the number that corresponds in column E and have it add my initials and the current date to the two columns of my choosing. Thanks in advance, even if it isn't possible!

PS: A blanked version of the worksheet we use is included
 

Answer:Excel 2007 Macro Help

Hey there, just registered to help you out! Check this and tell me if it works, just make sure to backup your files beforehand *wink*

Sub Book1()

Dim Initials As String
Dim StoreColumn As Integer
Dim InitialsColumn As Integer
Dim DateColumn As Integer
Initials = "KM"

For i = 1 To 30

If ActiveSheet.Cells(1, i).Value = "Store No." Then
StoreColumn = i
Else
If ActiveSheet.Cells(1, i).Value = "Assignment Completed by:" Then
InitialsColumn = i
End If
If ActiveSheet.Cells(1, i).Value = "Date Assigned" Then
DateColumn = i
End If
End If

Next i

For j = 1 To 10000

If ActiveSheet.Cells(j, StoreColumn).Value = "5526" Then
ActiveSheet.Cells(j, InitialsColumn).Value = Initials
ActiveSheet.Cells(j, DateColumn).Value = Now()
End If

End Sub

Im not sure of your experience level with excel but paste it in VBA in excel and run it. The code searches through columns to find the ones with stores, initials and dates and remembers where they are. Then it searches through stores to find the 5526 value and puts the current date and initials that you can set at beginning of the code. Let me know what you think.

PS. Before you run it make sure to check the names of the columns. I wrote what I saw but it isn't necessarily what there is. Just copy the names from columns and paste them to the code
 

2 more replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

Thanks to Zack Barresse for replying to my last thread; I have a similar question this time.

I need a macro that will quickly convert all text in a given cell (or range of cells or column) into uppercase. Up to now I've been manually entering the =upper(cell) formula, copying the contents, and then Paste Special -> Values for each one I have to do; this is obviously time-consuming.

(also a link to a beginner VB or Excel macro course might be useful, heh..)

Thanks!
Joel
 

Answer:Excel 2007 Macro

11 more replies
Relevance 71.34%

Hi,
i'm using excel 2007 for the first time. i download a few spreadsheets every day that require a lot of reformatting and i use the "record macro" button to write the macro so that i can same about 1 hour every day (i have very limited knowledge of vba). the problem is that even if i save the worksheet the macro disappears the next day.

please walk me through on how to exactly record a macro that i can use on a daily basis. i tried putting it in the personal macro workbook but it doesn't take.

regards,
 

Answer:Excel 2007 Macro help

10 more replies
Relevance 71.34%
Question: Macro Excel 2007

I wish to make a macro that will paste All from the clipboard Excel 2007 ( do the same as the Paste All Clipboard button )
The cell is A1 sheet "H"
after it has pasted there another macro to clear All (do the same as the clear all Clipboard button)
any ideas on the macro code??
Many thanks
Steve
 

More replies
Relevance 71.34%

I am trying to create a Macro in which I have data in sheet 2 that I copy and paste into Excel 2007 from a website that changes on a monthly basis that I need to place into sheet 1 at the bottom of the page.

Currently, sheet 1 column A is the date. Currently the 9/30/12 date is on row 44 and row 45 would be 10/31/12, etc...each row is a new end of month. I do this on a quarterly basis and for multiple tabs so the macro would be for all the sheets, but I figure if someone could tell me the macro for sheet 1, I can change the sheet names in the macro for the other sheets.

I need to create three rows at a time starting on row 45 for the next three months, then row 48 for the three months after, etc... Cell g45 will come from sheet 2 cell F3; g46 will come from sheet 2 cell F4 and g47 will come from sheet 2 cell F5. I need these cells to be copied and pasted with values only because I will change Sheet 2 on a quarterly basis and I need the "old" numbers to stay and be fixed. This means I can not use the equal sign to setup my formula. Cell H45 will use the formula from cell h44 (I normally just drag down) and this will continue until cell h47. Cells M45, N45, & S45 use the same drag down feature for the formula that the "H" cells do. There are no other columns with active data in it.

Does anyone know how to create a Macro for this? I tried to do the Macro Record feature, but it does not move down, it only create three new blank rows and does not give me... Read more

Answer:Excel 2007 Macro Help

Post a sample workbook and I'll be happy to take a look and offer my assistance.

Rollin
 

2 more replies
Relevance 71.34%

I hope you guys can help me out again with this. It seems similar to the the issue that I posted last time. But I think it is a little bit more complicated.
I receive two notepad - files (for example importfile 1 and importfile 2) via mail, I copy paste the data in an excel sheet.
Each line in the notepad - files is an order. The format of the file is :
- Character 1 to 2 (included) = Plant
- Character 3 to 5 (included) = Route
- Character 6 to 8 (included) = Suffix
- Character 9 to 18 (included) = Delivery Date
- Character 19 to 27 (included) = DunsNo
- Character 28 to 35 (included) = ContainerNo
- Character 36 to 40 (included) = Quantity
- Character 41 to 49 (included) = Total Weight
- Character 50 to 61 (included) = Volume
- Character 62 to 69 (included) = Planned Date
A csv-file ( has to be created for each line with the same route, suffix, delivery date and DunsNo.
The name of the csv-files to be created has the following format : Route_Suffix_Delivery Date
(for example DG2_3HB_22-12-2010
The output file consists of following format :
Cell A1 is always T , Cell A2 = the data in collumn D that matches the routeNo in collumn A of a excel-file (for example Trailer_Types1)
Then each row begins with P in collumn A for each line with the same route, delivery and DunsNo
Collumn B = Plant of notepad - file
Collumn C = Route of notepad - file
Collumn D = Suffix of notepad - file
Collumn E = Delivery Date of notepad -file
Collumn F = DunsNo of notepad - file
Collumn G = Container... Read more

Answer:VBA - Excel 2007 - Macro

16 more replies
Relevance 71.34%

I have searched the net but cannot find anything specifically what I'm trying to accomplish.

I have a timesheet, of sorts, that I added buttons to move around and I would like to add a button for the user to click and it will send the Excel spreadsheet to me when they're done entering their data. I can get the email to open a new email message with the file attached but I'm stuck on getting my name in the To field, as well as a subject and body. Then I would like for it to send and display a message that the email was sent.

I am using Office 2003

Any help would be appreciated.
 

Answer:Solved: Macro to Send Email with Spreadsheet as attachment

7 more replies
Relevance 70.93%

Hello all,

Am hoping someone may be able to help me with a task I am trying to complete.

I have a work book that I need to edit on a daily basis, and I ideally want to be able to send an email to specific email address when a date is input.

I have attached a copy of the workbook for perusal.

To try and explain - rows 3 to 11 contain data that I would want to be emailed to the email address listed in column c (drop down menu). But what I want to achieve is that the email is sent as a meeting/apt for the user listed in C (from the drop down menu of email addresses). But I want that meeting/apt to be made for the date in the BOOKED TO field.

I appreciate that this may not be possible as an automated process, so if it is possible, to have a 'send mail' button configured at the end of each row to send an email to the recipient that would also help.

I have done a fair bit of searching and looking at various bits of code - but I will have to be honest and say I have no idea on how to implement the code into what I am trying to achieve.

If anyone is able to spend time looking into this for me I would be ever so grateful - appreciate all are busy!

Many thanks in advance.
 

More replies
Relevance 70.52%

How can i make macro to print the worksheets of a workbook.?

Answer:print macro excel 2007

Please provide some more details.You can print multiple sheets of a workbook just by selecting multiple tabs via Shift-Click for contiguous tabs or Ctrl-Click for non-contiguous tabs, and then choosing PrintWhat exactly do you want the macro to do?P.S. Don't forget to included a Please and Thank You with your requests. We're all just volunteers here trying to help other members of this site.

2 more replies
Relevance 70.52%

I have created a roster that I want to barcode scan into. I want to run "text to columns" automatically when data is scanned into a1, then tab to a2, scan data, have "text to columns" auto run again. I know I can use the shortcut key but I would rather not. I want the user to simply scan into a1, a2, a3 and so on.
 

Answer:Excel 2007 How to automatically run a macro

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub

'Your code goes here.

End Sub
 

3 more replies
Relevance 70.52%

I have a spreadsheet of a lot of Data and I am having troubling finding a guide to take any cell and take 10% of the value away from it. I'd like to just select a cell or row and then simply click the button (or others who use the spreadsheet) and it reduce the current value by 10%.In Addition, I'd like to somehow create a similar button that also adds 10 or 2 to the current selected cell or row.Please let me know if this is possible.Thank you in advance.

Answer:Button and Macro Help in Excel 2007

Google something like Excel Command Button for lots of hits on how to create a Command Button and assign a macro to it.This is just one of many sites: http://www.mrexcel.com/tip068.shtmlAs far as the code required, your question isn't clear.I understand the part about reducing the values in the selected cells by 10% and I'll provide code for that below.However, this part is not clear:a similar button that also adds 10 or 2Do you mean 10 or 2 percent or the actual value 10 or 2?Do you want the code to randomly decide whether to add 10 or 2?Do you want to be able to choose the value that is added after you click the button?Do you a button specifically assigned to adding 10 and another button specifically assigned to adding 2?Please clarify.Note: The following code checks to see that the value in each selected cell is a number. If it is, it reduces it by 10%, if not, it skips it. However, if the value is the result of a formula, it will replace the formula with the value minus 10% and the formula will be lost. You can't subtract 10% from a formula generated value and retain the formula since the formula would no longer be accurate.If you study this code and understand how it works, you should be able to modify it to add your 10 or 2.Sub Subtract10Percent()
'Loop through selected cells
For Each myCell In Selection
'Check to make sure cell contains a number
If IsNumeric(myCell) Then
'Add 10%
myCell.Value = myCell - myCell * 0.01
End If
Next
End SubClick Here Befo... Read more

4 more replies
Relevance 70.52%

Hi, I'm trying to make a macro that will go to a comma in a cell and then delete everything from the comma to the end of the line and then do this for the whole worksheet. The comma must also be deleted. Is it possible to do something like this or must it be done manually? Thanks for your help.

Example:
Red, Gold and yellow with Green Stripes

The final result:
Red
 

Answer:Solved: Excel 2007 Macro Help

13 more replies
Relevance 70.52%

I am using Excel 2007. I want to hyperlink several stock symbols to open stockcharts.com. I know how to hyperlink, but think it would be quicker since I have over 50 to create a macro. The problem is the display text stays the same each time I run the macro - so basically each cell reads AAPL instead of continuing. What should I do?

I have Windows Vista installed.

Thanks in advance!
 

More replies
Relevance 70.52%

Hi. My name is Chuck and I am new to the forum. I am working on an Excel 2007 Workbook in which I am saving certain activities to macros. When creating these macros, I am storing the macros in "This Workbook". My ultimate goal is to be able to save this workbook with the macros I have recorded and assigned to buttons within the workbook to a thumb drive and give it to a friend who can then open the file on any machine that has Microsoft Excel 2007 running on it, and use the workbook and have the macros work. However, it appears that when I close the file and reopen it later to continue working on it (on the machine I am creating the file on), it is as if I never even recorded the macros... they are gone. Please help.
 

Answer:Excel 2007 Macro Question

9 more replies
Relevance 70.52%

I have researched my issue but other threads posted here did not solve my problem. I have an Excel 2007 spreadsheet that was first designed in Excel 2003, and it has a macro that generates 93 spreadsheets with prepopulated data. The macro works, it generates the files but then I cannot open them due to the following error:
'Excel cannot open the file 'xxx.xlsx' because the file format or file extension is not valid.'
I have tried changing the macro to save as .xlsm, but get the same result. I have conditional formatting that will not work in Excel 2003, plus most of the people who will be opening the file are on Excel 2007, so I don't want to save in xls format; however, I did change the macro to see if it worked for Excel 2003 and it does.

Sure hope someone can help.

Here is a sample from the macro:

Range("B1:I1").Select
ActiveCell.FormulaR1C1 = "HP Argentina"
Range("C2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\P2012 Templates\P2012 Country Summary and Cost Forecast - HP Argentina.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
 

Answer:Excel 2007 Macro Issue

If the macro is being run in 2007 you should first try changing the FileFormat value that you are passing. Change the portion below.

FileFormat:=xlNormalClick to expand...

So that it reads

FileFormat:= xlOpenXMLWorkbookClick to expand...

If the workbook is a macro enabled workbook use the line below instead.

FileFormat:= xlOpenXMLWorkbookMacroEnabledClick to expand...

Rollin
 

2 more replies
Relevance 70.52%

Hello,
Macro's for excel is new for me so I hope somebody can help me.
I receive a notepad file via mail. The content of this mail I copy/paste in an excel sheet.
This Notepad file consist of H-lines (=header lines - begins with H) and B - lines (Goods lines - begins with B).
For each H-line I have to create a new file in Excel. In this file, for each B-line that comes after the H-line,I have to get data in different cells:
In Cell A, I need characher 420 to 422 (included) of the H-line
In Cell B, I need character 2 to 10 (included) of the H-line
In Cell C, I need character 23 to 31 (included) of the H-line
In Cell D, I need character 27 to 35 (included) of the B-line
In Cell E, I need character 2 to 9 (included) of the B-line
In Cell H, I need character 348 to 351 (included) of the H-line
In Cell I, I need character 61 to 69 (included) of the B-line
After all the B -lines of the H-lines are processed the file may be saved as CSV-file (comma delimited)
Then the next H-line can be processed and a new file may be created.
Then the process is the same as above. This continue until no H- or B-lines are found in the file.

Can anybody help me?
 

Answer:Solved: VBA Macro - Excel 2007

16 more replies
Relevance 70.52%

I keep getting a runtime error with the following macro, which is part of a list of routines when I convert an excel spreasheet to a csv file for uploading data into another program. The line highlighted below is the one shown when I click on the debug button. Range a4 is the last row of my data.

'inserts the word end below the last data

Range("a4").Select
Selection.End(x2Down).Select
ActiveCell.Offset(1, 20).Select
ActiveCell.FormulaR1C1 = "end"
Selection.Copy
Range(ActiveCell, ActiveCell.Offset(0, 50)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
 

Answer:Excel 2007 macro problem

Morning and Welcome to the PCReview Forum!

Please could you provide the full details of the Runtime error.
 

7 more replies
Relevance 70.52%

Hi, I have got an Excel spreadsheet which has incident numbers in column A. For each of these incidents I need to put in hyperlinks which are very very similar. The first part of the hyperlink is the address to the database and the second part is the Incident number itself. I'm trying to create a macro which automates the process of putting in the hyperlink plus the incident number in. Can this be done?
 

Answer:Excel 2007 Hyperlink Macro

7 more replies
Relevance 70.52%

Hello,
I am an analytical chemist and I am dealing with very large data matrix (11,000 x 3).

I am looking for a macro that can reduce the data in following way

Column 1 Column 2 Value
A, B 1
B, A 1
A, C 1
C, A 1

With a Result output

Column 1 Column 2 Value
A, B 1
A, C 1

In other words in this data set A to B is the same as B to A and would like to delete the duplicate row.

Any help doing this with Macros would be appreciated. You can imagine going through 11,000 line looking for these types of entries
 

Answer:Solved: Excel 2007 Macro help

8 more replies
Relevance 70.52%

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?

Thanks!

More replies
Relevance 70.52%

Thanks to Derby and Humar for their help on the macro yesterday. Today, I've come across another problem. Based on the information that is separated into different worksheets based on years, I need to show that the money collected in a graph format. The problem that I need help with is, I need a macro that would graph column E (money collected) of each spreadsheet on the y-axis and then graph the months based on column B (date) of each spreadsheet. It also needs to be able to update itself from information in sheet 1. I know it sounds complicated, but i just can't figure it out. Any help would be appreciated.The macro that is already being used for this workbook to separate information from sheet 1 to other worksheets based on year is pasted below:Option ExplicitPrivate Sub Button1_Click()Dim rngStart As RangeDim rngEnd As RangeDim rngCell As RangeDim strYear As StringDim rngSearch As RangeDim rngFind As RangeOn Error GoTo ErrHnd'stop screen updating to increase speedApplication.ScreenUpdating = FalseWith Worksheets("Sheet1")'set start as row after cell with 'Copied' in it'if 'Copied' not found use B2 i.e., after heading row in column BSet rngSearch = Range("B2:B" & CStr(Application.Rows.Count))Set rngFind = rngSearch.Find("Copied", LookIn:=xlValues)If rngFind Is Nothing Then'Copied not found - so start at B2Set rngStart = .Range("B2")Else'Copied found'set start to row after 'Copied'Set rngStart = rngFind.Offset(1, 0)'delete the row containing 'Copied'rngFind.EntireRow.Delete... Read more

Answer:Graph Macro in Excel 2007

Hi,I am not sure why you are looking for a macro to create a chart from existing data.Assuming that you have dates in column B (say B2:B100) and amounts in E2:E100),then create a table of months.In cells G1 to R1 enter dates for the first of each month for the year in question.Format G1 to R1 as months "mmmm"In cell G2 enter this formula:=SUMPRODUCT((MONTH($B$3:$B$101)=MONTH(G1))*($E$3:$E$101))Note the $ signs.Now drag to extend the formula to cell R2R2 will contain:=SUMPRODUCT((MONTH($B$3:$B$101)=MONTH(R1))*($E$3:$E$101))Cells G2 to R2 will contain the monthly amounts.Select cells G1 to R2 and from the menu bar select 'Insert' - 'Chart'and create a chart of amounts by month.When completed, you can select the category axis, right-click, select 'Format Axis...' and select the number tab, and change the date display to just months if required, e.g., mmmmRegards

4 more replies
Relevance 70.52%

I am having a problem I cannot resolve when recording macro in Excel 2007. I am trying to record macro in a file which has the following:1. Sheet one has several columns headed as suppliers code, nominal a/c code date, gross amount, tax and net amount, etc. In each row below there are the actual codes, dates values etc for each transaction. 2. Sheet 2 has several columns but the main ones for my purposes are headed "supplier code" and "supplier name". Below in each row is the supplier code no. and the respective supplier name in the adjacent cell3. Sheet 3 is similar to Sheet 2 but it has details of all the nominal accounts instead of supplier accounts.The data in Sheets 1, 2 and 3 have been copied from 3 separate files and included in 1 file mentioned above with 3 worksheets. I now start to record macro and I tidy up sheet 1 and insert new columns where required and unmerge cells and do other stuff and by using the VLOOKUP Function I am able to state the respective Supplier name and Nominal account name in each row which is what I want to achieve. I save this file as a macro enabled Excel file. My problem is when I clear the contents in Sheet 1 and run the Macro again I can see the cursor moving everywhere but most of the original data that was there gets lost somehow.Also I want to run this routine regularly every month by updating the data in sheet 1. How can I get the macros to give me the correct layout that I have achieved previously. The number of rows in subsequent... Read more

Answer:Recording macro in excel 2007

The bottom line is that the recorder is simply going to record the exact steps that you perform while the recorder is running. If you start the recorder before you copy the data from another file, it may help, it may not.If the data is not in the exact same location, with the exact same format each time you run the macro, you may not get the same layout when it is pasted into your new sheet. Then, when the macro starts Cutting/Copying/Pasting, it simply going to perform those operations on the exact ranges it saw when you recorded it. If there is different data or a different layout in those ranges, you will not get the results you expect. There are ways to have the macro find the exact data you want to Cut, etc. but that takes manual code writing. You wouldn't be able to record that.One way to understand what the recorded code is doing is to single-step through it, watching your sheet as each instruction is executed. I suggest that you read the Single Stepping portion of this How-To. I think it will help you understand what I'm talking about.http://www.computing.net/howtos/sho...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 70.52%

hi hi,
could you help me to solve my macro problems with excel 2007.
i had the following macro in 2003 which is not working now:

Sub Journal()
'
' Journal Macro
' Macro recorded 28/01/2008 by od28689
'
' Keyboard Shortcut: Ctrl+j
'
Sheets("Upload").Select
Sheets("Upload").Copy
Dim div As String
div = Range("A1")
ChDir "Z:\UPLOAD\US1"
ActiveWorkbook.SaveAs Filename:="Z:\UPLOAD\" & div & "\JOUR25.prn", FileFormat:= _
xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Sheets("Journal").Select
Range("D8:E8").Select
End Sub

Many thanks in advance!
cheers,
tekike
 

More replies
Relevance 70.52%

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

Sale Date Sale Price Adj Sale Price 2010 Land 2010 Imp 2010 Total
7/7/2009 10,000 10,000 13,300 0 13,300
7/7... Read more

Answer:Excel 2007 Macro Sum 2 Columns

13 more replies
Relevance 69.7%

Hi All,

I'm trying to create a macro that will fill cells within a column with a zero when the cell is blank and leave the cell as-is when it is populated with a value. I figured out how to fill a column with zero down to the last active row in the sheet but I need to revise it to skip the cells with values.

Please help, I'm stuck at this point and it is the last part of the macro that I cannot figure out.

Thank you!

Jen
 

Answer:Solved: Excel 2007 Macro Question

9 more replies
Relevance 69.7%

Hello, I am very new to Excel 2007 and even newer to Macros. I have a spreadsheet that is created automatically that can contain many rows of data. The First column always holds certain words, such as "pending" or "Resolved". I would like to create a Macro to sort all of the rows with the same word in the first column into new sheets. For example, let's say their are 20 total rows in this sample, 8 rows have Pending in the first column and 12 have Resolved in the first column. I would like to search for all the rows that have pending in the first column and cut and paste the entire row to a new sheet. Then do the same for Resolved. I do not have much coding experience, what would be a good example?
 

Answer:Solved: Excel 2007 Sort Help - Macro

Welcome to the board.

Here's some code, whether it's a good example would be debatable. Post back if you need help with installing/running.

Sub pending_resolved()
'add new sheet, 'Pending'
x = ActiveSheet.Name
Sheets.Add
ActiveSheet.Name = "Pending"
Range("A1") = "Status"
Sheets(x).Select
'get last row on main sheet, for range to loop
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'loop range; if cell = 'Pending', copy row then blank cell
For Each Cell In Range("A2:A" & LastRow)
If Cell = "Pending" Then
Cell.EntireRow.Copy Sheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1)
Cell.Clear
End If
Next Cell
'delete all rows = blank in column A
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'rename main sheet 'Resolved' (only 'Resolved's remain)
ActiveSheet.Name = "Resolved"
End Sub
 

2 more replies
Relevance 69.7%

Hi All.

I have an attendance list for a course I'm running. It is essentially an array with the names of the attendees down the first column and a series of columns to the right (so that there is a block for the "X" i use to mark that they attended)

The problem is that if a person leaves early or starts the course late, I need to add their name and so I need the whole array to auto sort in rows by the first column. Obviously I can do this manually but I would prefer that it happen automatically if the first column list is changed. I have tried using a macro but the calculation takes ages. VB code is the same...

Can someone help me with a code which will do this for me?!?

I have attached the worksheet in question

TIA

drj
 

Answer:Excel 2007 Sort Macro Problem HELP!

7 more replies
Relevance 69.7%

I had been using a spreadsheet in Excel '97 which has a toolbar containing about 20 macro command buttons. This toolbar resides down one side of the spreadsheet - not across the top like normal toolbars.

I have now started using Excel 2007 which is quite different. The spreadsheet works and the macros have been copied across. However, the toolbar appears as on "Add-In" tab as a "Customized Toolbar". This only contains 12 command buttons - the other 8 or so are missing. The missing 8 can only be run by displaying the macros and then hitting the run button, or assigning a shortcut key. Of the 12 remaining buttons I cannot modify them or delete them, and I certainly can't add new ones. One of them doesn't run because it claims to unavailable '..in This Workbook', yet it will run by hitting the Run button.

I really don't know which way to turn for advice - except perhaps to revert to Office '97. Is there anyone there who can point me in the right direction?
 

Answer:Excel 2007:Toolbars and Macro Problem

This site has a download that adds a button to the QAT which allows a list of your macros to be held. This will appear as the old menus used to in 97. It is very easy to use and self explanatory.
http://www.rondebruin.nl/qat.htm
I certainly found this to be very useful as I regularly need access to about 10 macros.

Hope this helps
 

6 more replies
Relevance 69.7%

Greetings!

I have a macro in Excel to filter out rows with zeros. That works good. I want to change the "0" to a text string "(A1)". How do you do that? I tried putting both double and single quotes and it wouldn't work.

TIA for your ideas!

---------------------------------------------------
Option Explicit

Sub HideZeros()
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd End Sub

Sub ShowZeros()
Selection.AutoFilter Field:=1
End Sub
 

Answer:Office 2007 Excel Macro has me stumped...

Try something like this....
Code:


Selection.AutoFilter Field:=1, Criteria1:="=[I]TextGoesHere[/I]", Operator:=xlAnd

 

2 more replies
Relevance 69.7%

I need some help with a macro. I need to save data from certain cells in 1 worksheet and to another worksheet. Details belowI want to grab the data in worksheet "survey" from cells A3, A4 and A6 and save them to worksheet "data" in cells A2, B2 and C2. After the save is done I then want to clear the data from cells A3, A6. I created an object button in my workbook that is clickable. I just need to assign a macro to it. I have been working on this for over a week and I am new to macros so it is a struggle. I have even went as far as purchasing a program call teachmemirosoftoffice and still can't figure out the whole thing. I get portions of it to work but not all of it. I would greatly appreciate anyone who can help me write this macro. I would forever be indebt to you my friend. Thanks.

Answer:Need a macro for excel 2007 to save data

Since you do not appear to have a contiguous range of cells in your source document, you have to do each cell individually. A contiguous range could have been copied and pasted Transposed into your destination, but since you skipped A5, you can't use that method.Try this:Sub CopyThenClear() Sheets("data").Range("A2") = Sheets("survey").Range("A3") Sheets("data").Range("B2") = Sheets("survey").Range("A4") Sheets("data").Range("C2") = Sheets("survey").Range("A6") Sheets("survey").Range("A3, A6").ClearContentsEnd Subre: "I would forever be in debt to you "Forever is a long time to be indebted for a few lines of VBA code. A simple thanks and a promise to pass it forward will suffice.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 69.7%

Hello. I am attempting to develop a macro that will allow me to find a number in column D and replace that value with a text name. I have 31 - 5 digit codes with 31 corresponding text names. The spread sheet is 10,000+ entries and growing every day. For example, I would like to find all instances of "21021" (in column D ONLY) and replace them with the word "Boyle." And so on, for all 31 codes. I'm new to Macros and could really use a little help to get me started!

Thanks!
 

Answer:Excel 2007 Macro for Find and Replace

7 more replies
Relevance 69.7%

I'm working on a macro that does several things with budget reports. I have zero knowledge of Visual Basic, just recorded my macro and played with the code.
It creates a line called "total expenses" then, six cells to the right, I need it to add the value in a row titled "total coaches" and another value in a row called "total directors". these values are 6 cells to the right of the titles. so what I had tried to do was search for the "total coaches", move over six cells, take that cell's location and add it to the "total directors" value with the same process. however, the macro just used RC to record the cell's position. the position of the value needs to be dynamic as the reports will change.

I can get it to create the "total expenses" line and make the cell six to the right of it active. I just can't get the formula to be dynamic based on a text search.

here's the relevant code:
ActiveCell.FormulaR1C1 = "Total Expenses"
Range("A247").Select
Cells.Find(What:="Total Expenses", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(6, 0).FormulaR1C1 = "=(R[-147]C+R[-1]C)"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFo... Read more

Answer:Solved: Dynamic Excel 2007 Macro

This should do what you want, but I don't have an example to look at so I just made up my own example

You can adjust the column numbers to match yours. Note that I made some additions. I think in your recorded macro you went to Row 247 at the start, which I am assuming is the last row...I added code to find it for you.


Code:
Public Sub test()


Dim Coach As Integer
Dim Direct As Integer
Dim totalExp As Integer

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand


' finds the last row in column 1
lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'fills in cell 2 rows below last empty
Cells(lrow + 2, 1) = "Total Expenses"

totalExp = lrow + 2


Cells.Find(What:="total coaches", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Coach = ActiveCell.Row


Cells.Find(What:="total directors", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Direct = ActiveCell.Row


Cells(totalExp, 2) = Cells(Coach, 6) + Cells(Direct, 6)


err_Hand:
' 91 is the error number when the search value is not found
If Err.Number = 91 Then
MsgBox "Search value not found"
Exit Sub

End If

End Sub

 

3 more replies
Relevance 69.7%

I have about 20 macros on an Excel 2007 spreadsheet. I have added buttons into the QAT. Unlike Office 97 - the buttons aren't labelled and the design of each button is limited to the choice of 180 available. You would think this was enough, but it isn't. What happened to the ability to design one's own buttons? At the very least I would expect to be able to use letters of the alphabet, but unless you need an "A" the choice does not exist. Is there a way round this problem ?
 

More replies
Relevance 69.7%

I have a workbook that has no macros. Every time I open the file I get this message: "Security Warning Macros have been disabled" Is there a way I can get excel to show me what it is disabling?
 

More replies
Relevance 69.7%

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

More replies
Relevance 69.7%

Hi,

Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.
 

Answer:Solved: excel 2007 macro error

7 more replies
Relevance 69.7%

I am looking for a macro that willl do the following:
I have a date in cell A12
I want to put that date in each cell of column b as long as the corresponding cell in column a has a number in it.
After all dates are entered, I want the macro to skip two lines and start again, but this time add one day to the previous date.
Can someone help me on this?

Here is a screenshot which will hopefully clear up what Im wanting to do.


 

Answer:Solved: need help writing macro in excel 2007

16 more replies
Relevance 69.7%

Is there a way to have Excel open a folder and place the file name with extensions in Column A. Then open another folder and place the contents of that folder into Column B?

I also would like the list to skip adding file to column if it already exist on worksheet, but if a file was deleted from folder it would be removed from the worksheet.
 

Answer:Excel 2007 Macro/Folders & Files

Hi

Here is a macro that I use quite often ...
Code:


Sub Get_File_Names()

[B]'1st directory[/B]

lRowA = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lRowA).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 1) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 1) = MyFile
Wend


[B]'2nd directory[/B]

lRowB = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:B" & lRowB).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 2) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 2) = MyFile
Wend
End Sub

Unless identifying which files are new and what's been deleted is important I would just delete the previous list and get all current files - as I've done in this code.
 

2 more replies
Relevance 69.29%

Ok, first of all I'm new to your site and WOW! I am incredibly impressed with the wealth of knowledge I've come across while searching through various forums! I've already found all kinds of great macros and ideas that will make my workload a lot easier. That being said, I have a problem with efficiently sorting some data and I hope one of you macro gurus can assist :)I've found several macros that do something similar to what I'm looking for, so hoping to get something a little more specific. Basically, I have some weather equipment that monitors up to 5 things in the air and then transmits data to my computer via radio, and spits out text files that I then copy into excel. It creates one text file for each thing I'm monitoring. The format is:Date (mm:dd:yy:hh:mm:ss), WhatIsMonitored, Unit#, ConcentrationSo for example if I'm using this equipment to monitor the concentration of oxygen in the air over the course of a couple minutes using just one unit (and by unit I mean the machine that is doing the monitoring), it will give me something like:09/01/2010 17:02:55,O2,U_10_O2,20.90000009/01/2010 17:03:09,O2,U_10_O2,20.90000009/01/2010 17:03:25,O2,U_10_O2,20.90000009/01/2010 17:03:40,O2,U_10_O2,20.90000009/01/2010 17:03:55,O2,U_10_O2,20.90000009/01/2010 17:04:10,O2,U_10_O2,20.90000009/01/2010 17:04:25,O2,U_10_O2,20.900000So for in this case it's not too much trouble to just copy the text into Excel, then use the "text to columns" feature to put it in columns.The problem I'... Read more

Answer:excel 2007 macro to sort data to new sheet

After you use the text to columns feature, I assume your data looks like this:
A B C D E
1 09/01/10 17:02:55 O2 U_10_O2.txt 20.9
2 09/01/10 17:03:09 O2 U_11_O2.txt 20.9
3 09/01/10 17:03:25 O2 U_11_O2.txt 20.9
4 09/01/10 17:03:40 O2 U_11_O2.txt 20.9
5 09/01/10 17:03:55 O2 U_12_O2.txt 20.9
6 09/01/10 17:04:10 O2 U_12_O2.txt 20.9
7 09/01/10 17:04:25 O2 U_13_O2.txt 20.9
This will probably need some tweaking, but it might get you started:Sub SplitSheets()
Dim ws As Worksheet
Dim lastDate, srcRw, dstRow As Integer
Dim newdate, wsName As String
'Determine last row with data
lastDate = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
For srcRw = 1 To lastDate
'Remove slashes from dates
newdate = Month(Cells(srcRw, 1)) _
& "-" & Day(Cells(srcRw, 1)) _
& "-" & Year(Cells(srcRw, 1))
'Build worksheet name
wsName = newdate & " Unit " & Mid(Cells(srcRw, 4), 3, 2)
'Create sheet if it doesn't exist
On Error Resume Next
Set ws = Sheets(wsName)
On Error GoTo 0
If ws Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = wsName
End If
'Copy row to next open row in corresponding sheet
dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(1).Cells(srcRw, 1).EntireRow.Copy _
Destination:=Sheets(wsName).Cells(dstRow, 1)
Set ws = Nothing
Next
End SubClick Here Before Posting Data or ... Read more

10 more replies
Relevance 69.29%

Hey guys,I need some help. All of my data is in Sheet one listed as:Column A - NameColumn B - DateColumn C - Amount PledgedColumn D - Amount ReceivedColumn E - Balance OwedAll of the headers of those columns would be the same on each worksheet. What I need help with is that I need to create new worksheets for each new year in the Sheet 1 data. For example, If column B is 06/01/2006 then it needs to go to the "2006" sheet. 07/05/2008 would need to go the the "2008" worksheet and so on. This project is huge. Nearly 8300 lines. Any help would be greatly appreciated. Thanks in advance.

Answer:Excel 2007 Macro To Copy Rows to Tabs

Do you know how to write/modify VBA code?The code in this thread does pretty much what you are asking for, although the ranges it uses as the list for the new sheets is different than yours.Let us know if this helps...http://www.computing.net/answers/of...

13 more replies
Relevance 69.29%

Hi Guys,

I'm using with much profit a code that I have found in this forum:

Sub ReplaceString()

StartRow = 1
EndRow = 10
For i = EndRow To StartRow Step -1
Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Acutally this search coloumn "C" for values in Coloumn "A" and if found replace those values in "C" with what you have in "B:".
Example:

Before MACROAA1ABB1TCC1NB1B2B1After MACROAA1A1BB1TCC1NB1B2B2

what I need is the replaced value in "C" be highlighted as already in the example (iether by red fonts or green interior is ok).
The code need an addition to do that.

Could you help me please?

thanks!


 

Answer:Macro Multiple Find and Replace in Excel 2007 (2nd leg)

Hi, Why not post a sample file with the code you're using and also show what you need.
It's easier to work with a sample file.
Make sure the data is dummy data
 

1 more replies
Relevance 69.29%

Hi all,
This is just a preliminary request for advice before I undertake writing a fairly substantial VBA macro.

My plan, in short, is as follows:

- User receives a daily Invoice file. The data from this is to be dropped onto an Excel Table I've prepared (let's call this the Invoiced Table) in the Master File, which will allow some formulae, such as IFs and Vlookups to static tables, to auto-populate. Each day's data is to be appended to the bottom of the table.

- User receives a weekly Paid file. The data from this will be dropped into a second Excel Table (let's call this the Paid Table) on another worksheet in the Master File.

- Once the weekly file data has been dropped in, the user will run the macro. This will compare records from the Invoiced Table against the Paid Table using at least 4 (probably 8) different sets of lookups. In a simplified example, these might be 4 different customers.

- Each set of lookups will result in some matches. For each matching record for a specific customer, fields from the Invoiced and Paid tables need to be combined into a new record on a seperate worksheet.
So in a simplified example, Record 1 might have an Invoice Number in the Invoice Table that matches the Invoice Number in the Paid Table. I'll need to copy the Invoice Number, Customer Number and, say, the Invoice Date field from the Invoiced Table, plus the Paid Date from the Paid Table.

- I'll then need the macro to delete those lines from both t... Read more

Answer:MS Excel 2007 - VBA Macro to split large file

Is excel the only option? Access would do a lot of this without the having to write a macro every step.
 

2 more replies
Relevance 69.29%

Hi There I Have conditionally formatted column A with 2 conditions I want to be able to switch this formatting on or off as required with a macro please

Answer:excel 2007 macro to switch conditional formatting on and off

1 - Record a macro as you set the Conditional Formatting.2 - Record another macro as you delete it.3 - Assign each macro to a button to toggle between them.The recorded code will be be bloated, meaning that it will have a bunch of "default" settings that aren't really needed. Cleaning up recorded code is a great way to learn VBA.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.29%

I wrote a Macro in Excel that will open an e-mail and send it using the following code.

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = Range("T22")
.Item.To = Range("T15")
.Item.CC = Range("T16")
.Item.Subject = "CST CARRIERS"
.Item.Send
End With

I want to attach a file to this e-mail before sending but don't know the syntax to do this. I know exactly where the file will be stored as well as the file name. Does anyone know the syntax for performing the attachment of the file?


I know you can attach files to these e-mails because if you insert a "Stop" statement just Prior to the ".Item.Send" statement and run the macro you can see and manually send the e-mail. There is an attachment button on the e-mail that I have successfully used to manually attach the file prior to sending the e-mail. I tried recording a macro to get at the code but it didn't copy anything while I performed the manual steps.
 

More replies
Relevance 69.29%

Hi
I need a macro for sorting name and adresses;
Orginal format in cells like this
A1 Name
A2 Last Name
A3 Adress 1
A4 Adress 2
A5 Po number
A6 City
A7 "EMTY"
A8 Name
A9 Last Name
etc....
Some times there is a emty cell between adress and sometimes not.
Also the number of cells may variate (downwards). One complete adress could sometimes be 6 cells and other times 5 or even 4. Mixed in same file

I want it to be sorted so that it appear in a new worksheet sorted like this
A1 Name, B1 Last name, C1 Adress 1, D1 Adress 2, E1 Po Nr, F1 city
A2 Name ...............etc.......

Some ideas ????
 

Answer:Solved: EXCEL 2007 - macro - Real Challenge !!

15 more replies