Over 1 million tech questions and answers.

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

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

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

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

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

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

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

The following is the start of the macro code:

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

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

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

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

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

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

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

Read other 7 answers
RELEVANCY SCORE 120

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

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

Can anyone help?

Thanks!
 

A: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
 

Read other 1 answers
RELEVANCY SCORE 117.6

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.
 

Read other answers
RELEVANCY SCORE 112

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

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

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

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

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

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

Dim c As Range

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

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 107.6

Running Windows Vista Premium Home Edition.

I have an Excel 2003 spreadsheet.that I originally created on Excel Office Professional Edition 2003. Also, have Excel 2007 Home and Student Edition installed. I uninstalled the Excel 2003 software, but now when I clik on this created Excel 2003 spreadsheet with Excel 2003 it will not open. It says there is an invalid path. I reinstalled Excel 2003 and it opens. I tried to save this as an .xlsx extension and then uninstalled Excel 2003. It wouldn't open. So something is occurring here. On other Excel 2003 created spreadsheets, they open without issue.

Then I have a program with an .exe extension. It needs to open using Excel 2007. With both Excel 2003 and Excel 2007, it will not open. It freezes and then I get an error message saying it cannot open with another Excel other than 2007. If I right clik on the .exe program, there is no "Open with". I even tried opening a blank Excel 2007 spreadsheet and then cliking on "Open". but it is a bunch of gibberish. My IT person says you can't open the program that way, You have to launch it as an .exe program using Excel 2007.

Any ideas on what is going on? By the way, I have both Excel versions on my laptop running Windows XP and I do not have these issues at all.
 

A:Solved: Some Excel 2003 & Excel 2007 applications won't open on Windows Vista

Hi there,

If I said I was confused that'd be an understatement I think. You cannot open executable files (*.exe) with Excel. Recognized file types are things like .xls, .xla, (for 2007) .xlsx, .xlsb, .xlsm, etc. I don't know what your exe file is and I'm not sure I wanna know, but it certainly doesn't open in Excel. What is it exactly? If you don't know I wouldn't open it. Or if someone told you to open it with Excel then it's probably a virus. Unless I'm missing something here.

With the file in question which won't open, assuming it's not this strange and unknown exe file you're talking about, sounds like the file may have been corrupted. Does it open on any other machine? And if you uninstall Excel 2003, did you re-install 2007? It seems strange. I would try cleaning out your application and reinstalling 2007 fresh. You can't install 2003, then 2007, then take off 2003 and put it back on, you're gonna get things screwed up. I bet your registry doesn't know which way to turn here. Some good info on it can be found here...

http://officearticles.com/misc/best_install_procedures_for_microsoft_office.htm

Also, I would highly recommend you completely uninstall all versions, and I'm talking a deep cleaning, then reinstall. For that deep cleaning, take a look at this article (which I'd highly recommend you do) and then reinstall...

http://officearticles.com/misc/remove_microsoft_office_from_your_pc... Read more

Read other 2 answers
RELEVANCY SCORE 106.8

Firstly I am a beginner of beginners but I try to learn as much as I can.
I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.
Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then
MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."
Application.DisplayAlerts = False
Application.Quit
End If
End Sub
My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2934 Mb
Graphics Card: Intel(R) HD Graphics, 1243 Mb
Hard Drives: C: Total - 290143 MB, Free - 172186 MB;
Motherboard: Dell Inc., 0WXY9J
Antivirus: avast! Antivirus, Updated and Enabled
 

A:Solved: Excel 2003 vba code not working in Excel 2007

Read other 16 answers
RELEVANCY SCORE 106.8

I have just been upgraded to Excel 2007. I find that some of the macros that were recorded in Excel 2003 no longer work in Excel 2007. We use the Analysis Toolpak VBA add-in and I have added this in to 2007 ok, but when running a macro I get a run time 1004 error ATPVBAEN.XLA cannot be found. After some searching it would appear that some of these functions have been renamed in 2007 e.g. it is now a .XLAM file rather than .XLA which is why the macro code can't find it. I guess I could manually edit all the macros and change the fucntion names but there are other colleagues in my team that are still using 2003, so I don't want to break things for them. Is there any solution to this? Thanks
 

A:Solved: Problem with Excel 2003 macros in Excel 2007

Read other 8 answers
RELEVANCY SCORE 106.8

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("AQ2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown

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

Rows("2:2").Select
Range("AM2").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=43, Criteria1:="CO"

Columns("A:AQ").Select
Selection.Copy
Sheets("Carry Over").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

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

A: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.
 

Read other 1 answers
RELEVANCY SCORE 104

Is there a way to clear, and add Fill color to an Excel spreadsheet? Please see the attached file.

I need this to be compatible with Excel 2003 & 2007.
 

A:Solved: Excel 2007 & Excel 2003

Read other 16 answers
RELEVANCY SCORE 99.6

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?
 

A:excel 2007 won't open excel 4.0 macro

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

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

Read other 1 answers
RELEVANCY SCORE 99.2

Excel 2002 Platform

In need of macro script that I can paste and copy into my excel macro editor.

1. Script would look at specific column for the word TODAY

2. If TODAY is located...script would format the next 8 columns of text and print same.

3. All TODAY specific rolls would need to be printed on same page.

Thanks for all feedback, nt
 

A:Excel Macro Requested

>> In need of macro script that I can paste and
>> copy into my excel macro editor

Did you try recording the relevant actions & then studying the code? (this is the best way to learn stuff, I'm having a bad week & it's only Monday ).

>> script would format the next 8 columns of text

This bit might cause problems ; possibly better to format everything at the start (hint -- CTRL+Shift+*) & unformat at the end, that way you could include AutoFilter in the code so that it showed every row where eg: Column A = "TODAY" (and then printed).

Range("A1").Select
Selection.AutoFilter Field:=1, Criteria1:="Today"

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 99.2

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

Can anybody help me?
 

A:Solved: VBA Macro - Excel 2007

Read other 16 answers
RELEVANCY SCORE 99.2

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

Example:
Red, Gold and yellow with Green Stripes

The final result:
Red
 

A:Solved: Excel 2007 Macro Help

Read other 13 answers
RELEVANCY SCORE 99.2

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

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

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

With a Result output

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

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

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

A:Solved: Excel 2007 Macro help

Read other 8 answers
RELEVANCY SCORE 98.8

Hi all, I need some help please with excel 2003 vba code. I have recorder the macro which I want to look at a cell and copy the value, select the custom filter, paste the cell value into the custome filter and then sort the results in Z-A order.

The code however (generated by macro recorder is inserting the text value rather than the cell reference.

Can anyone tell me how to edit the code please?? The part number 2000801990 is shown in red - this should be the cell reference.

Thanks

Nic

Sub engineer_consumption()
'
' engineer_consumption Macro
' Macro recorded 18/09/2008 by Nic Cunliffe
'
'
Range("B1").Select
Selection.Copy
Selection.AutoFilter Field:=2, Criteria1:="=2000801990", Operator:=xlAnd
Range("C177").Select
Application.CutCopyMode = False
Range("A8:C44630").Sort Key1:=Range("C177"), Order1:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortTextAsNumbers
End Sub
 

A:Solved: Excel 2003 macro help

Read other 8 answers
RELEVANCY SCORE 98.8

Hi

I'm hoping someone can help me, what I'm trying to get it to do is copy info from one sheet and paste it into another sheet but in a slightly different format.

If I was to write what I want in english it would go like this :

Copy the date from cell F5 on sheet A to the next available row in column A on sheet B, then copy from cell H5 on sheet A to next available row (same row as the date in Col A) in Column B on sheet B. Then copy data from cell F31 on sheet A and to the same row, but if cell B7 on sheet A says 'white' then copy it to Column C on Sheet B, or if cell B7 on sheet A says 'blue' the copy it to Column D on Sheet B, or if cell B7 on sheet A says C then copy it to column E on sheet B and so on, I have 6 different columns. (I do have column titles on sheet B on row 5).

Hope that makes sense, can anyone help?

Thnax in advance.
 

A:Solved: Excel 2003 Macro help

Read other 6 answers
RELEVANCY SCORE 98.8

I have a userform created in Excel with a combo box. I would like to have the user select an option from a combo box, and then filter/hide data on a worksheet based on the combobox selection.

If any further information is needed please let me know.

Any help with this would be greatly appreciated.
 

A:Solved: Excel 2003 Macro

Read other 16 answers
RELEVANCY SCORE 98.8

I'm trying to populate blank fields with data from above. For example, in a column, there will be one cell containing data, I'd like to populate the blank cells below until another populated cell appears etc...

I think this is a "Run a For Each...next Loop" macro but do now know VBA language well enough to build.

Any ideas?
 

A:Solved: Excel 2003 macro

Read other 16 answers
RELEVANCY SCORE 98.8

Hi Guys

I have a Excel spreadsheet that has a lot of sheets. On the header sheet I have created a checkbox next to the names of each sheet, say "A", "B", etc

I've tried to create a macro that deletes all the sheets with the checkbox checked but unsuccessful so far. Can you guys please help me?
Also what's the best way to get to the sheet from header sheet without slowing down excel too much please? Hyperlink?macro?
 

A:Solved: Need help re Macro in Excel 2003

Read other 16 answers
RELEVANCY SCORE 98.8

Hello,
My question is "Is it possible to do what I want to do" in Excel 2003?

I would like to reformat a large list of name and address info from this format:

1. BUSINESS NAME (MILES)
ADDRESS
CITY STATE ZIP
PHONE: 000-000-0000
TYPE OF BUSINESS: XXXX XXXX

TO THIS FORMAT:

A B C D E F G
BUSINESS NAME ADDRESS CITY STATE ZIP PHONE TYPE

I HAVE NO PROBLEM MOVING THE DATA INTO THE DESIRED FORMAT USING A SIMPLE "RECORD NEW MACRO..." THAT I RECORDED.

MY PROBLEM AND QUESTION IS REMOVING THE RED HIGHLIGHTED INFORMATION.
I AM NOT A PROGRAMER AND HAVE NOT FIGURED OUT THE LOGIC IN THE 'VISUAL BASIC'.

I HAVE TRIED MANY "RECORD NEW MACO..." BUT CAN NOT GET IT TO REMOVE THIS UNWANTED INFO.

IS IT EVEN POSSIBLE TO DO THIS WITH EXCEL 2003 ?????????????????????
AS YOU CAN IMAGINE ANY HELPFUL ADVICE WILL BE GREATLY WELCOME.

THANK YOU SO MUCH.
JOHN
 

A:Solved: Excel 2003 MACRO HELP

Read other 16 answers
RELEVANCY SCORE 98

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

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

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

A:Solved: Dynamic Excel 2007 Macro

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

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


Code:
Public Sub test()


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

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand


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

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

totalExp = lrow + 2


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

Coach = ActiveCell.Row


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

Direct = ActiveCell.Row


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


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

End If

End Sub

 

Read other 3 answers
RELEVANCY SCORE 98

Hi,

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

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

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

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

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

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

Please help.
 

A:Solved: excel 2007 macro error

Read other 7 answers
RELEVANCY SCORE 98

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?
 

A:Solved: Excel 2007 Sort Help - Macro

Welcome to the board.

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

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

Read other 2 answers
RELEVANCY SCORE 98

Hi All,

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

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

Thank you!

Jen
 

A:Solved: Excel 2007 Macro Question

Read other 9 answers
RELEVANCY SCORE 98

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.


 

A:Solved: need help writing macro in excel 2007

Read other 16 answers
RELEVANCY SCORE 98

Hi,
I transferred all my old Excel (2003 and before) files to my new notebook which has Vista HP and Excel 2007. I open an old Excel file and it looks OK but when printing or print previewing, every line appears double-spaced and the document runs over to a second page.
If I change the row height it prints the squeezed up text but still double-spaces. If I copy the whole content of the spreadsheet into a new blank spreadsheet it prints normally.
Is there any setting in Excel 2007 that I might have missed ?
Thanks.
RB
 

A:Printing Excel 2003 files with Excel 2007 give double spacing

Read other 7 answers
RELEVANCY SCORE 98

Does anyone know how to setup a command button to run a macro?
 

A:Solved: Excel 2003 Run Macro from CammandButton

I figured out how to get the macro to run from a command button.

Here is the code I used....
Code:


Call MacroName

 

Read other 2 answers
RELEVANCY SCORE 97.2

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

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

Some ideas ????
 

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

Read other 15 answers
RELEVANCY SCORE 97.2

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
 

A: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....
 

Read other 2 answers
RELEVANCY SCORE 96.8

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010
 

A:Solved: Excel macro 2003 to 2010 error 438

Read other 16 answers
RELEVANCY SCORE 96.8

Hi Guys,

I'm after a bit of help.

I'm working on a spreadsheet in Excel 2003, it's a sheet to monitor staff Holidays/lieu days/entitlement etc.
The basic setup is as follows:
the main sheet is 'Staff List' and each Staff member will have their own sheet (named as per their initials)

What I like to achieve is to have an 'fool proof' button (macro) to automatically insert a new sheet and insert the staff members name in cell AD2 and name the sheet using the staff members initials
and also insert a row on the 'Staff List' sheet (row 50, I will manually move it to the proper location (this macro is simply for the times I'm not in and colleagues mess up the formulas)).
Now the tricky bit is that there are a number of formulas on the 'Staff List' that would need to be copied to the new row

I have attached a test file to play around with for anyone who would like to.

Any Help/suggestions much appreciated!

regards
mad-martin
 

A:Solved: excel 2003 insert sheet macro

Read other 16 answers
RELEVANCY SCORE 96.8

I have a large file that I print many times and there are some pages included in the list that are not needed and I throw away every time.

Other than print twice, I'm sure there is a macro that can be programmed to skip those pages, or maybe a macro to link the two print ranges.

Can anyone get me started?
 

A:Solved: Interesting Excel 2003 print macro

Read other 16 answers
RELEVANCY SCORE 96.8

I purchased Excel 2007 to take advantage of the larger number of rows and columns. My SS applications are very large and I run out of columns. But after trying my Excel 2003 in the new 2007 software, I find that 2007 runs 10-20 times slower, making it completely unusable. Googling the problem shows it's a widespread issue. MS help desk essentially no help. Any suggestions?
 

A:Excel 2007 Runs MUCH Slower than Excel 2003

The biggest thing that slows Excel 2007 is also what you wanted it for - the greater number of cells. If your formulas have cell references like A:A or $1:$1, then that is a lot more cell references for it to process.
Read this http://msdn2.microsoft.com/en-us/library/aa730921.aspx and maybe you will get some ideas on how to make your files process faster.
 

Read other 2 answers
RELEVANCY SCORE 96.8

I'm not sure if it because the files are 2003 or not? When I try to open them an error message appears stating, "...the file you are trying to open is in a different format than specified by the file extension. Verify file is not corrupted and is from a trusted source..." These are my files. The interesting thing is that if I try to open the same file that I have saved within the last year (before I got 2007) then I can do it? That is, I have a cache folder of files that I go get to do calculations then save in a new job folder. The ones in the new job folder will open (also 2003), but the ones in the original cache will not? I would truly appreciate any suggestions. Thank you.

A:Can't open 2003 Excel Files in Excel 2007?

Have you attempted to set them up in 'Compatibility Mode." Not sure if this will help, just hoping it will. I have been testing the new Microsoft Office 2010 Beta 64 bit, and have yet to have this problem, and like you I have a great deal of files in 2003 (never used 2007)'

I think you can still download MS Office 2010 beta from Microsoft's download site. Good Luck.

BTW, Welcome To The Seven Forums.

Read other 9 answers
RELEVANCY SCORE 96.8

Hi can anybody tell me hoe to copy macros from excel 2003 to excel 2007?
The excel 2003 is running under windows xp and the 2007 under windows vista.
(The two versions of excel are installed at differnt computers)

A:Copy Macros From Excel 2003 To Excel 2007

I don't know Excel 2007 nor Vista.
But the usual way to copy macros brute force would be to open the VBA toolbar, get into the Modules, select each, and Export one by one. Then import into 2007 modules if Excel 2007 VBA permits that.
If export/import doesn't work, you can always copy out the entire text into Notepad text files, and copy/paste from the Notepad into your 2007 Excel.
If the VBA language or object model has changed between 2000 or 2003 and 2007, then some tweaking might be in order, but I suspect they left it alone, and the error messages, if any, might suggest what to fix.

Since these are different computers, either network them and share some common directory, or use USB flash drive for transfers of the, say, Notepad files.

Export is good, because it names the modules. They get saved someplace in your local settings. Notepad will not name, and doesn't matter really.

Read other 3 answers
RELEVANCY SCORE 96

I have an Excel 2000 spreadsheet that contains several macros which ran fine under Windows 98SE. I finally broke down and upgraded to a new machine running XP Pro, and discovered that one of the macros doesn't run properly. What makes this perplexing is that there are three very similar macros in the spreadsheet, and only one of them is giving me problems.

The macros in question shell out to run external programs, which receive a selected cell value as a file name argument. The macro that isn't working calls a program I wrote called ReplayQk. Because of the way ReplayQk is designed, I can distinguish between cases where the program can't find the file it is passed (displays a "can't find it message") and cases where no argument is provided at all (presents a dialog box that allows the user to sepcify the file). Based on this I can tell that the program isn't receiving the argument. But another macro that is doing essentially the same thing is working fine. Finally, if I run ReplayQk from a command line and provide a filename argument, I works fine.

Below is a copy of the code for two macros, one that works and one that doesn't. And, in considering this problem, remember that both of them work under Win98. So, anybody have a plausible explanation for this?

Sub RunWinquake()
'
' Pass a selected event file to Winquake for execution. The
' event file is selected by selecting the event file name in
' the spreadsheet.
&... Read more

A:Solved: Excel macro works under Win98, doesn't under XP Pro

Read other 16 answers
RELEVANCY SCORE 96

Hello,

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!
 

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

Is anyone able to help me with this?
 

Read other 3 answers
RELEVANCY SCORE 96

Hi,

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************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(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,

Thanks

Lynchie
 

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

Read other 9 answers
RELEVANCY SCORE 95.6

Hi

I am using excel 2003 (version: 11.8341.8341) SP3 and created a macro to rename the active sheet:
Code:
Sub renamesheets()
Dim activesheet As String
If activesheet = "[B]Sheet01[/B]" Then
Set Sheets("[B]Sheet01[/B]").Name = "[B]qryOfficeNetForeign[/B]"
Application.ScreenUpdating = True
Else: End If
End Sub
SO the active sheetname is: Sheet01 and should be renamed to qryOfficeNetForeign.

This code is not working I am wondering why...

Thanks,
Wouter
 

A:Solved: macro to rename active sheet in excel 2003

You don't need the SET keyword.
Code:
If ActiveSheet.Name = "Sheet01" Then
ActiveSheet.Name = "qryOfficeNetForeign"
End If
or
Code:
If ActiveSheet.Name = "Sheet01" Then
Sheets("Sheet01").Name = "qryOfficeNetForeign"
End If

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 94.8

Why does the second line of the code shown below produce an error in Excel 2007 and not in Excel 2003? The error is:
Run Time Error 1004, Unable to set the Text Property of the Characters class

ActiveSheet.Shapes("btnJobSummary").Select
Selection.Characters.Text = "Prepare Job Summary" & Chr(10) & "for " & CurrentJob

CurrentJob is defined as: Public CurrentJob As String

In this case it equals: RockMont Rev 7-9-04

No problem at all in Excel 2003 and earlier. If CurrentJob is something shorter (e.g. MyFile), there is no error. I don't think the spaces are a problem either because I still got the error after adding Chr(34) before and after CurrentJob in my code. Any ideas? Thank you.
 

A:Error in Excel 2007 but not in Excel 2003

Hello, welcome to the board!

The text is too long. Use an ActiveX control instead of a Forms control. Then you can use similar code...
Code:
Public Const strCurrentJob As String = "RockMont Rev 7-9-04"
Dim btn As CommandButton

Sub FooTest()
Set btn = Sheet1.CommandButton1
btn.Caption = "Prepare Job Summary" & Chr(10) & "for " & strCurrentJob
End Sub
HTH
 

Read other 3 answers
RELEVANCY SCORE 94.8

Hi there.

I'm trying to open an Excel 2007 document with Excel 2003. I thought that I had downloaded what I needed to be able to do that, but I guess not. Is there a way to do this? I suppose I could upgrade to newer version if I need to. If so, should I go ahead and get the newest version and get it over with?

Thanks for your help and time. Have a good day.
 

A:Open an Excel 2007 doc with Excel 2003?

Have you tried the office 2003 compatibility pack https://www.microsoft.com/en-us/download/details.aspx?id=3
 

Read other 5 answers
RELEVANCY SCORE 94.8

I am working in Excel 2003, within XP and have the following situation.

For approximately 18 months, I have been running the macro listed below without issue. Now, for no apparent reason, the macro fails to execute in its entirety. When executing the macro from within Excel, the execution starts and proceeds a few lines and then just stops. No error, no warning, nothing, the macro just stops.

If I open up the Visual Basic editor screen from within Excel and attempt to execute the macro with F5 or step through line by line with F8, execution will proceed to the end of the Selection.TextToColumns command line, and the insertion point goes back to the start of this same command once executed. If F8 is pressed a second time, the insertion point goes all the way back to the Sub line at line 1 of the macro and tries to run the macro again. The behaviour is consistent, and no error code or indication occurs. The code has not changed.

If I break up the macro into a series of smaller macros at each error point, and run them sequentially, I get the desired outcome.

I have tried renaming, copying and pasting the text into different macro files, exporting the macro and re-importing into different worksheets (this one is currently sitting in personal.xls which is still sitting in my /xlstart subfolder.

I have 8 similar macros that now all do the same thing - very puzzling.

Would welcome any suggestions you may have. Thanks all.

ps. for all you real VBA folks out there, no this isn&... Read more

A:Solved: Excel 2003 macro halts during execution - no error message

Read other 7 answers
RELEVANCY SCORE 93.2

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 92.8

Hello again,

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

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

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

Sample form attached, thank you
____________________________
 

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

Read other 7 answers
RELEVANCY SCORE 92.8

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Read other 6 answers
RELEVANCY SCORE 92

Hi People!

I need help fast! I have a table as shown;

Date TC1 TC2 TC3 TC4 TC5 Min LOC Max LOC
4/12/12 9.3 9.5 9.1 8.3 9.0
4/13/12 9.5 9.9 9.3 7.3 8.0
4/14/12 9.3 8.3 7.1 9.3 9.0
4/15/12 7.3 9.0 9.7 8.3 8.0

What I need:
I need to find the location (LOC) for the occurrence of the Max & Min value in each row.

Question:
Is there a way in Excel 2003 or 2007 where I can obtain the top header title for each occurrence?
If there are multiple occurrences at the same row; can I get them too?
 

A:Solved: How to get an occurrence location in Excel 2003/2007

Read other 11 answers