Over 1 million tech questions and answers.

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

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

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

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
Cells(3 + j, 1).Value = ""
Cells(3 + j, 2).Value = ""
End If

j = j + 1
Application.CutCopyMode = False
Application.ScreenUpdating = True

Cells(1, 1).Select
I tried running through the steps and it looks like the error occured in the first loop right before the ycell.formula line and after it went through about 55 workbooks already.
Anyway, I know it's not very efficient code, but I just need it to work for now. Any help would be very appreciated. Thanks!

Preferred Solution: Excel VBA Macro Problem - Code used to work, now 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: Excel VBA Macro Problem - Code used to work, now 400 error

Read other 7 answers

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

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

Option Explicit

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

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

Read other 1 answers

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?

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

A:Solved: Problem with an excel macro 400 error

Read other 16 answers

Sub GetData()
Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Raw Data").Range("DD").Value = "YesData" Then
Worksheets("DD").Range("A1:Y100").Value = Worksheets("Raw Data").Range("A1:Y34").Value
End If

In the above code I am trying to populate another sheet "DD" with specific information from "Raw Data". In the If statement I want the data populated to only retrieve data rows from "RawData" that contains the word "YesData" in the D column.

Please help.

Read other answers

I'm trying to code a macro with (among other things) a section using countif(). For now let's just assume that the range is fixed, say C3:C43. The criteria needs to reference 2 other cells. I want the countif to equal

(the # of values <= the # in cell(31)) - (the # of values <= the # in cell(30)), both in a different column.

I can select the range but I don't know excel's code for using countif() in a macro. Here's what I tried so far:

a = Selection.CountIf("<=m-<=p")

where m=# in cell(31) and p=# in cell(30)
I get an Object doesn't support this method error.

How can I get there? TIA

A:excel vb macro code for countif()

Read other 7 answers

I've actually written a line of code that is too long to fit in the macro window. How can I continue the code on another line in the programming window? I've tried using &'s but it doesn't work. Thanks.

A:Excel Macro - Code Continuation

Read other 6 answers

Hello to All!

Does anyone have a code/macro that will find a certain text in cells (*total) in a column, then based on the text found, search another column for the totals association with it, format the color & bold that total, and calc the sum in a 3rd cell?
Column 1 would have something like, abcTotals
ghiTotals .........etc.
Column 2 ...something like, $63.00 (blue & bold formatting when found) ........ etc.
Column 3 would hold the sum of all the*Totals


A:Solved: Excel 03 Macro/Code

Read other 8 answers

I have a macro in excel which auto fills a specified column with the formula in the first cell.

Assuming that Row 1 contains my headers, the code looks as follows:

Selection.AutoFill Destination:=Range(M2:M1845)

Is there a way instead of specifying the range to ask the macro to just carry on filling the formula until it reaches the end of the data entered, i.e. the last row of data?

I'm sure it was something like Range = xlDown, but every attempt seems to fail.

I am running Excel 2007 by the way.

A:Solved: Excel Macro Code

Try using

Selection.AutoFill Destination:=Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)


Read other 2 answers


I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.

A:Solved: Excel-macro code help

Read other 15 answers

I have the following bit of code as part of an Excel macro:

'In G1, enter formula to apply 6 character French code for AUT vehicles
ActiveWindow.ScrollColumn = 1
Application.Goto Reference:="R1C7"
ActiveCell.FormulaR1C1 = _

I'd like to be able to add more IFs to it, but there are already 7 conditions in there, which I believe is the max number of nested IFs allowed.
Specifically, at present, I want to add:


But I may need to add more in the future.

Can someone help me write this in a better way? I'm no VB genius...

A:Excel 2003 macro - rewrite a little bit of VB Code

Read other 16 answers

Hi all, hope someone can help.

I have a list of contracts, each run for 3 years exactly.
One starting 12/04/2007 ends: 11/04/2010.

I have the following code:


Sub Add3Years()
If IsDate(Range("B2")) Then
ActiveCell.FormulaR1C1 = DateAdd("yyyy", 3, Range("B2"))
ActiveCell.FormulaR1C1 = DateAdd("d", -1, ActiveCell.FormulaR1C1)
End If
End Sub

However, as you can see the initial range is as the specific cell B2. What I need is for the macro to see that I am on row 20, goto Column $B on row 20 and use that value instead of
Range("B2")Click to expand...


I've tried offsetting, but that doesn't really work, and I've seen
varRowNumber=Activecell.Row will return the row numberClick to expand...

If someone could help join the dots, that would be appreciated.

A:excel vba/macro: relative reference in code.

Read other 7 answers

Is there a code to where I can have a macro run every 5 seconds? If so, where would I place the code?

A:Solved: Excel Macro/Run Code Every 5 Seconds

here's how Ive done it. In this example C1will contain a single digit representing the # seconds so that I can control it from the sheet rather than editing the macro. If you just want to hard code the 5 seconds you can set Freq="00:00:05" or get rid of that line and edit the next one to

TimeToRun = Now + TimeValue("00:00:05")

There are 2 macros involved ....

the first sets the time period to run the routine you want and then "calls" that macro
the second is the routine you want to run every 5 seconds. The final line in this macro is to call the first macro to start the timer again

[B]Sub ScheduleCopyPriceOver[/B]()
Freq = "00:00:0" & Range("C1")
TimeToRun = Now + TimeValue(Freq)
Application.OnTime TimeToRun, "[B][COLOR=red]CopyPriceOver[/COLOR][/B]"
[B]End Sub[/B]
[B]Sub CopyPriceOver[/B]()
'Do what you need to do
Call [B][COLOR=red]ScheduleCopyPriceOver[/COLOR][/B]
[B]End Sub[/B]
Finally, if you want to start the timer when the the file is opened you can add another macro like this
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
All of these macros can be in a normal module.

Read other 2 answers

Can someone tell me what I need to do to have a macro in excel, open another file?
For example, I need the following file opened:
How do I do this, and also, at the end of the macro, I want that file closed. Please help!

A:VBA Code to open file in Excel Macro

Give one of these lines of code a shot...




What are you going to do with the spreadsheet once opened?

Read other 2 answers

one more little problem.

I have this code (which I got from recording a macro) for placing a chart on a page:

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("FixedPointsBlank").Range("C920"), PlotBy _
ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"
ActiveChart.SeriesCollection(1).Name = "=""Frequency"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="FixedPointsBlank"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Frequency Plot for Fixed Measurements"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Upper End Value (cpm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frequency"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False

On the 2 lines that have the Range function I have replaced inside the parenthesis with: Cells(30, 8), Cells(lastUEV, 9)
which works just fine. I have to do this because from one run to another the range size will change.

The other line that needs to be changed the same way is:

ActiveChart.SeriesCollection(1).XValues = "=FixedPointsBlank!R9C3:R20C3"

However, when I replace the R9C3:R20C3 with Cells(30, 8), Cells(lastUEV, 9) I get an App... Read more

A:Solved: excel vb macro code for chart

Read other 10 answers

Hello Everyone
I'm a pro atlete (quite iliterate in Biz Apps) that based on previous post (from Zack Baresse and computerman29642) has unsuccessfully tried to set up a code to merge my next year training calendar (xls format) into outlook. I herewith included the code I'm using and the test xls file I'd love to merge. Any help would be really appreciated
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
' Row 1 = Startdate
' Row 2 = Finishdate
' Row 3 = Subject
' Row 4 = Location
' Row 5 = Body
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt

.StartDate = arrAppt(i, 1) + arrAppt(i, 2)
.Finishdate = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = arrAppt(i, 6)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.AllDayEvent = True
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub... Read more

Read other answers

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?

A:Solved: Excel: Copy/Paste Macro Code Help

Read other 9 answers

Hi there!

So I've found the code below here: http://www.google.com/support/forum/p/Calendar/thread?tid=5e4909ca64cffe90&hl=en - Full credit goes to user 'tpuiatti'

Sub Generate_ICS()
Dim rng1 As Range, X, i As Long, v As Long
Dim objFSO, objFile
Dim FilePath As String
FilePath = "G:\Service.ics"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(FilePath)
Set rng1 = Range([a5], Cells(Rows.Count, "H").End(xlUp))
X = rng1

objFile.write "BEGIN:VCALENDAR" & vbCrLf
For i = 1 To UBound(X, 1)
objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
Next i
objFile.write "END:VCALENDAR"
End Sub

This code takes the info from 2 columns in Excel: A which is the event title and B which is the event date. Then it export these informations to an .ics file which will add the event dates to the Oulook Calendar.

The macro and the .ics work perfectly, however i wan't to make a few changes/additions to fit my needs. Additionally I wan't this macro to:

- Pick up dates from column &... Read more

A:Additional code to Excel macro - export to Outlook

Read other 7 answers

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim

A:Solved: Need Excel Macro Code to Autofill Column

Read other 12 answers

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!

A:Excel 2003 macro code converting comma delimiters

Read other 9 answers

Can you write a macro that will copy something from excel and paste it into word?

If so how do you do it? Do you write the macro in Excel or Word?

Many thanks

A:Macro to work in Word and Excel?

Yes, this can be done. Describe in detail exactly what you are trying to do and I'll write you a sample macro. Where should the values appear in the Word document? Which cell values will you be using? If you could .ZIP and attach some sample files that would make things much easier.

The basic steps are:

1) Set reference to Word library in your VBA Editor in Excel
2) Create a new instance of Word Application within your Excel project.
3) Execute your code on the instance of Word application.
Dim wdApp As Word.Application

Set wdApp = New Word.Application

wdApp.Visible = True

wdApp.Documents.Open ("C:\Documents and Settings\DocumentName.doc")



Read other 2 answers

Hello, I would like to make a macro in excel that I can use for any spreadsheet. We constantly get reports and I would like to make a macro that performs the necessary tasks for each one. However, whenever I make a macro, it seems like it can only be used for that certain report. Is there a way I can make one that I can just use for any spreadsheet? Thanks in advance.

A:Solved: Is is possible to make an excel macro to work with all sheets?

Read other 16 answers

Hi All,

on this occasion Google has not provided me with the answers i am looking for.

I have got an excel macro that i have sent out to a number of people, for those who use excel it is fine but i am needing it to work in open office or similar open source free program for the few that dont have access to MS Office.

I know that the coding is different and after the research i have done it seems that there are extentions or converters that make the VBA code work in Open office.

Does anybody know how i can get this to work? I can not find the extentions but am told they exist.

Any help would be really great


A:Excel Macro needs to work in Open office ir Similar

Google is a wonderfull search tool, have you tried it?
I found the link below that opens a pdf file with the nice sounding name: "Porting Excel/VBA to Calc/StarBasic"

I think this is what you need.

...but there is more


Happy coding

BTW this is what the Open Office site claims:

VBA Support

OpenOffice.org can run many VBA macros unmodified due to its built-in; limited VBA support.
Click to expand...

Read other 1 answers

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
Selection.QueryTable.Refresh BackgroundQuery:=False
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

With code in the Form_Error event, I am able to capture and control the error if the user uses the navigation buttons. Also if the user clicks a command button where I have the VB code of docmd.quit, I am able to display my message followed by an access message, followed by the message of "can't save record do you want to leave database anyway" which is an acceptable way for me to handle the error - the user can click no, and the form with the bad data is still in the active window state. Now if on the click event to go to another form, I run a macro with warnings set to no, echo off, close existing form and open new form, I will get my message, the access message, and then if the user clicks the "no" on the exit database question, the "Action Failed" box from the macro pops up (and from what I have read, there is no way to prevent that) - and the user must click the "halt" button to return to the form with the bad data. However, if I try to put the code from the Macro into Visual Basic - docmd.close followed by docmd.openform, no error messages appear, the form just closes and the other form opens.

I would like to avoid getting the Macro Action Failed/Halt Message Box, be able to control the error from visual basic so that the user is returned to the form with the bad data and has the option to either correct the data or escape out of adding the new record. Can't figure out why it works on the docmd.quit but not on the docmd... Read more

Read other answers

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

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

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

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

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

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

Read other 7 answers

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

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
'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
Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("CodingTemplate")
... Read more

Read other 1 answers

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

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

=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. ​


Read other answers

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?


Read other answers

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.


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

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

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


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

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


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


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 _

A:Excel Macro Problem

Read other 6 answers

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


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


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:

This gives an error in my next program.

Some1 can maby help with this?


A:Excel macro problem

Read other 7 answers


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,

A:Problem with a Macro in Excel

Read other 15 answers

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
Sheets("Copy A")... Read more

A:EXCEL macro problem..!

Read other 8 answers

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

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

Sheets(1).Name = "AIRLog"
Columns("P:Q").Delete Shift:=xlToLeft
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
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", _
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

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

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

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

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