Over 1 million tech questions and answers.

Compile Error in Excel VBA macro

Q: Compile Error in Excel VBA macro

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) <> Application.PathSeparator Then
'FilePath = FilePath & Application.PathSeparator
'End If
'Set wkb = Workbooks.Open(FilePath & FileName)
'blnOpened = True
'End If
'Copy Cells - (r,3 to K8)(r,4 to D21)(r,5 to M21) (r,6 to D13)(r,7 to D32) (r,8 to E32) (r,9 to F32) (r,10 to M32)
BkDest.Cells(K8).Value = BkSrc.Cells(r, 3).Value
BkDest.Cells(D21).Value = BkSrc.Cells(r, 4).Value
BkDest.Cells(M21).Value = BkSrc.Cells(r, 5).Value
BkDest.Cells(D13).Value = BkSrc.Cells(r, 6).Value
BkDest.Cells(D32).Value = BkSrc.Cells(r, 7).Value
BkDest.Cells(E32).Value = BkSrc.Cells(r, 8).Value
BkDest.Cells(F32).Value = BkSrc.Cells(r, 9).Value
BkDest.Cells(M32).Value = BkSrc.Cells(r, 10).Value

'Print Coding Template To Default Printer
Worksheets.PrintOut _
From:=1, _
To:=1, _
Copies:=1, _
Preview:=False, _
ActivePrinter:="", _
PrintToFile:=False, _
Collate:=True, _
PrToFileName:="", _
IgnorePrintAreas:=False

'Put Date executed in Column
BkDest.Cells(r, 15).Value = Date
If blnOpened = True Then
BkDest.Close SaveChanges:=False
End If

Errorcatch:
MsgBox Err.Description

End Sub

RELEVANCY SCORE 200
Preferred Solution: Compile Error in Excel VBA macro

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

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

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

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")
If WbOpen(FileName) = True Then
Set wkb = Workbooks(FileName)
blnOpened = False
Else
If Right(FilePath, 1) <> Application.PathSeparator Then
FilePath = FilePath & Application.PathSeparator
End If
Set wkb = Workbooks.Open(FilePath & FileName)
blnOpened = True
End If
'Copy Cells - (r,3 to K8)(r,4 to D21)(r,5 to M21) (r,6 to D13)(r,7 to D32) (r,8 to E32) (r,9 to F32) (r,10 to M32)
BkDest.Cells(K8).Value = BkSrc.Cells(r, 3).Value
BkDest.Cells(D21).Value = BkSrc.Cells(r, 4).Value
BkDest.Cells(M21).Value = BkSrc.Cells(r, 5).Value
BkDest.Cells(D13).Value = BkSrc.Cells(r, 6).Value
BkDest.Cells(D32).Value = BkSrc.Cells(r, 7).Value
BkDest.Cells(E32).Value = BkSrc.Cells(r, 8).Value
BkDest.Cells(F32).Value = BkSrc.Cells(r, 9).Value
BkDest.Cells(M32).Value = BkSrc.Cells(r, 10).Value

'Print Coding Template To Default Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Put Date executed in Column
BkSrc.Cells(r, 15).Value = Date
If blnOpened = True Then
ActiveWindow.Close SaveChanges:=False
End If

Errorcatch:
MsgBox Err.Description

End Sub

Read other 1 answers
RELEVANCY SCORE 77.2

Hi,

I created a macro which is fairly too large. The macro is as follows:-

Sub VA()
Dim FilePath As String, fName As String
Dim aWB As Workbook, sWB As Workbook
Dim ws As Worksheet
Dim ColName As String
Set aWB = ThisWorkbook
'~~> Delete sheets
For Each ws In aWB.Sheets
If ws.Name <> "Sheet2" Then
If ws.Name <> "Sheet1" Then
If ws.Name <> "Summary" Then
If ws.Name <> "Summary Kg" Then
If ws.Name <> "Inventory" Then
If ws.Name <> "Caustic Soda" Then
If ws.Name <> "PCL3" Then
If ws.Name <> "PCL5" Then
If ws.Name <> "POCL3" Then
If ws.Name <> "TPPI" Then
If ws.Name <> "TBPO" Then
If ws.Name <> "TDP" Then
If ws.Name <> "TTDP" Then
If ws.Name <> "UPH 207" Then
If ws.Name <> "WDG" Then
If ws.Name <> "WDG DOW" Then
If ws.Name <> "MNZ 85 %" Then
If ws.Name <> "MNZ 80 %" Then
If ws.Name <> "MNZ 75%" Then
If ws.Name <> "Maneb 80%" Then
If ws.Name <> "Maneb 90%" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next ws
Set aWB = ThisWorkbook
'~~> Delete sheets
Fi... Read more

A:Compile Error in Macro - Procedure too large

Hi, welcome to the forum,
Looking at your code I can see it's too large, and it's very complicated and I even think unnecessary IF statements, it's really the last one that counts.

I suggest you break it down to smaller portions which are called when needed.

I suggest you write down the way you look at it and then try and translate these steps to code.

write down a step by step string of thought.
Attach a sample with code which will make it easier to read since we don not have the same structure and are not going to try and rebuild your sheet.

I also suggest you mention which version of Excel you're using
 

Read other 1 answers
RELEVANCY SCORE 70.4

Good Morning

With your help, I recently did a macro that let me do an e-mail out of Outlook. This is similar but lots more complicated as I have 2 sheets, one with the contact list and the information going into the e-mail is per active row and the other one with information that's the same for each mail.

Here's the code and it gives me the above error. It stumbles at GetBodyText in the .Body section. I'm trying to insert text from a .txt file into the body but it also needs to have the correct address (Dear ...) and my signature. So maybe I can't use this in combination?

As usual, it's pretty much cobbled together:

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

Function GetBodyText(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)
GetBodyText = ts.readall
ts.Close
End Function

Sub ProductCorrection()
'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

r = ActiveCell.Row
Path = [B5].Value

Set sh1 = Sheets("Contacts")
Set sh2 = Sheets("ProductCorrection")
Set rng = ... Read more

A:VBA in Excel: Compile Error: Argument not optional

Read other 16 answers
RELEVANCY SCORE 70.4

Each time I open and then again when I close I get a pop up window titled: Microscope Visual Basic and it reads "Compile Error in Hidden Module: DistMon". I click on OK and Excel proceeds to open or close as the case may be. How do I get rid of this annoying pop up?
 

A:Excel: Compile Error in Hidden Module

The cause and solution is here. http://support.microsoft.com/kb/307410
Although it applies to earlier Windows versions, I think it still applies to your problem.
 

Read other 3 answers
RELEVANCY SCORE 69.6

I wonder if anyone can help?

I&#8217;m getting receive a "Compile error in hidden module" error message when I start Word or Excel.

With Word it is "Compile error in hidden module. AutoExec" at entry and exit; and with Excel it is "Compile error in hidden module. AutoExecNew" on entry and "Compile error in hidden module. DistMon" on exit.
Once I acknowledge the 'error' then the program works ok.

I have followed the advice in http://support.microsoft.com/default...;EN-US;q307410 , but to no avail.

I&#8217;m using Vista Home Premium, have Adobe Reader 9.0, do not have acrobat or Norton, and have renamed all instances of pdfmaker showing on my system (even in hidden files) to _pdfmaker, and still have the problem!

All the Google references to this problem seen to follow a familiar theme which I seem to have done, does anyone have any ideas please?
 

Read other answers
RELEVANCY SCORE 69.6

I am doing an income tax (India) return. this is excel utility with a macro. there is one link "generate xml". when I click that I get an error message "compile error in hidden mdoule". what is the solution,. I saw earlier message in the forum (https://forums.techguy.org/threads/solved-word-and-excel-2007-compile-error-in-hidden-module.695619/) but those do not solve the problem. when I click nitropdf link and I get a message "We're sorry, mate!
That page cannot be found"
what to this is urgent. Is excel corrupted and if so have I reinstall?
 

Read other answers
RELEVANCY SCORE 68.4

First of all, my boss is running MS Office 2007 on Vista Ultimate. Both Word and Excel create error messages upon startup, but not closing. Here are the messages.

Excel 2007
"Compile error in hidden module: ThisWorkbook"

Word 2007
"Compile error in hidden module: uifunctions"

I've looked at all of the threads from this forum and other forums, even the microsoft support site but can't seem to find what is going on. I've searched for both pdfmaker files and found nothing, tried looking at the references after going into Visual Basic from both Word and Excel. There is only one add-in on there and it is Nitro PDF Professional, but it doesn't show up in the list that can be unchecked. I'm wondering if this started because we just recently updated to the newest version of Nitro PDF, 5.3.2.3. But he was having trouble with it, so he rolled back to version 5.3.0.14. Oh yeah, I've also tried the Office detect and repair, but it didn't come up with anything either...big surprise.

Thanks for any help in advance
 

A:Solved: Word and Excel 2007 compile error in hidden module

Well, it was a problem with Nitro PDF, just not exactly sure how. It was reinstalled without updating to the latest version. It looks like something with the new version messed with the add-ins for ThisWorkbook and uifunctions for Excel and Word respectively.
 

Read other 3 answers
RELEVANCY SCORE 64.8

New laptop with win 8. Moved my MS Office XP, Professional 2002 over. Now with Outlook, Word and Excel get variations of an error message: Microsoft Virtual Basic, Compile error in hidden module: Auto Exec, or Auto Exec New or Auto exec dist mon. Any ideas?
 

A:Word & Excel error "Compile error in hidden module"

A quick check of the Trust Settings, for location and enable macros.
 

Read other 3 answers
RELEVANCY SCORE 62.8

I've recorded a Macro in which I select several ranges of cells within a worksheet to be cleared, clear them, then save. It seems straight forward enough, and I've done this multiple times before on other duplicate worksheets without issue; however, I've done this several times on this sheet, but every time results in the same error for the same line in the code for this particular worksheet.

I don't know enough about VBA (hense why I recorded the macro rather than writing it) to trouble shoot it. I could use some help. The macro is tied to a button, and appears when I click the button. Here's the code, and the line that is highlighted when the error pops up:

Sub Sheet_Clear()
'
' Sheet_Clear Macro
' Resets all data entry cells back to zero. Macro created by J. Streck on 10/21/2010.
'
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Are you sure you want to clear your sheet? Click YES to clear, or NO to stop.", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If
Range("Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67").Select
Range("FQ67").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85" _
).Select
Range("FQ85").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS10... Read more

A:Solved: Excel 2007 "Compile error: Argument not optional".

Forgot to add that I'm using Windows XP Pro
 

Read other 3 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

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 59.6

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

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.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.2

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 59.2

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

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 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.4

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 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.4

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 58.4

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 58.4

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 58

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 57.2

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

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

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

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 57.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 57.2

I'm attempting to write a bit of VBA code that would perform a very useful function in my office: create a log of data. I have multiple XL files in multiple folders/subfolders from which I would like to extract a number of named ranges—the same names in each XL file—and compile that data into a spreadsheet. I figure there's two primary pieces of code that I need: (1) parsing through a tree of subfolders, identifying the XL files, and then opening that file; then (2) retrieving select named ranges and importing them into a spreadsheet. Any help would certainly be appreciated...not sure if I delve into FSO, or work around it.

Thanks in advance!
 

A:Need to Compile named ranges from multiple Excel files

Hi welcome to the board.

If found this code on the web

Code:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
MsgBox .FoundFiles(lCount)

[COLOR="Red"] 'Open Workbook x and Set a Workbook variable to it
'Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE

'wbResults.Close SaveChanges:=False[/COLOR]
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

The code in RED I disabled and added the Msgbox.

This will only search in the folder indicated, you can change this, there i... Read more

Read other 1 answers
RELEVANCY SCORE 56.8

I've been trying to implement a macro to apply a defined chart format to every selected chart. I've been using a post from this forum a few years back http://forums.techguy.org/business-applications/823459-macro-format-charts.html to help, and it's working great for a single chart. However, I'd like to enable it to run across several selected charts at once, and that is giving me problems that I can't get my head around.

I'm trying to use this code to run through the objects in the selection:

Sub ReFormatSelectedCharts()

Dim ThisChartObj As ChartObject

Dim ThisType As String
ThisType = TypeName(Selection)

Select Case ThisType

Case "DrawingObjects"

For Each ThisChartObj In Selection

Call FormatThisChart(ThisChartObj)

Next ThisChartObj

...Click to expand...

However, it gives me a type mismatch when I try to use it. I've investigated by implementing the following code instead of the for each loop:

X = Selection.Count
ReDim arrCharts(1 To X)
For i = 1 To Selection.Count
arrCharts(i) = Selection(i).Name
Debug.Print arrCharts(i)
Next iClick to expand...

Interestingly, it seems that the second chart in the selection is being treated as a button by Excel - so in the debug output in the case with three charts I'll see something like:

Chart 10
Button 1
Chart 5Click to expand...

while for two charts it'll just be
Chart 10
Button 1Click to expand...

I've tried it with different pairs and sets of chart... Read more

Read other answers
RELEVANCY SCORE 53.6

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 53.6

Hi,

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

Thanks for reading!
 

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

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

Read other 1 answers
RELEVANCY SCORE 53.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 53.6

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 53.6

Hello,

I need help to create an Excel macro that would

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

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

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

Looking forward to your help!

Thanks a lot.
Mzz
 

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

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

Read other 1 answers
RELEVANCY SCORE 53.2

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

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

Hello, and welcome to the board!

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

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

Option Explicit

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

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

Read other 1 answers
RELEVANCY SCORE 53.2

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

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

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

Any help will be much appreciated.
Thx
 

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

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

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

Read other 3 answers
RELEVANCY SCORE 52.8

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

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

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

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

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

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

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

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

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

Read other 13 answers
RELEVANCY SCORE 52.8

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

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

Can anyone help?

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 52.8

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 52.8

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

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

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

Does anyone have experience with similar questions?

Thanks
 

A:Excel Macro runs fine..then excel crashes

Read other 11 answers
RELEVANCY SCORE 52.4

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 52.4

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 52.4

When I open up Word files I get an error that reads:

Microsoft Visual Basic
Compile error inhidden module. WinFaxPrintAnd Merge

How can I get rid of this.

Win98
MS Office 2000
 

A:Compile Error

Is this on every new doc or on every doc?

Try this, if it doesn't work post back but this is the easiest way that might work and won't hurt anything by trying.

Close word.

Go to start, find, type in

normal.dot

click find.

When it find it rename it normal.old

Open word and see what you have.
 

Read other 1 answers
RELEVANCY SCORE 52.4

I just moved to Vista from XP. In running Microsoft Developer Studio 97 (Visual C++ 5.0) I am now getting "no compile tool is associated with the file extension" message for any .h file. Help!
 

A:Compile error with C++

Hi ADurtschi,

Welcome to TSG!

Sounds like you need to specify the the INCLUDE pathname, i.e. folder or directory path to the header files (.h) that get included by expansion into the source code before it enters the compile step (of the compilation process). Look in the Makefile for the application you are trying to compile for where to specify the INCLUDE path, or if compiling on a command line in a Terminal window, read the compiler documentation on how to specify the INCLUDE path.

It is also possible that you need to reinstall the Microsoft Developer Studio 97 (Visual C++ 5.0) in Vista.

-- Tom
 

Read other 2 answers
RELEVANCY SCORE 52.4

Hi,
Im having a problem regarding the #include header files. Lets say I have typed a simple program to find if a number entered is odd or even. I have loaded Borland C in my windows m/c. When I press Alt + F9, i get an error that says
Unable to open include file STDIO.H
Could anyone help??
Thanks,
Kali.
 

A:Compile error

Read other 7 answers