Over 1 million tech questions and answers.

Solved: Problem with an excel macro 400 error

Q: Solved: Problem with an excel macro 400 error

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>

RELEVANCY SCORE 200
Preferred Solution: Solved: Problem with an excel macro 400 error

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: Problem with an excel macro 400 error

Read other 16 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 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.6

Hey everyone,
So I used this code to copy a range of cells from multiple workbooks and paste it into one workbook. It used to work fine but now it gives me the 400 error. I've been adding files to the directory that it pull the files from and it was working fine, but all of a sudden it's causing this error. Can someone please help me out?

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String
Dim wblist() As String
Dim i As Integer
Dim wbname As String
Dim j As Integer

i = 0
j = 0
FolderName = "C:\Documents and Settings\s.k\Desktop\CRs\LOG"
wbname = Dir(FolderName & "\" & "*.xls")

Application.ScreenUpdating = False

Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir
Set ycell = Range(Cells(i + 3, 2), Cells(i + 2, 28))
Set xcell = Range(Cells(2, 3), Cells(2, 28))
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wblist(i) & "]" _
& sheetname & "'!" & xcell.Address
Loop

Do While j < 100
Cells(j + 3, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],4)"
'ActiveCell.FormulaR1C1 = "=LEFT[RC[6],4]"

Cells(3 + j, 1) = Val(Cells(3 + j, 1))
Cells(3 + j, 2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CR Status.xlsx]Sheet1'!R3C1:R189C3,3,FALSE)"

If Cells(3 + j, 1).Value = 0 Then
Cel... Read more

A:Excel VBA Macro Problem - Code used to work, now 400 error

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

Hi Folks

I have an Excel 2007 .XLSM file containing the following macro that performs a Fourier analysis
Code:
Sub Macro1()
Application.Run "ATPVBAEN.XLAM!Fourier", ActiveSheet.Range("$C$1:$C$4"), ActiveSheet.Range("$E$1"), False, False
End Sub

The macro works fine when I launch the XLSM file manually. However, if I launch the file from a VB program (in Microsoft Access 2007), I get the following message:

'Cannot launch the macro ". The macro may not be available in this workbook or macros may be disabled'

The code I use to open the workbook and relevant add-ins:
Code:
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Open(MyPath & "Template1.xlsm")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISATPVBAEN.XLAM")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISANALYS32.XLL")
objXL.RegisterXLL "Analys32.xll"
I then go to the worksheet and try to manually execute macro1 but I get the above error message. Note that:
"enable all macros" is set in Developer/Macro Security
"trust Access to the VBA etc." is turned on. in in Developer/Macro Security
I tried placing the macro in my PERSONAL.XLSB file to no avail (not sure this was worth it, but I found a suggestion to that effect on another forum)
I tried executing macro1 from the VBA program as follows but got the same error:
Cod... Read more

A:Solved: Macro Error when launching Excel as OLE Automation Object

found the answer on:
http://support.microsoft.com/kb/270844

I had omitted the auto open macro. Statement:

objXL.Workbooks("atpvbaen.xlam").RunAutoMacros 1
 

Read other 1 answers
RELEVANCY SCORE 68

Hello

I have design a macro to open up print preview for a particular area, as such:
Sub PrintSteelTables()

'PrintSteelTables Macro

ActiveSheet.PageSetup.PrintArea = "$A$1:$M$59"
With ActiveSheet.PageSetup

' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(0.5)
' .BottomMargin = Application.InchesToPoints(0.5)
' .HeaderMargin = Application.InchesToPoints(0.2)
' .FooterMargin = Application.InchesToPoints(0.2)
' .PrintHeadings = "The Aluminium Lighting Company - Cost Analysis"
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 0
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPotrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = False
' .FitToPagesWide = 2
' .FitToPagesTall = 2
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub

My issue is that the number rows needed might alter depending on the inputs into the original table. Currently it is down to 59, which is 9 plus the 50 years of data. i wo... Read more

A:Solved: Problem with print macro in excel

Read other 6 answers
RELEVANCY SCORE 67.2

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 67.2

I wonder if anyone can help with the following.

I have a table , A8:V36. Peoples names appear from cells B9:B36.
Dates are entered from C8:V8 with a range name defined as 'Date'.
Integer values appear in cells C9:V36

I want a macro to be able to sort the table by selecting one of the dates and sorting in descending order of value against each person for the date just highlighted.

If I were to do the job manually , I would highlight the table with the 1st Row of the highlighted area including the headings "Name" , "Date1" , Date 2" , etc up to "Date 20".
If I then clicked Data then Sort , a dialogue box would appear. Possible inputs to it , reading from Left to Right would be 'Column [Sort By]' , 'Sort On[Values etc]' and 'Order[e.g A to Z]'

In the 'Sort By' box a drop down list appears containing all the headings of each column. This includes whichever date you require to sort the underlying values by.

So what would the macro script be to achieve the presentation of that dialogue box , allow input to it and then activate the sort once the user had clicked OK ?
I hope that I have made the application clear. Help would be appreciated.
 

A:Solved: Excel macro required for a sort problem

Read other 16 answers
RELEVANCY SCORE 62.8

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 62.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 62.8

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 62

Hi guys,

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

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

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

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

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

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

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

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

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

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

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

Apologies again
 

Read other 1 answers
RELEVANCY SCORE 61.6

I have created a macro that goes through my workbook and is supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that just says "400" in it with a red circle that has a white "X" through it. Here is the code:

Sub Clearcolors()
Dim ws As Worksheet
Dim RngH As Range
Dim RngHD As Range
For Each ws In ThisWorkbook.Worksheets
ws.Select
Set RngH = ws.Range("A1:I" & Range("I900").End(xlUp).Row)
For Each RngHD In RngH
RngHD.Interior.ColorIndex = xlNone
Next RngHD
Next ws
End Sub

What can I do to fix this problem? Any suggestions?
 

A:Solved: VBA error "400" running an Excel macro.

Read other 15 answers
RELEVANCY SCORE 61.6

Hi Guys:

I have created a macro( 2 line macro actually) that goes through my workbook and it supposed to clear the value from cells b1:b68 on main page. When I try to run the macro with the sheet protected I get a pop up that just says "400" in it with a red circle that has a white "X" through it, whit the sheet unprotected works perfectly !. Because it is a survey I need to protect teh content, to be manipulated by many. Can somebody give a clue? Here is the code:

Public Sub Clear()
Sheet1.Cells.Range("B17:B68").ClearContents
End Sub
Windows 7
MExcel 2010

Many thanks

Rod
 

A:Solved: error "400" running an Excel macro

Read other 8 answers
RELEVANCY SCORE 60.4

Hi.
I created a macro for one of my co-wokers that:

Refreshes data using MS Query to pull data from an Access database.
It then formats fonts and headers and stuff correctly on that worksheet.
It then creates a pivot table on a new sheet (using the wizard) based on this refreshed info (it's actually based on a cell range 8 columns wide and 10,000 rows long excluding the header, just in case we get a huge number of claims on one ship).
It then formats the pivot table correctly and renames the worksheet.
Finally, it moves the worksheet to where it should be (still in same workbook).

This worked the first time, and still works now, except for the last part, where it moves the sheet. I now receive an error:

Run-time error '1004':
Application-defined or object-defined error.

I hit Debug to see where the problem was (I know very little VB) and this is what was there. The Bold part is what was highlighted in yellow in the VB window:

Sub Refresh_Pivot()
'
' Refresh_Pivot Macro
' Macro recorded 04/09/01 by Liz Clark
'
Range("A10").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A10:H10").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 11
With Selection.Font
.Name = "Times New Roman"
.Size... Read more

A:Excel 97 macro error

Read other 6 answers
RELEVANCY SCORE 59.6

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

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

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

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?

Thanks!

Read other answers
RELEVANCY SCORE 59.6

Hi there,

I'm trying to do a simple macro that opens an Excel template. Copies fields from a row in an open Excel sheet to the newly opened template. Prints the template. Puts a date into that row. Stop.

I got an error at r = ActiveCell.Row until I deleted Option Explicit. Also got an error at the copies code that I turned into text. Now I get a Compile Error: Named argument not found at IgnorePrintAreas:=False

I tried to delete that line but then I get an error for the entire print code.

Does anybody know where the error sits? I'm trying to print to the default printer.

Thanks, Christine

Code:
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'ActiveWorkbook.SaveAs FileName:=BkSrc.Cells(r, 3).Value
'Copied from Zack Baresse, I think I will need this
'Call ToggleEvents(False)
'Set ws = ThisWorkbook.Sheets("CodingTemplate")
'If WbOpen(BkDest) = True Then
'Set wkb = Workbooks(FileName)
'blnOpened = False
'Else
'If Right(FilePath, 1) <> App... Read more

A:Compile Error in Excel VBA macro

I have worked a bit more on this and above problems seem to be resolved. But, big but, I now get a run-time error '9'. Subscript out of range.

I looked it up and it says it means one of the workbooks isn't open but I'm working with 2 books, one which I have open to start with and the other one I do open.

Any ideas? Thanks, Christine

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
'Originally found written by Jake Marx
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'Copied from Zack Baresse, I think I will need this
'http://www.mrexcel.com/forum/showthread.php?t=292030
Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("CodingTemplate")
... Read more

Read other 1 answers
RELEVANCY SCORE 59.6

=IF(ISERROR(100),"NA",100) appearing in Excel 2007 shortcut key macro?

First, my disclaimer is that I am not familiar with Excel or VB!​

That being said, one of my users is getting an error when running a macro in Excel 2007 from his personal.xlsm.​
The macro is a simple color change which we have re-recorded several times but when he attempts to run the macro with a shortcut key the field populates with =IF(ISERROR(100),"NA",100) where 100 was the test value of the original cell. If you run the macro from the menu it works correctly. ​
Macro is:​
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
End Sub​
Any suggestions are greatly appreciated. ​
Thanks!​

 

Read other answers
RELEVANCY SCORE 59.6

I can run this macro on my own machine but when I transfer it to another computer, I get the following error: Run Time error 1004 Cannot open PivotTable source file 'headcount by area' - any ideas what's wrong??!
 

A:Error running a macro in Excel

Read other 6 answers
RELEVANCY SCORE 59.2

I have a new Dell laptop with Windows ME & Office XP. When I go to record a new macro, I can enter the description and other info, but when I click 'OK' to start recording the Macro, I get an "Unable to record" error message. The error message has no help button or details button, just an 'OK' button. I have everything for Office installed.....Please help, thanks
 

A:Unable to record macro error in EXCEL >:-(

Read other 7 answers
RELEVANCY SCORE 59.2

Hi,

I have a bix excel file with lots of macros written in it. While running a particular macro, I get "Subscript Out of Range" error in some specific rows.

The same workbook when used by others work well.

Can anyone help?
 

Read other answers
RELEVANCY SCORE 58.8

Every time I record a macro and create a pivot table it seems to work very well until I close the sheet. When I open up an a new sheet and try to re-run the macro the debugger opens and tells me that there is an error in this line. What am I missing?

The macro is save in the personal workbook.
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")
 

A:MS Excel Macro Problem

Can you provide a sample?
 

Read other 3 answers
RELEVANCY SCORE 58.8

HI everyone,

This is what i have right now and it works: I select a cell and run my macro. It ask me what code do I want to use, Then it goes to InfoSheet and copy that cell that has that name. Then goes to where the active cell is and pasted it. Then it goes to Infosheet again to the cell that has that name and move 3 spaces to the right copy this cell and go back to the active sheet (I guess) and paste it where the active cell 20 cell to the right.
Probably you all are not following this. I am not very good explaning what I did LOL.. so here is the code. What I would like to do now is the samething but if I select several cell to paste the same sequence on all the selected cell.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/22/2008
'
' Keyboard Shortcut: Ctrl+d
'
Dim Code
Code = InputBox("Please enter one of the following codes: " & vbCrLf & " " & vbCrLf & "SH500, FIX550, XO525, XOX530, CASE800, CASE810" & vbCrLf & "FIX850, PIT835, SH515, XO527, DH, FIX750" & vbCrLf & "FIXTH750, FIXTH, FIXTV, XX, XOX730, DHHC, SH4100" & vbCrLf & "XX4125, FIX4150, FIX4150TV, FIX41502TV, FIX1450" & vbCrLf & "TD1400, SGD9900XO, SGD9900OXO" & vbCrLf & "")

Select Case Code
Case "WW500", "ww500"

Sheets("INFO SHEET").Select
Range("WW500").Select
Selection.Copy
Sheets("Copy A")... Read more

A:EXCEL macro problem..!

Read other 8 answers
RELEVANCY SCORE 58.8

Hi,
I want to create a macro that copies the contents of the first column say 20 rows and then in column two calculates 5% of the value in column one.

I can do the calculation and create/edit the macro, the part Iím having problems with is:-

Say Spreadsheet one has 100 rows and Spreadsheet two has 200 rows how can I make the macro dynamic enough to copy the different rows in each spreadsheets have?

I thought I could do it using a named range but cannot get it to work!
 

A:Excel Macro Problem

Without your file or a view of your existing macro I can only give you a generic response.

The way I normally deal with dynamic ranges is to have the macro find the last row of a range like this;

lastRow=Cells(Rows.Count,1).End(xlup).Row

This will find the row of the last populated cell in Column A (if you want column B use 2 etc.)

Then, when defining the range I need to copy I would do this

Range("A1:A" & lastRow).Copy
 

Read other 2 answers
RELEVANCY SCORE 58.8

Hey

My data looks like this
It doesn't show the tabs that good but as you can see it got more columns
chr8 7222992 7727734 G07D5563-_Affy250 0 + 7222992 7727734 0.0.199
chr14 105042939 106356482 G07D5563-_Affy250 0 + 105042939 106356482 0,0,199

I need to save this to tab sepperated text file and so far thats all k.
But when i do this with a macro that does exactly the same as i did manually.
I get my last column like this:
"0.0.199"
"0.0.199"

This gives an error in my next program.

Some1 can maby help with this?

thanks
 

A:Excel macro problem

Read other 7 answers
RELEVANCY SCORE 58.8

Hello,

I'm trying to make a macro that will add (and show the sum) of some values in column D, IF their values in column A are the same. (all the A's in one group, the B's in another, etc)
Let me show you:

Therefore, if the cell in column A is an A, I would like it to add the values of the cells in column D, and display them in F2
Then add all the B's values into G2
and so on.
So it would look something like this:

Is this possible? It's the only remaining piece in this big project, and I can't find the solution anywhere...

Thank you for your time,
Danniel
 

A:Problem with a Macro in Excel

Read other 15 answers
RELEVANCY SCORE 58.8

Hi:

I haven't worked a while lot with complicated MACROS...and am trying to work this spreadsheet that was forwarded from someone else, and trying to recreate what they do. Have to run a few macro's. 3 of the 6 run. the 4th one fails and displays the following message... Any help is much appreciated...Thanks

RUN TIME ERROR 1004--The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changig the name of a PivotTable field, you must type a new name for the field.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"daily_chs!R1C1:R15192C12").CreatePivotTable TableDestination:="", TableName _
:="PivotTable"
 

A:Excel Macro Problem

Read other 6 answers
RELEVANCY SCORE 58.4

Hi,
I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice,
then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":
Sub Save_Sheets_To_New_Books() 'INVOICES
Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"
Dim strDate As String 'todays date
'save the sheets to new books within the active folder and print them
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd")
THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted
ActiveSheet.Copy
Call DeleteNamedRanges
Call PrintAreaAndPasteSpecial
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False         
ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_&quo... Read more

Read other answers
RELEVANCY SCORE 58

hi ppl, im having a trouble with these code cant make it work i have excel 2010, here it goes:
Code:

Sub filtro()
Dim bb As Long
Dim box As String
bb = Range("D" & Rows.Count).End(xlUp).Row
box = InputBox("Filtrar:")
With Range("D1:D" & bb)
.AutoFilter Field:=1, Criteria1:="<>" & box
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End Sub

i put my text on the inputbox an after that it deletes everithing in collumn D1 and it leaves me the text i inputed with the inputbox in the cell D1
 

A:excel macro problem with input box

well thats because you macro is filtering to Not show the values you put in the Input box, the last line removed the filter and shows the values that were filtered out with every other row being deleted....

TIP... position VBA window and sheet side by side... or on separate monitors and press F8 to step through your code, then you can see it all happen in real time ( slowly).
 

Read other 1 answers
RELEVANCY SCORE 58

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 58

I have a little problem with some VBA in MS-Excel with pivot tables (using Excel 97). The pivot table is built from a saved report definition. This definition can be changed (not usually), which adds or removes fields. When this happens, the Excel workbook is refreshed, but it gives the error of;
The PivotTable field name is not valid. To create a PivotTable, you must use data that is organized with labeled columns.

Anyway, their are 4 columns on the data sheet, each labelled 'Price' 'Quantity' 'Cost' 'Currency'. None of the columns have changed, so no pivot fields have been deleted. However, I am updating the source data of the pivot table (in case columns are added or deleted in the report definition).

The error occurs during the following;
For Each pvtTable In ActiveSheet.PivotTables
For Each pvtField in pvtTable.PivotFields
If Not pivotFieldInSourceData(myColumn.Count, pvtField.Name) Then
pvtField.Delete
End If
Next
Next
pvtTable.SourceData = newRange

This sets newRange as being the entire range of data on the data sheet (in this case Data!A13531). It then goes through the pivot table and makes sure that the fields in the pivot table exist in the columns on that data sheet and if fields are removed, they are in turn, deleted from the pivot table. This works fine. However, when it sets the source data to the new data range, the error appears.

The weird thing, is that the pvtTable.SourceData is currently showing C1:C4 from the dat... Read more

A:Excel PivotTable macro problem

Read other 9 answers
RELEVANCY SCORE 58

here my situation: I have created 2 worksheets. Both are contact list.
the 1st list is of People who work in the same building as I. The second is
a list of nationwide remote users. My boss emailed me a third sheet
containing names from both list combined. the question I have is can I
write a macro to check the third sheet against the 1st two then seperate
them back out? if you need more info let me know
 

A:Solved: need help with excel macro

Read other 8 answers
RELEVANCY SCORE 58

I am running Excel 2003. I am creating a macro that open files from a external hard drive. I was hoping to find a way for the macro to find the Drive letter of the external hard drive instead of me having to statically assign the drive letter.

Any help with this would be great.
 

A:Solved: Excel Macro

Read other 9 answers
RELEVANCY SCORE 58

Hi,

I have following data with me and this data extends till 1000 rows.

IDSYSTEMASSOCIATED_ABC_PROCESSASSOCIATED_XYZ_PROCESS9ABC Stage IIIABC.101-1XYZ.103-459ABC Stage IIABC.101-2XYZ.103-459ABC Stage IABC.101-3XYZ.103-459XYZ Stage IABC.101-4XYZ.103-459ABC Stage IABC.102-2XYZ.103-469ABC Stage IABC.103-2XYZ.103-479XYZ Stage IABC.103-4XYZ.103-4810ABC Stage IABC.104-1XYZ.105-4-310ABC Stage IIIABC.104-2XYZ.105-4-410ABC Stage IIIABC.104-6XYZ.105-4-510XYZ Stage IIABC.105-1-1XYZ.105-4-610ABC Stage IVABC.106-1XYZ.107-3

I am looking for a macro which will give me results as mentioned below.

IDABC / XYZ METRICABC XYZ STAGES9ABC.101-1
ABC.101-2
ABC.101-3
ABC.101-4
ABC.102-2
ABC.103-2
ABC.103-4
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-46
XYZ.103-47
XYZ.103-48
ABC Stage I
ABC Stage II
ABC Stage III
ZYZ Stage I
10ABC Stage I
ABC Stage III
XYZ Stage II
ABC Stage IVABC.104-1
ABC.104-2
ABC.104-6
ABC.105-1-1
ABC.106-1
XYZ.105-4-3
XYZ.105-4-4
XYZ.105-4-5
XYZ.105-4-6
XYZ.107-3



In Column 'ABC / XYZ METRIC' the values from coulmns 'ASSOCIATED_ABC_PROCESS' and 'ASSOCIATED_XYZ_PROCESS' are combined for the respective ID.

Thanks in advance,

Regards,
Akki
 

A:Solved: Excel Macro Help

I don't know how the table are disorganized after submmiting the querry. Submmiting new query with attachment.

Regards,
Akki
 

Read other 1 answers
RELEVANCY SCORE 58

hi... i am really having a problem with creating a macro, could you please help me.

i have to copy content from a auction website, paste it into excel.
this is what the content looks like

Auction - 125458
view bigger image --- name of item
ref number --- start date
end date --- price of item
page views
users tracking
no bids
relists remaining (this is sometimes left out and causes my stress)
copy or relist
edit
close
delete
the above content over 3 columns and over different rows.
i managed a simple macro to put these into columnís, but the problem being that sometimes there is not the same amount of rows with the relists remaining left out sometimes....
so what i need is a macro to put the data above into columnís,
eg... word starting with auction = c1
word starting with view bigger = d1
word starting with item name = e1
---------- then when the next word starting with auction = c2

could you pretty please help me with this
 

A:Solved: excel macro help please

Read other 9 answers
RELEVANCY SCORE 58

Hello, I need some help with a macro. I am writing an If...Then...Else statement. The If is based on whether the current cell is blank. This seems simple enough from my experience with Lotus 1-2-3 macros but I can't make it happen. Here is where I am -

If ActiveCell.Value.Isnull = True Then
Blah...Blah...Blah
Else.... Help me! Thanks
 

A:Solved: Excel Macro (VB)

Welcome to the board.

If ActiveCell = "" Then
Do this
Else
Do that
End If

It's the same as IF formula, only in code.

Rgds,
Andy
 

Read other 2 answers
RELEVANCY SCORE 58

Ok not sure if this can be done or not, but hopefully it can.
What I want to create is a macro or something that would read the values in column A, if it finds a duplicate number it would insert a row under it

i.e.

5
5
6
7
8
It would insert a row between the 5 and 6.
Is this possible?
 

A:Solved: Excel Macro - Not sure if it can be done

Read other 16 answers
RELEVANCY SCORE 58

i have created a spreadsheet that has a list of names down the left, days of the week and dates across the top and a "user' choose in the data fields.The spreadsheets are in one workbook and i have a sheet for each month.

What i need to do is create a form that will enable users to choose a "Date" period ie start and end dates and choose a "name". i then need this form to have a button on it that when clicked would generate an email to the relative person named which would have all of the relevant data in it.

I know how to create the actual form, it's linking everything to it thats the problem, so would really appreciate some help.

Hope this all makes sense !!!
 

A:Solved: Excel macro help req

Read other 15 answers
RELEVANCY SCORE 58

Hello,

I've built an excel spreadsheet and am having trouble with a part of a macro. The relevant section of the macro that I am having trouble with is below:

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$1:$A$1094"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

The spreadsheet is basically a template, so as I get new data, I open it up, dump the data in, run the macros and save. The problem is, the data is often different sizes, so one day I may have 1000 rows, and the next 1400. This is not a problem with generating charts, since I can just select that it plots cells 0-2000, and it will ignore cells without data, but if I do this for the above regression, the macro will not fully run and I get an error. It's not really a big deal, as I can open up the macro every time and just edit the range to fit the data I have, but other people often need to use it so I'd like to make it as simple as copy, paste, and run macro1.

Is there a way to insert a macro within the macro so excel automatically searches the column and then runs the regression?

Something like this

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("Only rage of cells with data in them"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

I've seen a macro here on how to find cell... Read more

A:Solved: Help with excel macro

Read other 9 answers
RELEVANCY SCORE 58

Hi Friends,

I'm very much new to Macro in excel. I wanted to Run Macro in the following situation :
I have 2 Excel Book having same header but some data which to be consolidated in a 3rd Book. I recorded macro in 3rd Book the macro function is to " to open Book 1 & Book 2 & consolidate in Book 3" on running the recorded macro.

But after saving the macro and running tha macro again with some new data in Book 1 & Book 2 it gives the following error message "Run-time error '9':
Script out of range

and on debug the following macros are displayed.
---------------------------------------------
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book2.xlsx"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xlsx"
Windows("Report.xlsx").Activate

Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\Administrator\Desktop\[Book1.xlsx]Sheet1'!R1:R1048576" _
, _
"'C:\Documents and Settings\Administrator\Desktop\[Book2.xlsx]Sheet1'!R1:R1048576" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub

--------------------

can anyone help. I'll be very much grateful to you.

I'm using excel 2007 but I dont think it has got to do something with macro!

thank you in advance.

Hemen
&nbs... Read more

A:Solved: Help in macro (Excel)

This message usually occurs when the code is trying to call a workbook or worksheet that is not available. Your code opens two workbooks from your desktop (Book2.xlsx, Book1.xlsx) and then is trying to activate a workbook called Report.xlsx. If this workbook is not already open then you will get the error. You need to add some code to open the workbook prior to activating it.

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 58

I am have a form in excel that when a certain person types his name at the end the following cell will put in date that he signed it. The problem is that when you e-mail the doc. the following day (or whatever day) the date changes to the current date and not the actual date that the doc was signed. (by signed i mean the person typing his name there) Here is the code that is currrently in the doc.
=+IF(F36="Persons Name",H43," ")

I'm not sure if there is some kind of formula for this or do i have to write a macro for it. If i do have to write a macro for it, could someone please help me out with it.
 

A:Solved: Excel Macro

Read other 6 answers