Over 1 million tech questions and answers.

Solved: Excel macro 2003 to 2010 error 438

Q: Solved: Excel macro 2003 to 2010 error 438

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

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel macro 2003 to 2010 error 438

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 macro 2003 to 2010 error 438

Read other 16 answers
RELEVANCY SCORE 89.6

We recently migrated from Office 2003 to Office 2010.

Just to give some background, (and I dont' know if this is relevant to this error), the Office 2003 excel did not accurately show the correct file paths where files were linked. It would show something in the C drive.

The issue I am concerned about today is this: when some users close files in 2010 (files were created in they sometimes get the error below:

'"The name ABE2, either conflicts with a valid range reference or is invalid for Excel. This name has been replaced with _ABE2"

Can someone assist me with this? I need to stop it from appearing and I am not sure how.
 

A:Solved: Excel 2010 v Excel 2003 Error Valid Range

Read other 14 answers
RELEVANCY SCORE 88

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 81.6

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

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

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

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

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

The following is the start of the macro code:

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

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

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

Read other 7 answers
RELEVANCY SCORE 78.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 78.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 78.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 78.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 78.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 78.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 78

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 77.6

Hi,
I was wondering if it is possible to trigger a macro when an particular custom tab is clicked.
This is part of my XLM code from the Excel file:
Code:

<!-- Add Custom group to the Home tab in the ribbon with one button-->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<[COLOR="Red"]tab id="customTab" label="Inzet Planner" insertBeforeMso="TabHome[/COLOR]">
<group id="customGroup1" label="Planner Maand Selecties" >
<button id="cG1B1" label="Huidige Maand" size="large" onAction="BtnOnActionCall" imageMso="ArrangeByAppointmentStart"/>
etc ....

It all works perfectly for the buttons, that's not the issue.
What I am looking for is that if CustomGroup1 is pressed a macro is triggered. An then of course I have a series of groups and that can be a different macro or no macro at all.
I tried sendKeys and so but that does not work well.
Like the Button Id has an onAction function, is there something similar for when a Group is selected?

Thanks for any answers
 

A:Solved: Excel 2010 trigger macro when a ribbon tab is selected

Read other 15 answers
RELEVANCY SCORE 77.6

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

A:Solved: Sort Macro for multiple files- Excel 2010

Read other 6 answers
RELEVANCY SCORE 77.6

HI,

I have a huge product listing that i need to find and replace on a mass scale. I have one spread sheet that has 3 columns (see example below)
Column A****************Column B**********************Column C
1************************ M2123*************************M3455
2************************M3455*************************M3455
3************************M3433*************************M3455
4************************M6543*************************M3433
5************************M8793*************************M3433
6************************M6543*************************M7832
7************************M7832*************************M7832
8************************M4893*************************M7832
9************************M3938*************************M2123
10**********************M4837*************************M2123
*********************************************************M2123
*********************************************************M2123
*********************************************************M4837

In essence Column A is the id for column B (A=B), column C is longer than column a and b, because it contains duplicate (M)numbers. I would like to use the defined id in column a to replace the (M)numbers in columns c

So the spread sheet with the end result would look like this

Column A****************Column B**********************Column C
1************************ M2123*************************2
2************************M3455*************************2
3************************M3... Read more

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

Read other 11 answers
RELEVANCY SCORE 77.2

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 77.2

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 76.8

Hello all. Not a regular user of Excel; but do need help in creating something that would be useful to me and a few others at work. I suppose the best way of explaining what I'm after is by giving an example.

I have a directory C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson
In Cape Nelson are a number of folders named alpha beta charlie delta echo and foxtrot and so on.

I would like to have an excel template in "Cape Nelson" with a macro that when activated names the file as whatever folder name might be in say cell A1 and whatever ever date might be in cell B1. Eg charlie_15-mar-2013.xls
This then is saved in the relevant folder. So in the end I would end up with
C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson\charlie\charlie_15-mar-2013.xls

Also the macro script would ensure that the macro was disabled in the saved file.

Hoping this is achievable and look forward to replies.
 

A:Solved: Excel 2010 - Macro to name and save file to a specific folder

Read other 16 answers
RELEVANCY SCORE 76.8

Here is part of my code where it errors out in 2013 for the sort .apply but not 2010 and can't figure out why. Help appreciated. I have recorded same in 2013 with same results.
Code:

Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Temp Data\mydata.csv"
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="<>"
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Sheets("Mobility").Select
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="="
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Range("AC2").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
Cells.Select
Range("U1").Activate
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
"Q:Q"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A:AM")
.Header = xlYes
.MatchCase = F... Read more

A:Solved: Excel 2013 Sort Macro errors out at .Apply but not in 2010

Read other 13 answers
RELEVANCY SCORE 76.4

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 76

Hello my fellow databasers.

The Information:
I am currently working for a company that is tracking their part(s)
expenses per each customer job.

All customers' expenses are entered into the same 'primary' workbook so entering information is easier than going to each customer workbook. The column headings are as follows.

Customer Name - Date - Part Used - Cost - Invoice # - Additional Notes

There is 1 primary workbook and then each customer has their own workbook.

I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for. I believe a macro is what I want though.
The question:
How, or what would I do, to have each workbook search for its relative 'Customer Name' and paste (auto populate) the information into the workbooks?

ex. I entered a part for John Doe within Primary Workbook. The second workbook sees John Doe, copies the row of all parts that have John Doe in the first column and populates it in the second workbook.

Also, would I need to create a separate Vlookup first to add a value to my customers so it wasn't text based to help the above equation work?
I know I could copy and paste each one, but sometimes we retroactively change values of costs, and for the amount of time I could save by creating ... a macro? or some formula would be well worth continued research. Any help is appreciated.

Application:

It's important for my employer to be able to see what parts were used ... Read more

A:Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

hi
A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.
Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
 

Read other 2 answers
RELEVANCY SCORE 74

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 72

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

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

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

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

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

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

Read other answers
RELEVANCY SCORE 71.6

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 70.4

I have the following code, but it doesn't put the correct date into the cell. It supposed to find "LT" (in column C) then decrement to see if the characters before the "LT" are a match and when they are it should take the date from that row in column H and move it to the row where the "LT" was found also in column H. Its making me nuts any help would be appreciated. Here is the code I have:
Sub LT_Search()
r = 0
rx = 0
For r = 1 To 10000 'Range("C1:C100000").End(xlUp).Row
tst = Right(Range("C" & r).Value, 2)
If tst = "LT" Then
rx = r
tst2 = Left(Range("C" & r).Value, Len(Range("C" & r).Value) - 2)
Do Until CStr(tst2) = CStr(Range("C" & rx - 1).Value)
If Range("A" & r).Value = "" Or rx = 1 Then Exit Do
Range("H" & r).Value = Range("H" & rx).Value
Loop
End If
Next

End Sub
 

A:Help with a macro for Excel 2010

Hi welcome to the forum,

First I suggest you put the code in a code bos for better reading
second it is good habit to Dim you variables.
Code:

Sub LT_Search()
Dim r As Long, rx As Long
Dim tst As String, tst2 As String
r = 0
rx = 0
For r = 1 To 10000 'Range("C1:C100000").End(xlUp).Row
tst = Right(Range("C" & r).Value, 2)
If tst = "LT" Then
rx = r
tst2 = Left(Range("C" & r).Value, Len(Range("C" & r).Value) - 2)
Do Until CStr(tst2) = CStr(Range("C" & rx - 1).Value)
If Range("A" & r).Value = "" Or rx = 1 Then Exit Do
Range("H" & r).Value = Range("H" & rx).Value
Loop
End If
Next r
End Sub

Anybody reading this will wonder what you are comparing, that would make it much easier to to understand.

You don't have to put the 100000 rows in there but some so that we can also test the code.

I have attached a file with code in Sheet1's VBA

All you need is put some non private data there and then reattach it.
 

Read other 2 answers
RELEVANCY SCORE 70.4

Can someone please help me to write a macro to assign to a button on a form that prints a copy of the excel page and saves the workbook to a specific file (Network/ESL-DC01/Datastore/Shared/Orders Placed) and uses the contents of a specific cell (E5) as the saved file name, when the button is clicked.
I think there might be a way to do this but it's driving me mad. Any help would be much appreciated, many thanks.
 

A:Help with macro in excel 2010 pls

I'm not an expert, but this code worked fine...Of course, you need to create the button and assign the macro PRINTandSAVE....Also, before you try it out, change the Path I used for the one you need.

Sub PRINTandSAVE()
'
'
'------------
'Print the Active Sheet
'------------
Set ws = ActiveSheet
ActiveWindow.ActiveSheet.PrintOut
'------------
' Save a copy of the workbook
'------------
Dim CSName As String

'This is the cell (E5) containing the name for the new book.
CSName = Worksheets("Sheet1").Range("E5").Value

ActiveWorkbook.Sheets.Select
ActiveSheet.Activate
'below, you should type the path where you want to save the file
ChDir "C:\YourPath\"
'Save the new workbook.
' FileFormat := 52 allows to save the Workbook containing macros
ActiveWorkbook.SaveAs (CSName), FileFormat:=52

'Close the original workbook, do not save. Remove if not needed
ActiveWorkbook.Close (False)
End Sub
hope it helps
 

Read other 3 answers
RELEVANCY SCORE 70.4

Hello

I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.

The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"

which results in a string as follows

C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\COD001_20124.pdf

All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.

My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.

I don't think it has anything to do wi... Read more

A:Excel 2010 Macro

OOPS, Serious OOPS.

I see that in my recorded macro that the path string is a literal one and not the variable I expected from the cell formula. This is obviously where I need assistance.
 

Read other 1 answers
RELEVANCY SCORE 70.4

Hi i am trying to create a macro that clear's contents in an excel file for 5 seperate tabs. When i run the macro i get a run time error 1004 and select method of worksheet class failed. Does anyone know what is wrong with the code or what the correct code would be for the macro to run succesfully? I pasted below the code from the macro:

Sub ClearContents()
'
' ClearContents Macro
'
' Keyboard Shortcut: Ctrl+d
'
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A9").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A9").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range... Read more

A:Macro excel 2010 help

Read other 7 answers
RELEVANCY SCORE 70.4

Hi there,
I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

The ErrorCatch says it's an object or applications driven error.

I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

Any help would be greatly appreciated. Thanks.

Christine

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

On Error GoTo Errorcatch
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Leon" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
"Supplier: " & Cells(E43) & vbNewLine & _
"Invoice Number:&quo... Read more

A:Solved: Error 400 in Excel Macro

Read other 14 answers
RELEVANCY SCORE 70.4

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

Code:
WorksheetFunction.Large
value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.
 

A:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!
 

Read other 2 answers
RELEVANCY SCORE 70

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 69.6

Can anyone tell how to write a macro for Excel 2010 that will do the following?

After performing some modifications to a currently open Excel workbook, I want to:
Save it to a particular folder on a backup drive
Return to the original document and save it to its original location
Close the original document
Display the "Open" window for selection of the next desired document
End the macro

I have an old macro that performed these functions for many years, but it doesn't work properly in Excel 2010 on a Win 7 computer. I've been unsuccessful in trying to record a macro to do the above, but if I knew the necessary code wording, I could write the macro from scratch using the VBA editor that's provided with Excel 2010.

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) Celeron(R) CPU E3400 @ 2.60GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2013 Mb
Graphics Card: Intel(R) G41 Express Chipset, 782 Mb
Hard Drives: C: Total - 465551 MB, Free - 392139 MB; D: Total - 11285 MB, Free - 1058 MB;
Motherboard: FOXCONN, 2A8C
Antivirus: Norton Internet Security, Updated and Enabled
 

A:Backup macro for Excel 2010

Read other 16 answers
RELEVANCY SCORE 69.6

With purchase of a new computer (HP Pavilion P7-1010t) running Windows 7, I successfully transferred a large number of Excel 97-2003 files from my former computer, and they continue to work fine in compatibility mode on Excel 2010.

For many years I've used an Excel macro to: 1) save the open document to a backup disc, 2) save the document to the original disc (hard drive), 3) close the document, and 4) pop up the 'Open' window from which to select the next wanted file (if any).

Although written originally in Excel 4.0, the macro continues to work just fine in Excel 2010. Except for new documents created in Excel 2010, for which when I execute the macro I get the following error message:

The file could not be accessed. Try one of the following:
· Make sure the folder exists.
· Make sure the folder that contains the file is not read-only.
· Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
· Make sure the file/path name doesn't contain more than 218 characters.

I'm OK on all 4 cautions cited above, but my macro no longer works on newly created documents – regardless of whether the new document is saved as .xls or .xlsx.
What is it about Excel 2010 that allows the macro to work with old documents but not with new ones?
 

Read other answers
RELEVANCY SCORE 69.6

The following macro works perfectly on the Mac I have built it on. But when I transfer it to my server - or try to run it on a Windows machine - it returns a 400 error.

Does anybody know why?

<code>
Sub NextInvoice()
Range("K9").Value = Range("K9").Value + 1
Range("F34:K40").ClearContents
Range("F7:F11").Value = "Name/Address"
Range("K7").ClearContents
Range("F29:K30").Value = "Personal message..."
Range("G21:K21").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = Range("B3").Value
ActiveWorkbook.SaveAs ActiveWorkbook.Path + "\" + NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
ActiveWorkbook.Close SaveChanges:=True
End Sub
</code>
 

A:Solved: Problem with an excel macro 400 error

Read other 16 answers
RELEVANCY SCORE 69.6

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 69.6

When I try to compile the Excel Macro, I get the following error message:

"Compile error: Procedure too large"
 

A:Solved: Excel Macro - Error Message

Read other 15 answers
RELEVANCY SCORE 69.2

In MS Excell 2003 under Tools>Macro>Record New Macro, before when I clicked on "Record New Macro" a dialog box would appear giving me the option for "Relative Reference", that box giving me the option has now disappeared.

How do I get it back?
 

A:MS Excel 2003 Macro

If you mean the Stop Recording toolbar doesn't show up at all, then while recording ; View -- Toolbars -- Stop Recording.

If you mean the button's gone from the Stop Recording toolbar, rightclick any toolbar & select Customize. On the Toolbars tab, highlight Stop Recording and click Reset...

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 69.2

I used the below macro, which worked perfectly the first time. After adding another Excel workbook to the default directory and running the macro, nothing happened. I expected the data to append to the existing worksheet, but it didn't. I would appreciate any help possible.

Sub ConsolidateAll()

' This macro opens all Excel files in the working (default) directory,
' one at a time, and and copies all filled rows from the first worksheet
' of each to the first worksheet in this workbook (the workbook containing
' this macro), only copying the header row 1 once.

Dim Filename As String
Dim ConsolWS As Worksheet 'The worksheet where the data are consolidated
Dim NextRow As Long 'Next available row in ConsolWS worksheet

Set ConsolWS = Worksheets(1)

NextRow = 1

'Look for all files ending with .xls or .xls + any character
'Can include entire path if desired. This example assumes working
'directory so no path specified.
Filename = Dir("*.xls*")

Do While Filename <> ""

If Filename = ThisWorkbook.Name Then GoTo SkipThis
Workbooks.Open Filename
Application.StatusBar = Filename & " added to New workbook."

'Base count of rows in each workbook on the last filled cell
'in column A
Dim LastRow As Long 'Last row in source workbook
LastRow = Range("A65536").End(xlUp).Row
If NextRow = 1 Then
'copy all rows including header (row 1)
Range(Rows(1), Rows(LastRow)).Copy Des... Read more

A:Excel 2003 Macro

would this work for you, I just posted a similar solution here

You can modify the ranges, as this requestor ( who never replied back) asked for a specific range to copy.
 

Read other 1 answers
RELEVANCY SCORE 69.2

HI I'm currently looking for assistance with the following.
I have one workbook with two sheets.

I would like a macro that can search text in sheet 1 cell ‘A1’ in sheet 2 ‘column A’, if there is no match I would like for it to search text in sheet 1cell ‘B1’ in sheet 2 ‘column A’ and return the value that is in sheet 2 cell ‘E1’ to sheet 1 Cell ‘F1’

Any help will be greatly appreciated.
 

A:If, then macro excel 2003

What do you want the macro to do if the first value (A1) is found in Sheet2? BTW....what you are wanting to do can probably be done using a formula instead of a macro. Is there any particular reason why you want to use a macro?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 68.8

Good Day
I am fairly new to code and writing Macros.
I am workig in Excel 2010 and creatinfg an estimating tool for bidding condtruction project
My goal is to create a user control form (Done)
Parts DB (Done)
automate the creation of the proposals and project file using the user form
My issue now is creating the macros to input the dats

my list, combo boxes see the cell ranges by the range put in the control source that works fine
I currently can not figure out the Macro to input new date if the current infromation is not listed
example: col B=project Name. row 1 is title. B2 = bakery, B3 = Bank. I ned my next entry to input new information in B4 then auto populate a new qu0te number in col A

The user form has function over the entire workbook and is located in the VBA this workbook load upon opening

I apoligizes if my terms or explinations are not clear as I said I am really very new to this and need all the assistance I cane get
 

A:Excel 2010 User Form Macro

Read other 7 answers
RELEVANCY SCORE 68.8

Hi All,

I included the macro that was developed last week for this "AIR Log." However, the creator is on leave and I only know how to read and interpret...thus giving me an error that I cannot fix:
I would also like to do the following...

In Column O have a formula... =CONCATENATE(Lxx,Mxx,Nxx)
Hide columns L-N
Remove Table Style
Set font to Arial; size to 9; Make sure all text is Middle & Center Aligned.
Row 1 has a fill color: Blue, Accent 1, Darker 25%
Option Explicit

Sub oooFormatAIRLog()

ActiveWindow.ActivateNext
Sheets(1).Name = "AIRLog"
Columns("P:Q").Delete Shift:=xlToLeft
Range("Table_AIRLog").Select
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Risk/Issue/Action Item?]") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Issue,Risk,Action Item", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Criticality]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:="High,Medium,Low", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Due Date]&quo... Read more

A:Solved: Excel Macro: Run-time error '1004'

With the sheet and the charts it's a needle in a haystack.
The error messag is that you're adressing a range or sheet which is either misspelt or hidden or non existant.
You will have to go through the macro step by step.
Open the VBA editor, loop for the macro select it and press F8 so the code will be executed line by line and it will stop when the error occurs.
Check that line and see if what it addresses exists.
Trouble shooting takes time an patience.

Good luck
 

Read other 2 answers
RELEVANCY SCORE 68.8

I have attached a spreadsheet. The first tab (Setup) has buttons 1,2,3,4,5. The purpose of these buttons is to have different years investments. I have a tab (Sheet1) that is hidden in the spreadhseet. Each macro unhides, then changes about 3 formulas on the Investment Analysis Tool tab, simply changing the cell reference. Only it does't ' change. It errors out with the above error. I've never see anything like this happen! Thanks in advance for the help.
 

A:Solved: Excel: Run-time Error 1004 During Macro

Monty75, I am sorry but the spreadsheet that you posted does not have any buttons visible.
The Code is there and I have looked at and run it and the problem is that the Code is not on Sheets("Investment Analysis Tool").Select
This is a quirk of Excel, You need to put activesheet. in from of your "Range selection" like so
ActiveCell.FormulaR1C1 = "=Sheet1!R[31]C[-2]"
 

Read other 3 answers
RELEVANCY SCORE 68.4

I'm trying to write a macro for Excel 2010 to count cells only if the background fill color is a certain color. I'm having trouble finding how to access the background color information in Excel 2010.
 

A:Excel 2010 - Macro to count if cell is a certain color

Read other 6 answers
RELEVANCY SCORE 68.4

Hi I need a Macro to support the following:

Search Column C for cell(s) containing the content of B1 and then replace all instances of B1 (located in Column C) with the content of Cell A1. and so on, and so on.

I found what I believe to be the solution here in an old thread below although the solution does not appear to be attached! Please help.

http://forums.techguy.org/business-applications/1032256-solved-macro-multiple-find-replace.html
 

Read other answers
RELEVANCY SCORE 68.4

Hi there,

To start off, I'm not too bad with excel, but I'm not very familiar with macros.
Scenario:

- Working from 3 sheets (1)Payroll sheet (current), (2)Payroll sheet (previous period), (3)Values which have changed when comparing (1) and (2)

My dilemma:

The issue is that when new entries are added to (1), it creates a mismatch with (2). I am thinking maybe a macro that runs from an if statement which compares the name fields for each entry and then runs a macro that adds a blank row so that differences will be easier to isolate.

if('sheet1!'a1='sheet2!'a1,,run macro)

Am I on point or even at that realistic in my assumption or is there another easier way to get this to work that I am not thinking of.....

PS, I hope this makes some sort of sense!

**ALSO! The rule needs to apply uniqely to about 405,000 records...
 

A:Excel 2010 - IF statement as event to trigger macro?

You can use a vlookup to check for difference.
As for you If macro, it doesn't exist.
You can however get macro to add rows for you though.
 

Read other 2 answers
RELEVANCY SCORE 68.4

Hi all,

Been browsing through this site and theres alot of helpful people out there which is good to see. I've just joined and am hoping someone could kindly help me on this problem I have.

I've got a Excel template for a file which I want to be able to update quickly and press a comand button to send an email via Outlook that sends the worksheet (not attachment) in the message body.

I need help creating that Macro for the command button that when pressed will email a specific address.

I also need help in formatting the spreadsheet. I have attached it here as well. What I want is for column C, D, E and F to have a drop down list with the information provided. I also want it so that whenever I start a new row, the same dropdown information is available again and so on.

Is this possible?

Really really appreciate anyones help!!
 

A:Excel 2003 Macro Help needed please

Read other 6 answers
RELEVANCY SCORE 68.4

Hi
Can any one help me? I have a CSV file that’s exported, (extract attached )and I want a macro to run that deletes the row with 18 or 66 or 39 in Column B. The file is a different length each time its run.
 

A:loop macro excel 2003

Read other 15 answers