Computer Support Forum

Excel 2007: Macro Quick Access Toolbar Not Working

Question: Excel 2007: Macro Quick Access Toolbar Not Working

First time poster so bare with me.

My issue is: My customised QAT in Excel 2007, that includes 'not in ribbon' buttons, doesn't save when I add a macro button for default or specific docs.

The macro button and the macro will work fine when I add it, but when I close and reopen the file or email the file, the button has gone

The bizarre thing is, it was working a few weeks ago. I even had some customised macro buttons in the file that I was trying to add more to, and when I saved and reopened the file, ALL the custom macro buttons disappeared.

I've already searched google for days now trying to find a solution.....

I've read ron debruin's site back to front and

The instructions are there for the process but no solution as to why the button wont stay put.

I'm running Windows 7, and obviously Office 2007. Also, this is a work PC on a network (I'm not the network admin). The network admin hasn't done anything to the system that he thinks would have done the changes i'm whinging about.

Any help, ANY!!!!, will be greatly appreciated.



Relevance 100%
Preferred Solution: Excel 2007: Macro Quick Access Toolbar Not Working

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 (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Excel 2007: Macro Quick Access Toolbar Not Working


I've either not explained myself properly or i've stumped even the brightest of PCReview forumers.

Guess i'll have to live with the issue

Thanks anyway

13 more replies
Relevance 75.69%

I am trying to get commands to stay in the quick access toolbar of excel 2007 but everytime i log off it resets. I am using citrix to run excel on a network. I was wondering if it was possible to get them to stay in the quick access toolbar?

Answer:Excel 2007 commands wont stay in quick access toolbar

Quote: Originally Posted by Bootz

I am trying to get commands to stay in the quick access toolbar of excel 2007 but everytime i log off it resets. I am using citrix to run excel on a network. I was wondering if it was possible to get them to stay in the quick access toolbar?

Hi and welcome.
I think your issue is directly related to Citrix and your network.
I think it dissapears because the actual link is not avaialble on bootup until you login. Also your network may have something already in place that doesn't allow it.
I know I may have not answered your question but just a few ideas.

3 more replies
Relevance 91.43%

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

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

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

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

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

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

7 more replies
Relevance 88.97%

This is one of those things where you think you are remembering correctly but maybe not. I opened Outlook 2007 this morning and the Quick Access Toolbar isn't there. Now I am wondering if it ever was?I could have sworn the Quick Access toolbar and Ribbon was available not just in a new or open email message or calendar item, etc., but also from the main Mail, Calendar, Contacts, and Tasks views.Thank you.

Answer:Restoring the Quick Access Toolbar in Outlook 2007

Not on mine it isn't. I think that your set up is normal.

1 more replies
Relevance 88.15%

I have created a macro in powerpoint that should run when the user clicks on a button i have attached to the quick access toolbar.
The problem is when i send the file to a user and they open it the button is not there.
I have done this with excel and in order to get it to work I had to convert the file to a zip then edit an xml file, but havent been able to do the same thing with the powerpoint file.

Any help would be much appreciated,


More replies
Relevance 86.51%

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

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

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

Thanks In advance!

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

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

1 more replies
Relevance 85.69%

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

More replies
Relevance 74.62%

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

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

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:

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

Does anyone have a better solution?

1 more replies
Relevance 74.62%

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
.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 73.8%

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

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

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

2 more replies
Relevance 73.8%

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

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

Can anyone help?


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

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

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

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

1 more replies
Relevance 73.39%

I would like define variable as a cell location. Simply shown

Dim x as Integer
Range ("A1:Ax").Select
Range ("B1:Bx"). Select

How do I get it to accept x as integer

Range ('A1:A1000").Select
Range ("B1:B1000").Select

Answer:Quick Excel Macro Question

Range("A1:A" & x).Select

Although physical selection of a range is inefficient and rarely necessary. For example:

Range("A1:A" & x).Copy Range("B1")

1 more replies
Relevance 72.57%

I have a worksheet from which I pull a percentage each month that's populated via an online database. Each month, another column is added to the end, so if Nov's data is in column F then Dec's will be in G.

I wrote a macro to pull this data (which take up a couple hundred rows per column), but each month now I have to cut and paste the new month's data into the column the macro looks at. Thus, if the new month's data is in column Z, but when I wrote the macro it pulls from T, then I have to cut and paste the data from Z into T before kicking off the macro.

Is there a way to have the macro look at a column incrementally farther over each time it's run, or something like that? After all, I only run the macro once a month like clockwork.

I figure I could have a prompt ask me which column the latest data is in, and then have that variable dictate the column to pull data from for the rest of the macro, but I wonder if there's an easier way.

Thanks in advance for any help.

Answer:Solved: Excel: Quick Macro issue

How about checking the last column filled?

Range("IV1").End(xlToLeft).Column returns the nr of the last column filled in row 1
The IV value is for Office 2003
The latest version goed to something like XXA or so

2 more replies
Relevance 72.57%


Ok, i will try to be short, quick and precise. I need a macro or VB code to format the "SignOn.xls" excel spreadsheet to look like the "SignOn_AccessExport.xls" they are both in the zip file. If you need more information please let me know.

I am doing this so that I can easily import this xls into an Access Database.

Thanks in Advance for any help.


Answer:Calling All VB/Excel Macro Experts - Need Quick Help!

15 more replies
Relevance 72.57%

I have a access database thAT exports data to an excell file. A macro in access then opens this file and another in excell. The second excell file reads data from the first then uses a macro to format it properly and print the info. Right now I can only run this macro from the excell file. Can I run this macro (called Button2_Click in Module 3) from access? I have been using the OutputTo and RunApp action to run this macro so far.

Answer:Solved: How do I run an excell 2007 macro from access 2007?

11 more replies
Relevance 72.16%

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

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

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

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

More replies
Relevance 72.16%

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

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

Answer:excel 2007 won't open excel 4.0 macro

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

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

1 more replies
Relevance 71.34%

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

Answer:Excel 2007 Macro - HELP

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

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

On Error GoTo ErrHnd

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

'get name of this worksheet
strSrcWSName = ActiveSheet.Name

'set sta... Read more

3 more replies
Relevance 71.34%

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

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


Answer:Excel 2007 Macro help

10 more replies
Relevance 71.34%

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

Jan Feb March April May June Total To Date Projected Total

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

More replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

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

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

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

Thanks in advance!

Answer:Excel 2007 Macro

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

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

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

2 more replies
Relevance 71.34%

View attachment 212815

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


Answer:Macro help for excel 2007

6 more replies
Relevance 71.34%

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

Answer:VBA - Excel 2007 - Macro

16 more replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

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

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

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


Answer:Excel 2007 Macro

11 more replies
Relevance 71.34%

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

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

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

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

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

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

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

Answer:Excel 2007 Macro Help

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

Sub Book1()

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

For i = 1 To 30

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

Next i

For j = 1 To 10000

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

End Sub

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

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

2 more replies
Relevance 71.34%
Question: Macro Excel 2007

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

More replies
Relevance 71.34%

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

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

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

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

Answer:Excel 2007 Macro Help

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


2 more replies
Relevance 71.34%

Hi all,

A few days ago, I purchased a new 19" widescreen monitor, and have a question regarding Office 2007.

The resolution of the monitor is 1680 x 1050 32-bit, and when I open up any of the Office programs, the Quick Access Toolbar icons are small. I have been unable to find how to use larger icons with the Office 2007 programs. Is there (if at all) any way to use larger icons in the Quick Access Toolbar?

Mike T.

More replies
Relevance 70.52%

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

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

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

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

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

Answer:Excel 2007 Macro Sum 2 Columns

13 more replies
Relevance 70.52%

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

Red, Gold and yellow with Green Stripes

The final result:

Answer:Solved: Excel 2007 Macro Help

13 more replies
Relevance 70.52%

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

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

Many thanks in advance!

More replies
Relevance 70.52%

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

Answer:print macro excel 2007

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

2 more replies
Relevance 70.52%

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

Answer:Graph Macro in Excel 2007

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

4 more replies
Relevance 70.52%

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

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

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

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?


More replies
Relevance 70.52%

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

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

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

With a Result output

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

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

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

Answer:Solved: Excel 2007 Macro help

8 more replies
Relevance 70.52%

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

Answer:Button and Macro Help in Excel 2007

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

4 more replies
Relevance 70.52%

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

Answer:Excel 2007 Macro Question

9 more replies
Relevance 70.52%

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

Can anybody help me?

Answer:Solved: VBA Macro - Excel 2007

16 more replies
Relevance 70.52%

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

Answer:Recording macro in excel 2007

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

7 more replies
Relevance 70.52%

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

'inserts the word end below the last data

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

Answer:Excel 2007 macro problem

Morning and Welcome to the PCReview Forum!

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

7 more replies
Relevance 70.52%

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

Sure hope someone can help.

Here is a sample from the macro:

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

Answer:Excel 2007 Macro Issue

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

FileFormat:=xlNormalClick to expand...

So that it reads

FileFormat:= xlOpenXMLWorkbookClick to expand...

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

FileFormat:= xlOpenXMLWorkbookMacroEnabledClick to expand...


2 more replies
Relevance 70.52%

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

Answer:Excel 2007 How to automatically run a macro

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

'Your code goes here.

End Sub

3 more replies
Relevance 70.52%

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

I have Windows Vista installed.

Thanks in advance!

More replies
Relevance 70.52%

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

Answer:Excel 2007 Hyperlink Macro

7 more replies
Relevance 70.11%


In a couple of my databases, whilst in Form view, entering records, I regularly need to use the calculator, so I thought I'd save myself a couple of clicks by creating a macro (using the RunApp action) to open it.
I created a nice icon for it, and palced it on the Formatting toolbar, and then copied the macro to the 2nd database. All fine and well.

However, I realised that although the icon has been added to the Access template, the macro has not - I guess it would have worked in Word or Excel, but evidently not in Access.
So I'm just wondering if:

a) the macro can be stored, as with Word and Access, in the application template, or

b) if I can surpress the icon from appearing in all databases but these 2

I don't really want to have an extra toolbar with just one icon on it (which is why it sits on the Formatting toolbar).

Any ideas?


Answer:Access 2000: Macro on Toolbar Button

8 more replies
Relevance 70.11%

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:
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()
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
End Sub
Sub GetData(fl)
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???...

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
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
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 70.11%


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

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

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

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

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

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

Please help.

Answer:Solved: excel 2007 macro error

7 more replies
Relevance 70.11%

Hi All,

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

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

Thank you!


Answer:Solved: Excel 2007 Macro Question

9 more replies
Relevance 70.11%

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


Answer:Excel 2007 Macro for Find and Replace

7 more replies
Relevance 70.11%

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

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

Answer:Excel 2007 Macro/Folders & Files


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

Sub Get_File_Names()

[B]'1st directory[/B]

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

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

[B]'2nd directory[/B]

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

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

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

2 more replies
Relevance 70.11%

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

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

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

Answer:Excel 2007:Toolbars and Macro Problem

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

Hope this helps

6 more replies
Relevance 70.11%


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

TIA for your ideas!

Option Explicit

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

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

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

Try something like this....

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


2 more replies
Relevance 70.11%

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

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


Answer:Solved: need help writing macro in excel 2007

16 more replies
Relevance 70.11%

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

More replies
Relevance 70.11%

Hi All.

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

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

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

I have attached the worksheet in question



Answer:Excel 2007 Sort Macro Problem HELP!

7 more replies
Relevance 70.11%

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

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

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

Answer:Solved: Dynamic Excel 2007 Macro

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

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

Public Sub test()

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

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand

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

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

totalExp = lrow + 2

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

Coach = ActiveCell.Row

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

Direct = ActiveCell.Row

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

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

End If

End Sub


3 more replies
Relevance 70.11%

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

More replies
Relevance 70.11%

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

Answer:Need a macro for excel 2007 to save data

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

6 more replies
Relevance 70.11%

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

Answer:Solved: Excel 2007 Sort Help - Macro

Welcome to the board.

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

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

2 more replies
Relevance 69.29%

How do I hid the larger toolbar above it which I find confusing to use, and then to expand the quick access toolbar, which at least I can use but is small?
In passing, I find this Word 2007 a lot harder to use than the earlier Word versions and I've yet to see the advantages from the increased complication.

Answer:Quick Access Toolbar

Originally Posted by ukthesis How do I hid the larger toolbar above it which I find confusing to use, and then to expand the quick access toolbar, which at least I can use but is small?In passing, I find this Word 2007 a lot harder to use than the earlier Word versions and I've yet to see the advantages from the increased complication. Can you explain that a bit more. I am afraid I do not understand your problem. And what Operating System are you talking about - Vista, Windows7??
Regarding Office 2007, you will find over time that it is a lot easier than 2003. I used both and can assure you that 2007 is miles better. Takes just a bit of patience and training to learn it.

4 more replies
Relevance 69.29%

Hi, I am trying to run a macro from 2003 in Excel 2007 and it gives the following error:Run Time Error 1004Method 'AcceptLabelsInFormulas' '_Workbook' FailedThe line in the macro that it is referring to reads:.AcceptLabelsInFormulas = TrueDoes anyone know what the line should be in Excel 2007?I don't know if this is the only problem as it stops at this point. But it appears everything prior is ok and runs to this point.

Answer:2003 Excel Macro in 2007 Excell Problem

A quick Google search reveals that the option to set.AcceptLabelsInFormulas = True has been removed from 2007.2 reasons were given:1 - seldom used2 - prone to errors when column labels are moved, etc.Apparently the "standard" workaround is to use Named Ranges instead.

4 more replies
Relevance 69.29%

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

Answer:Excel 2007 Macro To Copy Rows to Tabs

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

13 more replies
Relevance 69.29%

I just bought a new laptop running windows 8, downloaded and installed Excel 2007. I have the Developer tab active, did the Macro Security changes to allow activex, enable all macros etc., but the Record Macro, Visual Basic and Macros buttons are inactive. That is, they are not clickable. I updated to windows 8.1 - no joy! Otherwise the install of Excel 2007 seems okay. Spreadsheets from my old computer & a much older version of Excel open okay, formulas work, etc. The Macro issue is the only problem I am aware of at this point. The symptoms are the same on a new (blank) workbook or on an existing spreadsheet. Any suggestions? - I guess a re-install is my next option. Thanks in advance. Bob

Answer:Solved: Excel 2007 Macro & Visual Basic Not Available

A re-install of Office 2007 did the trick. I enabled functions this time that I had not enabled the first time. Don't know which one solved my problem but the macro buttons are now active & I can get on with it....

2 more replies
Relevance 69.29%

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

My plan, in short, is as follows:

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

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

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

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

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

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

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

2 more replies
Relevance 69.29%

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


Answer:Excel 2007 Macro to Send Reminder Email

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

2 more replies
Relevance 69.29%

Greetings -


Target Complete Money
1 1/1/09 1/7/09 50.00
2 1/5/09 1/12/09 100.00
3 1/7/09 1/14/09 50.00
4 1/17/09 1/17/09 25.00
5 2/2/09 1/26/09 25.00
6 2/5/09 150.00
7 2/6/09 150.00
8 2/5/09 75.00

Currently i have the following formula to calulate how much money that I might make.

This works fine as I have no blank cells in column A

If I try to apply this formula to column B I get an incorrect number as I have blank cells in there with no values.

Is there a way to continue to use this formula but have it check for blank cells and if it finds any, don't add those values??

Answer:Excel 2007: Date/Sum Macro/Blank Cell

Welcome to the board.

How about the attached? Contains 1 "master" dynamic named range & 2 others based on the "master". Use E2 to define the month you're after.

3 more replies
Relevance 69.29%

Hi Guys,

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

Sub ReplaceString()

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

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


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

Could you help me please?



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

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

1 more replies
Relevance 69.29%

Hi Guys,

First of all thanks for the great support provided by your forum!

I'm using with remarkable profit the following macro edit by Rollin_again (sorry in case the nick is wrongly written!)


[SIZE="5"]Sub ReplaceText()

Each vCell In Range("A2:A200").Cells

("C:C").Cells.Replace What:=vCellReplacement:=vCell.Offset(0Read more

Answer:Macro Multiple Find and Replace in Excel 2007

To get a green fill the code you could use would be
vCell.Interior.Color = vbgreen

but is you place this before Next cCell, I think all the cells for vCell would turn green.

You turn the colouring back to normal by using

vCell.Interior.Color = xlNone

Give it a shot

3 more replies
Relevance 69.29%

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

ActiveWorkbook.EnvelopeVisible = True

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

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

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

More replies
Relevance 69.29%

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

Answer:excel 2007 macro to sort data to new sheet

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

10 more replies
Relevance 69.29%

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

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

Some ideas ????

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

15 more replies
Relevance 69.29%

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

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

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

2 more replies
Relevance 68.47%

I can't find it either in programs or apps as suggested in Microsoft Help

Answer:How to remove Quick access toolbar

from what? windows 10? MS word? Autocad?

1 more replies
Relevance 68.47%

I went to customize the bar but when I clicked on "Customize Quick Access Toolbar" nothing happens. All I can do is add or delete the six options that is presented. I previously customized it and used that a lot! It is a deal breaker for me. Does anyone know of a work around or should a buy CD and reinstall 8.1 which came on the machine?

Answer:Quick Access Toolbar not responding

Well I feel like an idiot! My comment was way off base. I was looking at a folder which is now formatted like Access or Excel window. It is an enhancement/improvement to folders. Sorry for wasting space and the time it took for folks to read my rant.

1 more replies
Relevance 68.47%

How do you make a macro that does the following:

go to next cell down in Column D
copy contents (a single word) of that cell
select entire Column A
find first instance, in Column A, of the word that was copied from Column D, above, (the word is part of a sentence in a single cell in Column A),
underline just that word (not the whole sentence) in Column A


Answer:Excel 2007 macro - locate specific word & underline it

Hi Brad, welcome to the forum

I just recorded a amcro that does that and translated it to vba code

Function ULinetxt(cVal As String, uStr As String)
Dim tp1 As Integer, tp2 As Integer
If InStr(1, LCase(cVal), LCase(uStr)) > 0 Then
tp1 = InStr(1, cVal, uStr)
tp2 = Len(uStr)
With ActiveCell.Characters(Start:=tp1, Length:=tp2).Font
.Underline = xlUnderlineStyleSingle
End With
End If
End Function


Sub test()
Call ULinetxt(ActiveCell.Value, "underline")
End Sub

You could use this function in vba macro and it's done

Select a cell with your data string and rin the test macro, replace the word "underline" with the text you want underlined and the first occurrence (if found) will be underlined, case is not matched.

Let me know it it works for you.

1 more replies
Relevance 68.47%


I was hoping someone could provide me with some detailed instructions on how to create a macro to automatically sort some columns I have and then enable it so that I can place a button on the worksheet which will activate that macro.

Any help would be greatly appreciated!

Answer:Solved: Running A Macro Using A Button In Microsoft Excel 2007

Is anyone able to help me with this?

3 more replies
Relevance 68.47%

I'm really new with macros, but I want to know if it is possible to do the following scenario and how to accomplish it:

I have two sheets in one workbook. On sheet 1, there is an inventory which contains codes, dates, titles, and most importantly, subjects in 6 columns (G-L). On sheet 2, I put the old subjects (from G-L on sheet 1) in column A and the new subjects in column B. I want to utilize a macro to find all the "sheet2 column a" subjects on sheet 1 (match entire cell contents) and replace them all with the new "sheet 2 column B" subjects. Can this is done? How would I do it?

Thank you for any advice you might have, I truly appreciate it!


More replies
Relevance 68.47%

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!

More replies
Relevance 68.47%


I'm new to this, so any help would be appreciated.

My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

Sub CheckBox2_Click()
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Selection.EntireRow.Hidden = False
End If
End Sub

And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.


Answer:macro excel 2007 that copies filtered rows to another sheet

13 more replies
Relevance 68.47%

I have XP and just installed Office 2007. In order to get a 2003-like environment, I set up a customized Quck Access Toolbar that is meant to come up every time I open Excel. I also have a Personal Macro Workbook I wish to automatically open every time I open Excel.

To do this, I set them up as Add-ins in the ...\Application Data\Microsoft\AddIns\ directory:
* PersonalMacroWorkBook.xlam
* MyQAT.xlam

The issue is that when I launch Excel directly (say, from the Start Menu), the two add-ins open and work. However, if I launch Excel by directly opening an Excel file, neither of the two add-ins are opened. [Note that this happens whether I'm opening file with extension .xls, .xlsm, or .xlsx.]

One other point - this used to work just fine. The issue arose recently - and I think I can trace it back to Excel crashing a few days ago. It crashed, incedentally, independent of any action by me - I was actually working in a different application at the time.

Can anyone tell me what's going on? Can you help me fix this?


More replies
Relevance 68.47%

Hi All,

I really hope someone can help me. I've been looking through all the forums and haven't quite found a macro code that has worked for me. I know this is very simple, but I have no idea what I am doing. Here it goes....

I have a very simple expense report on Sheet 1 of an Excel (2007) workbook. In this worksheet, the first couple of columns simply list the expense items. However, there is a table with only 14 rows (including the header) of data (6-19). Underneath row 19 is a total row that sums the 2 columns of information (don't need to worry about this row but thought I'd tell you anyway). This table only has 4 columns as follows:

Column G - Code
Column H - Category
Column I - $ (this column is using the SUMIF function to calculate the expense category totals)
Column J - % of Total (this column is simply dividing column I by the Total $)

I don't know if this matters or not but there is also a pie chart linked to this table of information.

I would like this table (H6:J19) to automatically sort by % of Total (in descending order) any time I add new expenses so that I don't always have to sort manually.
If there is a way to do this, I would so appreciate simple instructions. I've attached a sample of what the report looks like.
I hope you guys can help!!!

Answer:MACRO NOVICE - Simple Auto Sort using Excel 2007

Hi Janel, welcom to the board

I think this should do the trick, I don't have 2007 here but I used your sheet in 2003

Add the following code the the Sheet1 project


Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range("G7:J19").Sort Key1:=Range("J7"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub

To do this just right-click on the Sheet1 tab and select view Code, you will automatically be in the VBAProject.

In the right screen paste the code above.

every time you change values in the list the range G7:J19 without hearders will be sorted by Total % descending order, th epoie chart will do it's own work since it's related to this table.

Happy coding.

1 more replies
Relevance 68.47%


Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. 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 wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog*************
etc etc**************************

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).
Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,



Answer:Solved: Macro Multiple Find and Replace in Excel 2007

9 more replies
Relevance 68.47%

I have a recorded excel (MS Office 2003 professional) macro that does not run at all in MS Office 2007 professional is there an easy edit that I can do to make it work?

SmallFines macro:

Sub SmallFines()

Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False

Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
Selection.AutoFilter Field:=43, Criteria1:="CO"

Sheets("Carry Over").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlToRight)).Sel... Read more

Answer:recorded 2003 excel macro no longer works in 2007

I think a sample sheet would be better.
Does the macro do anything at all?
Do you get an error?
Remember that Macro's are disbled by default in 2007.
Check that first.
If you have a sample, I'm now using both 2003 and 2007 so I could take a look at it.

1 more replies
Relevance 68.47%

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.

More replies
Relevance 68.06%

I have installed the updated version of the above (see title), but cannot seem to be able to find out,how to get the tool bar the full width as it was with the last version I had installed.
This is what it looks like at the moment.

Answer:ASAP Utilities Toolbar With Excel 2007

I would try uninstalling and re-installing.

2 more replies
Relevance 68.06%

I have an add-in that installed its own menu item on the ribbon bar. From the ribbon there are a bunch of functions that can be inserted as well as toolbars. The functions work fine but I can't see any of the toolbars.

Answer:Solved: Can't see 3rd party toolbar in Excel 2007

Hi sync

Can you give us the name of the add-in and post a link to the third party program you are using?

3 more replies
Relevance 68.06%

Outlook 2010 32-bit running on Win 7 Pro 64-bit - something is broken.

I cannot customize the ribbon and same for quick access toolbar.

All COM add-ons are turned off. Tried in /safe mode too.

More replies
Relevance 67.65%

I'm trying to create a macro that uses conditions based on a form. The trouble I'm having is that for some reason the condition is always resulting as true no matter what I do.

The condition is "[Forms]![Unmatched Charges]![Text14] Is Not Null"
The action that happens when the condition is true is the displaying of a message box.

The form is based on a query that when run shows no results as expected. The form when opened also shows no results. However, when the macro runs, the condition of "Is Not Null" still shows as true indicating that there is data present. The form only has two objects on it and I've verified the names of the objects many times.

Any help would be appreciated.

More replies
Relevance 67.65%

I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)


Email | Phone | Last Name | First Name

I would like to have a macro cut/paste entire rows and put in a specific order

First name |Last Name| Phone| Email

Any help would be great! Thank you!

Answer:Excel 2007 – Macro (newbie) - Move Columns bases on name question

How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.


3 more replies
Relevance 67.65%

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows

AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

Answer:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

8 more replies
Relevance 67.65%

I've searched and have not been successful to find an answer to my issue.
I have a workbook with multiple spreadsheets. Each spreadsheet has a list of employess with tasks and hours.
I need a macro that will search for the employee name and add the hours spent working on a task.
I also need to search for the different tasks and add the total hours spent on the specific task.
Workbook contains multiple tabs (Summary, week1, week2, week3, etc)
Each worksheet has three columns and employee name may be in the first column multiple times with different tasks:
Employee Tasks Hours
Jason Dyer dishes 8
Joyce Brown lawn 12
Donald Steiner vacuum 16
Jacqueline Lowe dusting 4
Robert Jones mopping 2
Barbara Fritts trash 8
Lisa Stillman painting 10
Antionette Adkisson repair 6
Billy Barkley errands 14
Ian Grayson sweeping 18

And the Summary has:
Employee Total Hours
Jason Dyer
Joyce Brown
Donald Steiner
Jacqueline Lowe
Robert Jones
Barbara Fritts
Lisa Stillman
Antionette Adkisson
Billy Barkley
Ian Grayson
Task Total Hours

Any help will be appreciated!

Answer:Excel 2007 search and match macro, add values in multiple spreadsheets

7 more replies