Compile Error in Excel VBA macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Amapola

Thread Starter
Joined
May 4, 2010
Messages
26
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
 

Amapola

Thread Starter
Joined
May 4, 2010
Messages
26
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top