Computer Support Forum

Excel to Outlook Macro

Question: Excel to Outlook Macro

Hey guys, I had some great assistance from computerman29642 earlier on this macro. It works perfectly on my own calendar; however, I need to target a specific shared calendar. Hopefully it could be user imput by a msg box. I am struggling to find information on how to do this.

The first thread was this
http://forums.techguy.org/business-applications/835748-solved-excel-macro-outlook-calendar.html

So, as for now the example works. But before it writes to the calendar I want a msg box pops up asking for the desired calendar(Sometimes more than one word), and then target that calendar and write the data. A simple little box that confirms or denies progress (I am pretty sure I can figure that one out) Any suggestions or directions where I can learn more fire away please. Ill attach a copy of my test again.

Relevance 100%
Preferred Solution: Excel to Outlook Macro

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 to Outlook Macro

16 more replies
Relevance 65.6%

Hi,

I am an extremely basic user who has managed to get a basic macro together through multiple web searches and am now stuck...

I have dates entered into the following columns in excel: 14,15,16,26,27,28

I have managed to make the macro create appointments for 14,15,16 but not the rest. Also, every time i run the macro it creates a new appointment regardless if one already exists. I saw on this forum about adding a word to another column which the macro searches for and if found doesnt create a new appointment - or something to that extent. Here is what i have:

Sub POATEST()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

' Start at row 7
r = 7

Do Until Trim(Cells(r, 1).Value) = ""
For Each cell In Range(Cells(r, 14), Cells(r, 16))
If cell.Value <> "" Or 0 Then

' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)

' Set the appointment properties
myApt.Subject = Cells(r, 1).Value & " " & "Update Due"
myApt.Start = cell.Value
myApt.categories = "Yellow Category"
myApt.ReminderSet = True
myApt.Body = "blah blah blah"
myApt.Save
End If
Next cell
r = r + 1
Loop
End Sub

Please can someone assist by:
1. making it apply for all the columns originally mentioned
2. making it search if an appointment for that item (ie that row) exists - if not, create appt, if so, do not create appt and insert "Yes" in column 29

Thank... Read more

Answer:Excel Macro - Add Appointment To Outlook

HI,

Check this code you may be able to modify it.
Have not used it in a long time but....

Code:

Sub AddAppointmentsToCalendar()
Dim OLF As Outlook.MAPIFolder, objItem As Outlook.AppointmentItem
Dim i As Long, lngItemCount As Long, r As Long
On Error Resume Next
Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
On Error GoTo 0
If OLF Is Nothing Then Exit Sub

Application.StatusBar = "Adding appointments to Outlook..."
With wsSheet1
.Activate
r = Range("NewAppointments").Row + 1
Do While Len(Range("A" & r).Formula) > 0
On Error Resume Next
Set objItem = OLF.Items.Add(olAppointmentItem)
On Error GoTo 0
If Not objItem Is Nothing Then
With objItem
.Start = Range("A" & r).Value
.End = Range("B" & r).Value
.Subject = Range("C" & r).Value
.Body = Range("D" & r).Value
.Categories = Range("E" & r).Value
.ReminderSet = False
.Save
End With
Set objItem = Nothing
End If
r = r + 1
Loop
End With
Application.StatusBar = False
Sheets("Appointments").Select
Range(&... Read more

1 more replies
Relevance 64.78%

Hey guys, I am quite new to creating macros. I do have some primitive experience with code. My goal is to take 3 or 4 colums of information and put them into Outlook Calendar.

A- Discription B- Start -C Finish

So, in the Calendar in outlook it would show a discription and connect the dates in B and C to the days in the Calendar. I don't know if it can be done. At my current state I can't figure out how to get even one to move over. Any suggestions on where to start??

I am running Outlook and Excel 2003 all updated. On Windows XP Pro Service Pack 2.
 

Answer:Solved: Excel Macro to Outlook Calendar

16 more replies
Relevance 64.78%

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

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

Hi all,

I'm looking to create an Excel document and I need to put cells with dates in that are linked to outlook calendar reminders.

I.e I need a macro that when I click the link it sets the reminder on outlook for the date allocated in the cell.

could anyone please help me with this as i have never created a macro and would greatly appreciate any help you could give me.

Thanks

More replies
Relevance 64.78%

I need a macro that will 1) create a new outlook folder, then 2) create new Outlook distribution lists from data in an Excel sheet.

The Excel sheet has columns:
A - ID
B - DEPARTMENT_DESC
C - STUDENT_LEVEL_DESC
D - MAJOR_DESC
E - PROGRAM_DESC
F - EMAIL_ADDRESS
G - LAST_NAME
H - FIRST_NAME

The first entry begins in row 2 with row 1 being the column title.

I need a new distribution list to be created for each department (column B) and each student level (column C). (Column B will only ever be one of 6 values, column C will only ever be one of 2 values. Therefore, the macro should create 8 distribution lists.)

I have looked at threads similar to this question but I can't seem to get anything working.
 

Answer:Macro from Excel to New Outlook Distribution List

I saw something you might could work with here: http://www.helenfeddema.com/CodeSamples.htm
scroll down for sample 61. I haven't used it but marked it one day. I'm assuming your wanting to make local distb list? Not in AD.
 

1 more replies
Relevance 64.78%

Moved from Software Development
Hi, We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in to emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed so they could be updated by all. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I have updated an excel spreadsheet containing all the information and I would like to create a macro for all the team to periodicaly import this data in to Outlook Contacts and possibly create distribution lists from these. I trust this makes sense I am using Outlook 2003.
Thanks
 

More replies
Relevance 63.96%

Hello Everyone
I'm a pro atlete (quite iliterate in Biz Apps) that based on previous post (from Zack Baresse and computerman29642) has unsuccessfully tried to set up a code to merge my next year training calendar (xls format) into outlook. I herewith included the code I'm using and the test xls file I'd love to merge. Any help would be really appreciated
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = Startdate
' Row 2 = Finishdate
' Row 3 = Subject
' Row 4 = Location
' Row 5 = Body
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt

.StartDate = arrAppt(i, 1) + arrAppt(i, 2)
.Finishdate = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = arrAppt(i, 6)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.AllDayEvent = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub... Read more

More replies
Relevance 63.96%

Hi, I have a macro in excel that automatically updates my outlook calender - I use it to keep track of actions/deadlines/etc...

The macro runs beautifully except... whenever I run it it creates duplicates of appointments it has previously made in my outlook calendar. Is there any thing that I can add to the macro below to ensure it doesn't duplicate?

Really appreciate any help on this one. Cheers.
Sub outlookexport()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i
Set olApt = Nothing
Set olApp =... Read more

More replies
Relevance 63.96%

Hi there,

I have to prepare several daily emails which are prepared using a range of data (for example B3122) from Excel 2003 and then have to be sent using Outlook 2003. Unfortunately, due to various management requirements I am unable to merely attach the Excel file as an attachment or paste the Excel data into the email as a straight paste - I have to paste as Unformatted text and then manually change various lines of the email to Bold and Underlined text.
I am trying to write a macro that will do this automatically but am unable to get the Excel data pasted into Outlook (as unformatted text or otherwise). I am assuming that for the font changes to bold I will have to use a Find function in the macro.

The code that I have got so far is as follows:

Range("B3122").Select
selection.Copy

Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Subject = Range("B3")
olMail.Display

I am new to VBA and so I may be missing a really obvious solution but after trawling the net I have been unable to find an answer.

Many thanks to anyone that can help and make my work a lot less tedious!
 

More replies
Relevance 63.96%

Good Morning,

I have very limited knowledge when it comes to Macros and VBA.

What I need is one of two possible scenarios.

1. Information from a Training Assignment PivotTable exported to a shared calendar on Outlook.
2. Information from a Training Calendar bulit as a Macro in Excel exported to a shared calendar on Outlook and the ability to update either calendar if new information becomes available.

Don't know which is best using code via VBA or using the Export/Import functions.

Any help is greatly appreciated
 

More replies
Relevance 63.96%

Hi there!

So I've found the code below here: http://www.google.com/support/forum/p/Calendar/thread?tid=5e4909ca64cffe90&hl=en - Full credit goes to user 'tpuiatti'

Code:
Sub Generate_ICS()
Dim rng1 As Range, X, i As Long, v As Long
Dim objFSO, objFile
Dim FilePath As String
FilePath = "G:\Service.ics"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(FilePath)
Set rng1 = Range([a5], Cells(Rows.Count, "H").End(xlUp))
X = rng1

objFile.write "BEGIN:VCALENDAR" & vbCrLf
For i = 1 To UBound(X, 1)
objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
Next i
objFile.write "END:VCALENDAR"
End Sub

This code takes the info from 2 columns in Excel: A which is the event title and B which is the event date. Then it export these informations to an .ics file which will add the event dates to the Oulook Calendar.

The macro and the .ics work perfectly, however i wan't to make a few changes/additions to fit my needs. Additionally I wan't this macro to:

- Pick up dates from column &... Read more

Answer:Additional code to Excel macro - export to Outlook

7 more replies
Relevance 63.96%

Hi, I am looking for a macro to import contacts from excel. We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I would like to create the macro for all the team so that they can periodicaly update their contacts. I trust this makes sense
Thanks
Moved to Business Applications.
 

More replies
Relevance 63.55%

Ok, just a bit of advice needed on this one for the time being...
I need a macro that will:

- Save all (Excel) attachments from the currently open email message (or a message selected in the Inbox) to a directory on the PC.

- Merge all the saved Excel files into a single file - they're all formated exactly the same - data in about 20 columns, no column headers or anything, just standardised data.

- Save the merged file with a standard name (i.e. every time this is ran, it needs to have the exact same filename).

- Delete all the files in the directory apart from the merged file.

I know there's probably a fair bit of code online for doing the individual parts of this. Can I do both from within Outlook? It doesn't particularly matter if I need to do this in 2 steps - an Outlook macro to save the attachements, and an Excel macro to do the file merge etc.

Cheers.
 

Answer:Solved: MS Outlook / Excel macro - extract attachments and merge

16 more replies
Relevance 63.55%

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct
Job_xxx SUCCESS SUCCESS FAILED FAILED
Job_yyy SUCCESS SUCCESS SUCCESS RUNNING
Job_zzz MISSED MISSED CANCELLED SUCCESS

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.

Example:

- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

More replies
Relevance 62.73%

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

More replies
Relevance 62.73%

Hi,

We have a spreadsheet that keeps track of when our computer warranties expire. Would like to run a macro using the warranty expired date to create an appointment in Outlook. That part I understand. The problem is that when I go to run the macro again, it creates a duplicate appointment if the appointment already existed. Very frustrating!

This is the code below, any help would be appreciated!!!! Thanks
Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, dStartTime As Date, dDuration#

For r = 4 To 10

sSubject = Sheet1.Cells(r, 1).Value
sBody = Sheet1.Cells(r, 4).Value
dStartTime = Sheet1.Cells(r, 3).Value
dDuration = 30
Set o = GetObject(, "Outlook.Application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Service Tag= " & sBody
ai.Subject = "Warranty Expires for Server " & sSubject
ai.Start = dStartTime
ai.Duration = dDuration
ai.Close olSave

Next r
End Sub
 

Answer:Solved: Excel Macro to Update Outlook Calendar Creates Duplicates

6 more replies
Relevance 62.73%

Good Afternoon,

I'm quite stuck trying to fix my code. I've scoured the internet looking for similar questions, but to no avail. I am trying to have excel create appointments in the outlook calendar and be able to run the macro multiple times without have duplicate appointments.

I could run my macro and create an appointment, which worked fine, but there was no routine to check if the appointment already existed. So I would have multiple appointment alerts for the same appointment if I ran the macro more than once. (If I were to add something and run the macro again to keep everything up to date).

So I tried to add a loop checking first the date of the appointment then the title (subject). However, now, I am getting the "Object doesn't support this property or method" error when I run the following code.

If anyone could help me with what the error refers to specifically in my code or any code I need to add or take out to make my loop work I would really appreciate it. Thank you very much in advance.
Code:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

Dim olCalendarFolder As Outlook.MAPIFolder 'use MAPI to loop through folder
Dim olMessage As Object 'items in calendar/inbox are messages
Dim olInboxMessages As Object
Dim olAppSession As Outlook.Namespace
Dim strSubject As String
Dim myDate As Date

Applic... Read more

More replies
Relevance 62.73%

I am currently trying to create appointments in a calender i have created in outlook using information from an excel spreadsheet. I have copied several codes from other forums to help with this Macro but have only been able to add the appointments to my calender.

The code i am currently using is:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub
I also have the calender location but have no idea where it would need to fit into... Read more

Answer:Macro/VBE to create appointments in Outlook from an Excel Spreadsheet (Office 2003)

16 more replies
Relevance 62.32%

Hi,

I need help with a macro. So far the macro is operating as it should , creating MS Outlook 2010 calendar appointments using data from an excel data spreadsheet (data base). The issue I&#8217;m having is that every time the macro is run for a new entry, duplicate calendar appointments are created for the already existing data in the data base. I would like to have the macro do a search to ensure an appointment does not exist before creating one. I have read a few threads with similar problems but have had major issues incorporating the solutions to resolve the issue I&#8217;m having.

I have attached the macro in txt. format for reference.

Thanks for your help.

kkfonty
 

Answer:Solved: Excel macro updating Outlook calendar is creating duplicate appointments.

Hi
You can use this function to check if an appointment exists based on the Start Day/Time

Code:

Public Function CheckAppointment(ByVal argCheckDate As Date) As Boolean

Dim oApp As Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim oApptItem As Outlook.AppointmentItem
Dim oFolder As Outlook.MAPIFolder
Dim oMeetingoApptItem As Outlook.MeetingItem
Dim oObject As Object

On Error Resume Next
' check if Outlook is running
Set oApp = GetObject("Outlook.Application")
If Err <> 0 Then
'if not running, start it
Set oApp = CreateObject("Outlook.Application")
End If

Set oNameSpace = oApp.GetNamespace("MAPI")
Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)

CheckAppointment = False
For Each oObject In oFolder.Items
If oObject.Class = olAppointment Then
Set oApptItem = oObject
If oApptItem.Start = argCheckDate Then
CheckAppointment = True
End If
End If
Next oObject

Set oApp = Nothing
Set oNameSpace = Nothing
Set oApptItem = Nothing
Set oFolder = Nothing
Set oObject = Nothing

End Function

For this to work you need to have Tools/References/Microsoft Outlook xx Object Library enabled.
Then alter your macro like this

Code:

Private Sub cmdOutApt_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = "&q... Read more

2 more replies
Relevance 62.32%

Hi all... I just joined this forum as past threads have been of great assistance... But now I have a immediate issue... I have wrote some code to pull info from a Excel report and create an Outlook appointment base on various commitment dates... This report is sent out daily therefore the macro is used daily... An issue that has risen is that everytime the macro is ran it creates duplicate appoinments which quickly bogg down the server... I saw an example on a closed thread and could not quite figure it out... Can anyone assist??? I am running 2003 versions of both Excel and Outlook and my code is as follow and thanks...

Sub ExceltoOutlookCommitDateSmithRenewalReport()
'
' ExceltoOutlookCommitDate Macro
' Load Excel Commit Dates into Outlook
'
' Keyboard Shortcut: Ctrl+o
'
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

If MsgBox(prompt:="OK if YES / CANCEL if NO", _
Buttons:=vbOKCancel + vbQuestion, Title:="HAS PAA BEEN FILTERED?") = vbOK Then
MsgBox "MACRO WILL NOW UPDATE OUTLOOK CALENDAR WITH PROCLAIM COMMIT DATES."

Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste

' Start at row 5
r = 5
Do Until Trim(Cells(r, 1).Value) = ""
If (Cells(r, 20).Value) = "" Then
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 2).Value
If (Cells(r, 5).Va... Read more

Answer:2003 Excel to Outlook Calendar appoinment VB macro creates duplicates appointments

Hi jayJay,

I'm not going to look through the whole code but what I think happens is that sinec you run the macro every time it will just add an appoitment, I think an options should be added, something like an extra column namen DONE and when the macro is run the cell in the column DONE should be given a value True() or a 1 to indicate it as done, so that when you run the macro again the column DONE cells where the valkue is not True() or is not 1 (dependign on waht you choose) is carried out.
This way you will avoid duplication.
Just an idea, happy coding.
 

1 more replies
Relevance 59.04%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 56.99%

I am looking for help in writing a macro that will do the following.

With Excel 2003 open and the current workbook open. The macro should be able to save 3 variables from the current spreadsheet (some text, a value and a date ) The macro should open up Outlook 2003 as a task, attach the current workbook. In the Subject field of the task it should add the user's name, then the attached spreadsheet's filename then the text variable, then the value variable and finally the date.

Also it would be great if the Priority of the task could be set to High and the Start and Due dates set to 3 calendar days before the date variable.

Finally the macro would set a list of people the task is assigned to. The macro should NOT save the task as a check would need to be done manually.

I have tried unsuccessfully to write a macro in Outlook as I cannot seem to access the attachment filename or any data from the file. I have tried a macro in Excel which I can create a task and add some text to the Subject of the task but with no real progress on the attachment.

I have tried searching the net for help but a lot of code for Outlook is for the mail side of things rather than tasks and the little code snippets I have found have been of limited use. I have limited skills in programming Visual Basic and C so quite prepared to get my use code which does similar things
 

More replies
Relevance 55.35%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 54.94%

Im 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 54.94%

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

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 54.94%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 54.94%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 54.53%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 54.53%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 54.12%

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

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 54.12%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 54.12%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 54.12%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 54.12%

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

Hi,

Is it possible for an email to trigger an Outlook macro and then for that Outlook macro to trigger a macro, which is external to Outlook (and Office)?

I'm using Outlook 2007
 

More replies
Relevance 53.3%

A macro I wrote in VBA for Excel 2004 won't run in Excel 2011 (alas...). Upon opening the spreadsheet an "on open" routine runs, and bombs out on the following command:
Code:
Set cmdbar = Application.CommandBars.Add _
(Name:="Sort", _
Position:=msoBarTop, _
MenuBar:=False, _
temporary:=True) 'msobartop docks the menu at the top

Does anyone know if the VBA structure ("CommandBars.Add") has been removed or altered?

Is there another way to accomplish the same thing in 2011? I want to create a custom command bar (I populate it later in the module).
 

More replies
Relevance 53.3%

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

I have an equation that I need help with putting into an excel Macro. The equation is as follows:
=IF(AND(ISBLANK(E2),ISNA(F2)),"XXXXXX",IF(AND(ISBLANK(E2),ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNA(F2)),E2,IF(AND(OR(ISBLANK(E2)="FALSE",E2<>"000000",E2<>"111111",E2<>"999999",E2<>"IOIOIO",E2<>"XXXXXX"),OR(ISNUMBER(F2),ISNA(F2))),E2,"No Criteria Met")))))

I need this put into a macro as either an if then else statement or a case statement. If anyone could help me out i would appreciate it
 

Answer:Excel Equation converted to an excel macro

Not sure if this will work for you or not, untested...
Code:
dim e as range, f as range, c as range
set e = range("E2"): set f = range("F2"): set c = activecell
if e = "" and worksheetfunction.isna(f) then
c.value = "XXXXXX"
elseif e = "" and isnumeric(f.value) then
c.value = "AS SIGN LAN ID"
elseif e = "XXXXXX" and isnumeric(f.value) then
c.value = "ASSIGN LAN ID"
elseif e = "XXXXXX" and worksheetfunction.isna(f) then
c.value = e.value
elseif (e <> "" or e <> "0000 00" or e <> "111111" or e <> "999999" or e <> "IOIOIO" or e <> "XXXXXX") and (isnumeric(f) or worksheetfunction.isna(f)) then
c.value = e.value
else
c.value = "No Criteria Met"
end if
HTH
 

3 more replies
Relevance 53.3%

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

Hi

I am trying (without sucess) to write or find a macro that allows me to compare cells in a Excel (2007) spreadsheet.

The macro would work like this

if A1 was the target and A2 was the actual,

If A2 was greater or equal to A1 then A3 would be the differance in green
if A2 was less than 5% of A1 then A3 would be the differance in amber
if A2 was more than 5% of A1 then A3 would be in Red

Practical the result would be this

If the target A1 was 20 and the actual was 30 then A3 would read 10

If the target A1 was 20 and the actual was 19 then A3 would read -1

If the target A1 was 20 and the actual was 15 then A3 would read -5

I hope you understand this and someone can help, I would also need to repeat this for multipical pairs of cells


Please please help

Many Thanks
 

Answer:Excel Macro help please

Hi,

i am new to macros and to forums so forgive me if i am breaking rules or anything here.

i am trying to write a macro that scrolls the page up and to the right on each worksheet in the workbook.

here is what i have so far
Sub PageUp()
'
' PageUp Macro
'

'For Each ws in worksheets (array("sheet2", "sheet1")).SmallScroll ToRight:=15
Range("Z5").Select
ActiveSheet.Shapes.Range(Array("Smiley Face 9")).Select
Selection.OnAction = "PageUp"
Range("Z5").Select

End Sub
Please could somebody advise me on how to do this?

Many Thanks
 

7 more replies
Relevance 49.2%
Question: Excel macro help

_____ A_____ B______ C

1 ___ 888___ Apple___ Blank
2 __________Orange__ 888
4 __________Orange__ 888
5 __________Orange__ 888
6 ___999____Apple____Blank
7 __________Orange __999
8 __________Orange__ 999
9 __________Orange__ 999

Please advise how to write a macro or VB in excel using above table.
The given table data contant in A and B column. The result is in C column where it search in B column for "Apple" will be blank and "Orange" will be the value of A1 and A7.

I have try Vlookup and IF then statement but no luck. I can not get the value on the left of "Apple" to display in C column.

Please advise,
Thanks
 

Answer:Excel macro help

12 more replies
Relevance 49.2%
Question: Excel Macro

I have two workbooks (Book1.xls and V.xls) that I am trying to match names in Column A of both. When there is a match I need to copy the value from Column I with the corresponding matching row in Column A into Column C of the corresponding matching row and i need to loop this process until Column A in V.xls says DONE in the last cell.
AND I have no idea how to do this but I know it should be fairly simple for smart people.

Please help my fingers hurt from using alt+tab, ctrl C and ctrl V so many times as it is.
 

Answer:Excel Macro

6 more replies
Relevance 49.2%
Question: Excel Macro help

Hi guys, i was wondering if you could help.

I simply cannot get my macro working,

I need the macro to take the value from a cell, e.g. "D9" and search for it in a data set which is in A5 to A9, once found it then needs to offset by two collums and enter a number 1 into the column.

I know this sounds simple, but i have given up hope

Please help

Thanks in advance

Jon3sey
 

Answer:Excel Macro help

6 more replies
Relevance 49.2%
Question: Excel Macro Help

I need some help in writing a macro in Excel. Here's what I want to do: When a check box is clicked, I want the actual value of a text field to be changed to negative and the value to be displayed in the same text field not copied to another field as would be in an if statement. Please help!

Thank you,

M.
 

Answer:Excel Macro Help

catalina you already asked a similar question here....

http://forums.techguy.org/business-applications/700029-excel-help.html

bomb21 has given you a response....

don't double post
 

2 more replies
Relevance 49.2%
Question: Excel Macro Help

I don't know anything about visual basic, and have no programming experience, but am skilled with computers in general.

Would someone please help me with a Macro for Excel?

All I want is for it to send an email with an attachment (same email address, same attachment) every Monday-Saturday at 10:00 PM

Thanks!

(My email is [email protected])
 

Answer:Excel Macro Help

10 more replies
Relevance 49.2%

Need help with some Excel VBA as I am not very good a programming!
I have a long list of records in a single excel spreadsheet and I would like to move a whole row based on the the data content in one of the cells to a new workbook with the cell data as the name of the work book.

bouitac
 

Answer:Excel VBA/Macro Help

16 more replies
Relevance 49.2%
Question: excel macro

I am looking for a macro in excel spreadsheet, where in I have 2 columns one is ID and second is Type.

Requirement,

1. ID should get replace with Name

For example:

3300 = Shashi

2. It should validate the Name Shashi and then replace the row in front under column type with HP

Note: There are multipe ID and Type

Code which I am trying is solving requirement No 1 but not No 2 for type,

Sheets("Sheet1").Select
Cells.Replace What:="3300", Replacement:="Shashi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Any help is much appreciated
 

Answer:excel macro

8 more replies
Relevance 49.2%

Hello Gurues,

I need help creating a macro or something simular.

Situation: I got a spreadsheet w/ tons rows and columns of data. I would like to create a macro that will enable me cut and paste a particular sorted group and pasts in a new worksheet and naming the worksheet with the specific info that was paste. There are various groups that I would new a new worksheet for ie (cash, checks, money order, etc). How can I go about this?

Thx for any help provided.
EWart
 

Answer:Help With Excel Macro or VBA

7 more replies
Relevance 49.2%

I'm using a piece of software that automatically sends data from a register in a PLC straight into Excel, all you have to do is reference the program as below:

=cslite|net1_Node1!'R345'

'CSlite' is the name of the software and 'net1_node1!' is the PLC, this part never changes, I just change the R345 to whatever register I want.

I hope this makes sense so far?!

The register value changes every second so the excel document is constantly changing when left alone! What I want to do is get the value copy it to the next line and then insert a line ready for the next value. I've managed to get the copy and insert parts working but I need it to loop round and keep going whenever the value changes, basically creating a self updating table.

Any ideas how I can do this? I'm not too hot on VB and the functions I've tried seem to either create an infinite loop or just don't work!

Any help gratefully recieved.

Mike.
 

Answer:Excel Macro Help please!

Here is my suggestion:

I would keep your original formula =cslite|net1_Node1!'R345' in a particular cell such as cell A1. Now take the first value you see for this formula and manually enter it into cell A3 (this is to manually create a blank row between your dynamic cell A1 and your static list which begins in cell A3) Next add a macro to the the Worksheet_Calculate event. This macro will fire everytime the value in A1 changes. The macro would copy the current value from cell A1 and place it in the next available cell below. In this case, the next available cell would be cell A4.

Right click your worksheet name tab and select VIEW CODE and then copy and paste the code below into the worksheet module. BTW....how often does the data change and how big do you expect this table to get. I'm asking because you'll run out of rows sooner or later.

Code:


Private Sub Worksheet_Calculate()

Range("A65536").End(xlUp).Offset(1, 0).Value = Range("A1").Value


End Sub



Rollin
 

2 more replies
Relevance 49.2%
Question: excel macro

Hi,
Everyday, I get a file with a list of items and thier prices.How would i create a macro in EXCEL to automatically copy the same data to four regions. This can be anywhere in the sheet and the item and price need to be in the same columns.For example:
item# price
5555 10.00
6666 20.00
7777 30.00
8888 40.00

I would have to do the same with regions 1,2,3,and 4
item# region price
5555 1 10.00
6666 1 20.00
7777 1 30.00
8888 1 40.00
5555 2 10.00
6666 2 20.00
7777 2 30.00
8888 2 40.00
5555 3 10.00
6666 3 20.00
7777 3 30.00
8888 3 40.00
5555 4 10.00
6666 4 20.00
7777 4 30.00
8888 4 40.00
 

Answer:excel macro

6 more replies
Relevance 49.2%

Excel 2013:

Hi Guys,

I have a simple excel macro that lopps through showing the sheets in a spreadsheet. All works well except once running I can't stop it.
Esc doesn't work
Ctrl Break doesn't work

Ideally I would like some code where I could assign a button to stop the macro running.

Sub StartSlideShow()
Application.OnTime Now + TimeValue("00:00:02"), "ShowNextSheet"
End Sub

Sub ShowNextSheet()
Dim lastIndex As Integer, nextShtIndex As Integer

If ActiveSheet.Index = Worksheets.Count Then Worksheets(1).Select

lastShtIndex = Worksheets.Count
nextShtIndex = ActiveSheet.Index + 1

If nextShtIndex <= lastShtIndex Then
Worksheets(nextShtIndex).Select
StartSlideShow
Else
MsgBox "End of slide show"
End If
End Sub
 

Answer:Help with Excel macro

7 more replies
Relevance 49.2%

Hi Excel expert,

Please help me with a simple excel macro formula. I am trying to write a macro that will eliminate duplicates. example on column A1- A1000 I have to 50 of the same name or items within that range. I am trying to eliminate all duplicate of the same name or item. Can someone please help? My spreadsheet is like 16,000 rows and It's taking me way too much time eliminating the duplicate one by one with the same column.
 

Answer:Excel Macro help!!!

Hi, welcome to the forum.
Is it just one column of data or more, with this I mean the whole sheet, I undrstand that your duplicates are in one column
You could sort the date then all duplicates will be under each other but if there a lot then a macro could come in handy.
 

2 more replies
Relevance 49.2%
Question: Excel macro

Excel macro

A friend has a spread sheet he receives every week. He has to copy info from that sheet and past it into a new Excel sheet in a different order and then save the new sheet as a "prn" file.

He wants to create a macro (or two) that will do as much of the following as possible.

The following cells are always the same.

He wants to copy the following cells and then paste each cell area below the previously pasted cell area with one blank cell between each cell area.

The "pasted" info is then saved as a "prn" file.

Here's the cell info he's coping.

D7-F18
D21-F32
H7-J18
H21-J32
L7-N18
L21-N32

I can create a macro but this is out of my league.
Questions? Solutions?
 

Answer:Excel macro

7 more replies
Relevance 49.2%
Question: Excel Macro Help

I bought a 50,000 name phone fax list from experian. The list came with the phone numbers in the format of: 9092248212.

Winfax, my fax program, does not work with that format and experian will not change it for me. I need to build a macro that will strip the first 3 numbers (the area code) and either put that in a separate column or change the format to: 909-224-8212 (preferably instruction to do both if that is not WAY too much to ask)....

I am getting kind of desperate! Any help given will be (GREATLY) appreciated. I have used Excel since it was invented but this kinda throws me....

Regards,
Jim K
[email protected]
 

Answer:Excel Macro Help

6 more replies
Relevance 49.2%

Hey everyone,
i need some help with vb/macro in excel
i got two integers, lets say Value1 and Value2.
Value1 = 2
Value2 = 4.
i want the macro to goto a cell where the column is Value1 and the row is Value2.

is it possible?
 

Answer:Excel VB/Macro help

7 more replies
Relevance 49.2%

I have a macro written in VB that works but not "perfectly".
Basically it looks at a range of cells and replaces X text with Y text. Very simple.
The problem that I have is I would like for it to replace the text with a literal match.
For example:
Replacing "B" with "BOMB" on a row that has "BOMB" on it would result is "BOMBOMBOMB"

What happens is it takes every "B" and replaces it with "BOMB".
What I would like it to do is to replace cells that have literally only "B" in them with "BOMB".
If a cell has anything else than just "B" it is ignored.

Here is the code:

Code:

Sub Replace_ALL()
Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
Cells(r, c).Value = replace(Cells(r, c).Value, "B", "BOMB")
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

Answer:Excel VB Macro help...

Here's a quick and dirty solution, just test for the length of the cell before substitution:
Code:
Sub Replace_ALL()

Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
If Len(Cells(r, c).Value) = 1 Then
Cells(r, c).Value = Replace(Cells(r, c).Value, "B", "BOMB")
End If
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

2 more replies
Relevance 49.2%

Hey guys I am working on a spreadsheet for this cinema thingy at school and I wannaget top marks sooo can any of you tell me or show me how to make a macro to reset certain cells ie B2-B5 by reset i mean change the number to 0 please help I rlly need help i cant write macros lol
 

Answer:Excel macro help please

6 more replies
Relevance 49.2%
Question: Excel macro Help!!

Hi Guy's
I#m very new to excel programming and I need to create an Excel macro for a small application. I have a drop down list created in Excel and I need to assign a macro for the selected drop down list.

Sheet1: Drop-down list with three Items,
Item1
Item2
Item3

Sheet2:
Col1Row1 (data), Col2Row1 (Item1 --> Actually same Item name as drop down list)
Col1Row2 (data), Col2Row2 (Item2--> Actually same Item name as drop down list)
Col1Row3 (data), Col2Row3 (Item3--> Actually same Item name as drop down list)

In Sheet1 I need to create a macro that,

When I select the Item1 from the drop down list and if it matches with the name in cell Col2Row1 then I need to copy the cell Col1Row1 data into Sheet1.

Simillarly when I select the Item2 from the drop down list and if it matches with the name in cell Col2Row2 then I need to copy the cell Col1Row2 data into Sheet1.

Please help me out!!

Thank You.

-Arun
 

More replies
Relevance 49.2%
Question: excel macro

Hi

I have been playing around with a macro I was shown at College many moons ago before I realised I would never be any good at VBA.

The macro simply unhides a protected worksheet and copies it to the end of the workbook, then displays an input box asking you to name the sheet.

After remembering why I was no good at VBA for a few hours, I got it all working except 1 thing....The new worksheet won't move to the end. The first one does, but the rest go in front of this one.

The vba code follows, I suspect the problem is with the "Copy After:=Sheets(10)" line, but have lost patience.
.....................................................................................................
Dim NewPageName As String
Sub NewPage()
'
' NewPage Macro
' Macro recorded 28/01/2003 by
'

'
Sheets("Master").Visible = True
Sheets("Master").Copy After:=Sheets(10)
Sheets("Master").Select
ActiveWindow.SelectedSheets.Visible = False
NewPageName = InputBox("What would you like to call your new Worksheet")
ActiveWindow.ActiveSheet.Name = NewPageName
End Sub
.......................................................................................................
 

Answer:excel macro

6 more replies
Relevance 49.2%
Question: Excel Macro Help

HiI have 2 problems with an excel macro that I inherited many years ago and it seems a little too complex for my limited knowledge of macros.The first problem is that the macro takes a spreadsheet that has been sorted by column A (in this instance that is Supplier Names)and splits the files at each new supplier name and saves. This gives me circa 60 new individual files. This works perfectly and is easy to use. However, this macro is used at least once per week and the output files all need to be saved. Since the new file names are basically just a reflection of column A data, I manually need to rename the previous weeks files before I can run this. I am looking for a way to differentiate the current weeks files from the previous weeks using the macro. The easist way I can think is to have the date added to the end of the supplier name each week but I can't find a way to add this into the macro. The second problem is that there is an additional file that is supposed to record all the file names to allow the files to be sent via email but the instructions are not clear on how to set this up, where to record the email addresses etc. Any insight in to either of these problems would be very much appreciated.Below is the macro detailsim efile As StringPublic continue As BooleanFunction GetColumn(mColumn) Select Case mColumn Case Is >= 53 GetColumn = "B" & Chr(mColumn + 12) Case Is >= 26 GetColumn = "A" & Chr(mColumn + 38) Case Else GetColumn ... Read more

Answer:Excel Macro Help

7 more replies
Relevance 49.2%
Question: EXCEL macro's

OK here is my problem i have i would like to select a range of cells then copy them on to a new sheet. the problem is that the range is different every time due to qty. of items. But the 10 row header before & the symbol " " at the end is consistent.

I would like all the row's between the header and the symbol. any suggestions
 

Answer:EXCEL macro's

Stop posting duplicate threads! You already have posted at least three of these! Stick to one thread and just maybe it'll last long enough to get solved!

Continue here: http://forums.techguy.org/business-applications/532303-excel-usage.html
 

1 more replies
Relevance 49.2%
Question: Excel Macro Help

I have a complex database file that the users require to be in excel. I have created a master part list and the attached sheets in the workbook are for loading Bill of Materials (BOMs) into. The attached BOM sheets have formulas which reference the Master Part List via VLOOKUP formulas and there are also complex macros. I would appreciate help with some or all of the following:

1)find the lowest price on the parts master
2)create an order of operations with which to populate one column on the BOM sheet (for example: first search for most recent price, if not then lowest price, if not then a particular vendor, etc...
3)create buttons to execute 2)

I know I am asking for a lot and if anyone can help with a small fragment of the above, I would be so eternally grateful!!!
 

Answer:Excel Macro Help

Please don't post duplicate threads, you're already being helped here: http://forums.techguy.org/business-applications/529763-excel-database-help.html
 

1 more replies
Relevance 49.2%
Question: Excel Macro's

Hello,

I have developed an approx 50 page (work tabs) spreadsheet in Excel 2003 (Windows XP).

The workbook contains three main macro?s and after several manual inputs are performed the salaries for the organization are projected.

When I send out the report to the applicable parties, I hide the other 49+ tabs and then protect the workbook with a password to ensure the other parties cannot see the other areas information.

Due to the size/complexity of the document I am unable to delete the tabs (takes a long time) and that is why I have hid the rest of the tabs.

Each tab runs on the three main macros and the figures that are generated are rolled up in summary pages for the applicable areas (these summary pages do not used the macros)

Question #1: in a complex workbook is there a way to delete the tabs quicker?

Part 2:

A majority of the parties still use Windows 2000 (Excel 2002) due to other applications that are based around this operating system.

They are able to update/save the report and any changes that are made are accurately reflected. However, when the Excel 2002 user saves the document and sends it back to me it takes an extremely long time (45 mins) for their report accurately show the figures. This message occurs in any cell that requires the macro:

#NAME?

Another piece of information that I obtained said:

Microsoft Office Excel re-calculates formulas when opening files last saved by an earlier version of excel.

This leads me to believe that since the... Read more

Answer:Excel Macro's

Your problem reads like there's a lot of unstated constraints in your situation, like resources and procedures for distribution, management, return, collation. Too much for me. I'm looking here just at a more efficient message setup operation.Sounds like much of your overhead irritation relates to bothway transmission of large quantities of irrelevant (even though hidden) data. Here are three lines of thought:1. Another macro to generate 50 individual spreadsheets from your workbook.2. Rework your base dataset in EXCEL to bare-bones tabular form, to enable mailmerge into a user-friendly WORD template. I've done this with 50 data fields into a 20-page 85-client report template, and it's a ho-hum WORD mailmerge task once you get the master doc looking pretty.3. Take this on to include the transmission stage with the article "Integrate mail merge in Microsoft Word with Excel" at TechRepublic, at http://techrepublic.com.com/ The direct approach http://search.techrepublic.com.com/index.p...l&go=Search might even work.I'm afraid any of those options is a week's work, but that's why we're paid so well.

1 more replies
Relevance 49.2%
Question: Excel Macro?

Hi,

I have to adjust a database that is in the incorrect format required for statistical analysis, and I hoped to record a macro to help me out. My experience with this is very limited, and I fear that this is too great a task for me! Could someone please help out? What I need is for my current data, which looks like this:

a b c d e f g h i
to look like this:

a d g
b e h
c f i

Basically, the data is grouped into three, and I need every 2nd and 3rd bits of information to be transposed into a column under the 1st bit of information.

Can anyone help me?

Many thanks!
 

Answer:Excel Macro?

I should mention that this is a good sized database, . . . >1000 rows and >150 columns, . . . don't know if this changes anything, . . .
 

2 more replies
Relevance 49.2%

Hi,
Could someone tell me if a macro would be capable of the following?
A file contains a number of spreadsheets, each of which has three columns of unique data. I would like each set of data (=3 columns) listed all together on one spreadsheet (preferably in a different file), separated by an empty column for clarity. Previously I've just copied and pasted the relevant columns, but now the number of spreadsheets per data file is upwards of 40, and it is becoming highly non-productive.

I have looked online for macro and VB coding help, but I think I am a bit out of my field...I know a bit of HTML, but this seems very different.

Thank you for any help,

nezhka
 

Answer:Will a macro do this? (Excel)

Yes, a VBA macro will do it, but you'll have to work at it a little. The following site has some excellent help:

http://www.xl-logic.com/

Included on that site are two examples that will give you most of what you need, but you'll have to tailor it to your requirements. Click on MAIN MENU, then VBA Code (Macros). The two files you want to download are:

consol loop.zip
consol mckillop.zip

They contain VBA scripts which should give you the basics for what you need. If you're not too familiar with VBA, then stick with something like in the consol loop script. It's simple, fast, and not too difficult to understand.
 

2 more replies
Relevance 49.2%
Question: Excel Macro Help

This is what I have so far...

what I need should be just after auto open and before running the "newinvoice' macro.

I need to do something like...

if current workbook filename is not equal to "invoice.xls" then goto end (do NOT run "newinvoice")
OR

if current workbook = "invoice.xls" then run "newinvoice" else goto end (do NOT run "newinvoice")
if somebody could put the above into proper macro code please???


Sub Autpen()
' this macro will run every time the workbook is opened
Worksheets(1).Activate
Application.OnSheetActivate = "NewInvoice"
End Sub

Sub NewInvoice()
'
' Keyboard Shortcut: Ctrl+n
'
Dim x As Integer
Workbooks.Open Filename:="d:\number.xls"
x = Workbooks("number.xls").Worksheets("Sheet1").Cells(1, 1)
'
ThisWorkbook.Worksheets("Invoice").Range("n4:n4") = x
x = x + 1
Workbooks("number.xls").Worksheets("Sheet1").Cells(1, 1) = x
Workbooks("number.xls").Save
Workbooks("number.xls").Close
myFile = [n4] & ".xls"
ThisWorkbook.SaveAs myFile
End Sub
 

More replies
Relevance 49.2%

Hi,
I have been working on getting multiple files to open in excel and maybe even organise the data in the format i want. A number friends have helped and here is what I have:

Public Sub open_files()
Dim intIndex As Integer
Dim fsSearch As FileSearch
Dim fsoFile As FileSystemObject
Dim tsmContents As TextStream
Dim strPath As String
Dim wksSheet As Worksheet
Dim intCount As Integer
Dim fdDir As FileDialog

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fdDir = Application.FileDialog(msoFileDialogFolderPicker)
fdDir.Show
fdDir.AllowMultiSelect = False
strPath = fdDir.SelectedItems(1)
Set fdDir = Nothing

Set fsoFile = New FileSystemObject
Set fsSearch = Application.FileSearch
fsSearch.LookIn = strPath
fsSearch.Filename = "*"
fsSearch.Execute
If fsSearch.FoundFiles.Count > 0 Then
For intIndex = 1 To fsSearch.FoundFiles.Count
intCount = 0
Set tsmContents = fsoFile.OpenTextFile(fsSearch.FoundFiles(intIndex))
Set wksSheet = ActiveWorkbook.Worksheets.Add
wksSheet.Name = fsoFile.GetFileName(fsSearch.FoundFiles(intIndex))
Do
Call Import_Contents(tsmContents.ReadLine, intCount)
intCount = intCount + 1
Loop Until tsmContents.AtEndOfStream
Next intIndex
End If

Set fsSearch = Nothing
Set wksSheet = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

The trouble is the code is not really working in my worksheet as I keep getting compile errors.

AS I described before, I want the... Read more

Answer:Help with Excel macro

What does the Compile error tell you?
 

3 more replies
Relevance 49.2%

Hi, I am new to macros and I am trying to make a macro for a spreadsheet to delete some columns, change some font sizes, and row heights. This spreadsheet has to be adjusted daily for date and data changes. I recorded the macro and when I go to replay it back after I reopen the macro it deletes all the info on the spreadsheet while it adjusts everything. How to I keep the information of the spreadsheet? This is what I have so far.

I messed up a few things when running it through but the end result is the same.

Please help!!!
Thanks,
Tim

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/14/2009 by tsmits
'
'
Columns("B:B").Select
Range("B2").Activate
Selection.Delete Shift:=xlToLeft
Columns("D").Select
Range("D2").Activate
Selection.Delete Shift:=xlToLeft
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("C3").Select
ActiveCell.FormulaR1C1 = "NAV Change"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial "
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B2").Select
ActiveWindow.SmallScroll Down:=45
Rows("63:82").Select
Selection.Delete Shift:=xlUp
Range("A65").Select
ActiveWindow.SmallScroll Down:=-54
Cells.Select
With Selection.Font
.Size = 8
.Strikethr... Read more

Answer:Excel Macro Help!!!!

6 more replies
Relevance 49.2%
Question: excel macro

hi evbody,
I frequently need to use cut copy function in microsoft excel.Is it possible to create a Macro in excel using VB so that i could avoid the repetitive tasks.
I need to copy data from my excel sheet into a software application?(live server)
Is there any ready macro available in excel or i need to write the VBA code for it
Rgds
Ojas Nafday
 

Answer:excel macro

7 more replies
Relevance 49.2%

Ive to convert Excel macro which is written in 95 to Excel 2000. Ive no idea where to start or how to go about to do this. Any pointers/ideas/suggestion is greatly appreciated.

Thanks,
Helen.
 

More replies
Relevance 49.2%
Question: Need excel Macro

Good dayI need help creating an Excel Macro. I have some names in a column, followed by some numbers in the next column. I need to have Excel copy the names and numbers for each, in some other cells.Since I am a beginner I do not know of the basic commands so even this IF then is a mistery to me. If anyone can help explain how it should be written, I can do the rest. Thank you.

Answer:Need excel Macro

You'll need to supply some more information, perhaps with some "before and after" examples of your data.Data can be "copied" with simple formulas, so I'm assuming you need something more sophisticated than that.Before you post any data, please click on my signature line and read the posting instructions available via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 49.2%
Question: Excel Macro Help

Hi All

Just started using Macros last week, so not an expert but can understand the very basics. I have written the following code below, and it works except for when it gets to th bits highlighted in red. It then asks for an update on values but I can't understand this.

Thanks in advance for any help!!

ActiveWindow.SmallScroll Down:=-21
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=-31
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Range("R1").Value, Destination:=Range _
("$A$1"))
.Name = "MItest1204"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = &qu... Read more

Answer:Excel Macro Help

No bother, I've found the answer.
 

5 more replies
Relevance 49.2%
Question: Excel macro??

I have a problem in Excel which I assume can be solved with a macro, but I cannot find the right solution.

I want to set up a template with a 4 character code (such as abce or a134) going into a particular cell - say cell b4. I want this then to set up a file in a folder in my documents with the code in cell b4 being the file name. The aim is that I should only have to press Save when the rest of the spreadsheet has been assembled. It is not a big spreadsheet - a single page.

Hope this makes sense.

TIA
 

Answer:Excel macro??

6 more replies
Relevance 49.2%

I'm trying to create a macro that will look at a specific cell of a worksheet (B14) and then either print and close active worksheet, just close the active worksheet or look for second sheet and repeat the process depending on whether or not cell B14 contains text that says "no activity". I guess the tricky part is that sometimes there will be two worksheets (for example Contract 2009 and Contract 2010) and sometimes there will only be one. I need to incorporate an instruction to look for the second worksheet and again either print then close or just close. Not sure how (if even possible) to do this. Thank you in advance to anyone who can help me figure this out.
 

Answer:Need help with Excel Macro

Hi,
Creating a pivot table is a one-off activity, and the overhead of writing a macro to do it surely exceeds the work involved in just creating the pivot table.
I can suggest a macro for creating the worksheets and copying the rows.
Thanks
 

2 more replies
Relevance 49.2%

Hello,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have two colums A & B. I want to be able to find the value from Column A and replace it with the value in the same row in column B. The values are diferent sizes.

Thank You
 

Answer:VBA Excel Macro Help

Welcome to the forum microfire.

Can you provide a sample of dummy data, with a little more info - perhaps a before and after of what you want achieved.
 

1 more replies
Relevance 49.2%
Question: Excel Macro Help

I'm working on creating a macro in Excel to automate some of my daily tasks. For the most part, I was able to make a successful macro using only the record function. However, I ran into a snag when I tried to get the macro to record my Ctrl + F function. I don't have too much (read: any) experience working with either VBA or macros, so I was wondering if someone here could help me out. This is what I would like for the macro to do
1. Search for cells with the value "12345"
2. Highlight all of the cells with the value "12345"
3. Delete all of these cells, and shift the remaining cells up
Any help would be greatly appreciated!
 

Answer:Excel Macro Help

Hi and welcome to TSG

Here's a little macro for you:

Code:
Sub Delete_12345()
Dim Rng As Range, WS As Worksheet, DummyString As String

'this DummyString is just necessary, you will see why :)
DummyString = "gibhu, etho et&#337;zohketok&#337;u&#337;ou&#337;(=%/HN "

'modify the sheet and cell range as needed
Set WS = Sheets("Sheet1")
Set Rng = WS.Range("A1:J10")

'changing/comuflaging the empty cells by filling them with a dummy string
Rng.Replace what:="", replacement:=DummyString, lookat:=xlWhole

'clearing the cells we look for (change all "12345" values into empty string)
Rng.Replace what:="12345", replacement:="", lookat:=xlWhole

'deleting the new empty cells (note: they were 12345 values just a moment ago)
Rng.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp

'recreatig the original empty cells by replacing the dummy strings by empty strings
Rng.Replace what:=DummyString, replacement:="", lookat:=xlWhole
End Sub
I'm sorry for leaving out Step 2., but highlighting the cells with the value "12345" doesn't make much sense, if they are all to be deleted anyway...

Jimmy
 

1 more replies
Relevance 49.2%
Question: Excel Macro Help

Howdy all. Attached is a mock-up of a workbook I'm trying to assist someone with. It should be a pretty simple macro; we want to take only the negative numbers (in red) in col E and move them to col F; the only caveat is I need the rest of the data in each particular line to stay static.

I will be offline this weekend, but back early AM Monday. The person whom I am assisting may pop in and be able to answer questions over the weekend as well, but that is not confirmed.

Thanks in advance,

v
 

Answer:Excel Macro Help

7 more replies
Relevance 49.2%
Question: Excel Macro

I am new to macros. I have a excel spreadsheet for technical assistance. There are sheets for various sites and there is a summary sheet. There are columns asking for the date of request and the date of resolution. If there is a date of request and a date of resolution it means the issue was solved but if there is no date of resolution it means the issue was not resolved. The total number of resolved and unresolved have to be then entered in the summary sheet. I have this macro so far but I keep getting out of range error. Please help.

Sub Macro()
Dim R As Long
Dim S As Long

Sheets("Sheet2").Range("C9").Activate

If IsDate(Cell) = True Then
ActiveCell.Offset(0, 1).Select
If IsDate(Cell) = True Then
R = R + 1 'resolved issues
Else
S = S + 1 'unresolved issues

End If
End If


Sheets("Summary").Range("B9").Activate 'go to first sheet

Range("B9").Value = R
Range("B10").Value = S

ActiveWorkbook.Save


End Sub
 

Answer:Excel Macro

After posting the last message I was able to get the macro to go past the subscript errors but the macro is not calculating the value of R and S correctly...PLEASE HELP!!!!!!!!!!!!!!!
 

3 more replies
Relevance 49.2%
Question: Excel Macro Help

Good afternoon all

I am putting together a spreadsheet for some colleagues at work to complete each day. Each row is completed for every piece of work they complete (starting at row 6). In column K (cells K6 to K106), a colleague can enter 1 of 5 strings of text (cell has been validated) as follows: -

Car
Van
Motorcycle
Cycle
Other

What I want to do is make excel delete the value/text in the adjecent cell (the one directly to the right, ie L6 when K6 is completed) when the first 4 values above are entered.

For example, if the user puts "Car" in K55, I want all text in cell L55 to be cleared. If the user puts "Other", I want no action taken.

I know that I have to start and end the code with the below, but I am struggling after that. I have tried to define the Range and use the offset function but without success.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Any help would be greatly appreciated.

Mark
 

Answer:Excel Macro Help

9 more replies
Relevance 49.2%
Question: Excel Macro

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

Answer:Excel Macro

Hi, welcome.
A 'picture' tells more that a thousand words, but it's quite simple.

The other thing is; Which version of Excel are you using?
Code:

Sub CopyData2From()
Dim xRow As Long, yRow As Long
xRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
yRow = Sheets("From").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("A5:A" & xRow).Copy
Sheets("From").Range("A" & yRow).PasteSpecial
Application.CutCopyMode = False
End Sub
 

2 more replies
Relevance 49.2%
Question: Excel macro

Hi there. This is an excel macro problem. I want to write a macro that lets me do the following: As soon as I open up my excel spread sheet and the date is the 25 of any month, I want a message box to pop up that says "Today is the 25 of March (April, May, June whatever month it happens to be) please do the following tasks". I can do the message box with no problem. However, I'm having syntax problems on how to make visual basic recognize that it is in fact the 25th of the month. If it is not the 25th then no message box should pop up. Can anyone out there help me with this problem?
 

Answer:Excel macro

Marpin,
Try this:

Dim strDay As String

strDay = Day(Now())
If (strDay = "25") Then
MsgBox strDay
End If

Just replace Msgbox strDay with your Msgbox. The Now() function returns the current date and time, and the Day() function returns the day part of a date.
 

1 more replies
Relevance 49.2%
Question: excel macro

how to extract multiple excel files contents into one excel files , that all excel files are in one directory with directory reference plz anybody help, i need macro code
 

More replies
Relevance 49.2%

Hi,
I need some somewhat simple excel macro/scripting help.

Say I export a report from our database program into excel, basically, it is a 100+ page report with headings on each page. When we export to Excel, it shows the headings throughout. It is something like:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 2
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

:Actual Data:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 3
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

Etc.

What's an easy way to strip out that information so we have have concise spreadsheet? My guess, is: Search for the word "Database" and delete 4 lines above it and 5 lines below it.

Also,

When the report is exported to Excel, all the font formatting is stripped. So if we have lines like:

Office Supplies $300 (This is a total line, but doesn't state the word total)
Pens $100
Paper $200

Is there a way to search for a specific string, say I insert the word "total" to the certain lines, so it searches for that word "total" and it bolds entire land? Thanks for the help in advance, you guys rock!
 

Answer:More Excel/Macro help :)

16 more replies
Relevance 49.2%
Question: Excel, Macro, VB ?

I want to put a button in a spreadsheet. When a person clicks on the button, it will email me the spreadsheet automatically. THanks for any help.
 

Answer:Excel, Macro, VB ?

6 more replies
Relevance 49.2%
Question: Excel Macro

I am using Excel97. I have a twenty column worksheet with a combination of text and numbers. The data will be inputed by numerous sites and then sent to a central site for collation.
I have written a macro to do a specific sort automatically at the individual sites - Using column p (text) as Sort Key 1 and column q as Sort Key 2. Here is the part I cant figure out Column P is a mixture of blank cells and text entries. In order to sort the blank cells first I have the sheet replace the blanks with 1s and then I have it sort column P first and then column Q. But when that is done, I want the macro to ignore all the cells with 1s and switch so that the sort is done on Column Q first. After the first sort, can I then specify a range based on cell contents?
 

Answer:Excel Macro

7 more replies
Relevance 49.2%
Question: Excel Macro Help

Good Afternoon,
I was hoping to get help writing a macro that would look at cell A9 and run function GetCellComment('Master Pricing'!R[-6]C[-8]) and paste that into cell I9.

then loop to the next cell (A10 and paste into cell I10) until it encounters an empty cell in column A.

Thanks in advance for your aid.

/frank
 

Answer:Excel Macro Help

8 more replies
Relevance 49.2%

I have a report that gives me order numbers, that I then have to provide hyperlinks to the original file. I have limited space on the report, so am converting the full hyperlink into a short name. I also need a # days elapsed and data validation list for each row. I need to be able to do this for each entry in column A as the report can be quite large. Currently I have the macro set up to run after manual entry of the first hyperlink. I then have it copy the hyperlink from the first entry and use the SUBSTITUTE function to update it to correspond with the next entry. All work well. What I don't know how to do is set up the code to create a loop to perform the same action for each row there is data. Think I need a Do Until loop, but am at a loss where to go from here. Here's the code I have so far, it's not pretty but (almost) does the trick:

Manual entry of the first hyperlink, then:

Range("I2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[8],RC[-8])"
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With

And what I want to happen each row after:

Range("Q2").Select
Selection.Copy
Range("Q3").Select
ActiveSheet.Paste
Range("R3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],R[-1]C[-17],RC[-17])"
Rang... Read more

Answer:Excel Macro: Do Until?

I didn't read the code but something like this will go thru each row

intRow = 2
Do Until oExcel.Cells(intRow,1).Value = ""
' Execute some code...
intRow = intRow + 1
Loop

Setting 'intRow to 2 gets you past the header.

Do Until oExcel.Cells(intRow,1).Value = ""
is checking the first cell in each row for null.
 

1 more replies