maybe this'll work:


Option Explicit
Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim ans As Long
ans = MsgBox("Are you sure you want to convert the" & _
" current quote into an Invoice?", vbYesNo)
If ans = vbYes Then
Set wbk = Workbooks.Open _
(Filename:="C:\SyntheticShield\SyntheticShieldInvo ice.XLT")
wbk.Worksheets("Sales Invoice").Range("I47").Value _
= ThisWorkbook.Sheets("Customer Quote").Range("I47").Value
ThisWorkbook.Close savechanges:=False
End If
End Sub


"RPIJG <" wrote:

OK, so I shortened up the code quite a bit since it was a pain to look
at, I thought perhaps there might have been too much for Excel to
handle also, however, I don't see that as being the problem, since I'm
still getting the subscript out of range error

Code:
--------------------
Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim ans
ans = MsgBox("Are you sure you want to convert the current quote into an Invoice?", vbYesNo)
If ans = vbYes Then
Workbooks.Open Filename:="C:\SyntheticShield\SyntheticShieldInvoi ce.XLT"
With ActiveWorkbook
With .Worksheets("Sales Invoice")
Sheets("Sales Invoice").Range("I47").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("I47").Value
ActiveWindow.ActivatePrevious
ActiveWorkbook.Close False
End With
End With
End If
End Sub
--------------------

Is there perhaps a conflict between the code on this page, and the code
that runs on the opened page?

Code:
--------------------
Private Sub Workbook_open()
If ActiveWorkbook.Path = "" And Left(ActiveWorkbook.Name, 4) < "Book" Then
Dim ans3
ans3 = MsgBox("Do you really want to create a new invoice? Once created the Invoice number is final. ", vbYesNo)
If ans3 = vbYes Then
Dim InvNo&
'Do not assign new inv nbr if the original template has been opened
If UCase(Right(ThisWorkbook.Name, 3)) = "XLT" Then Exit Sub
'If O3 does not contain anything, enter the next inv nbr
If [O3] = "" Then
'Get next inv nbr
InvNo = GetSetting("XLInvoices", "Invoices", "CurrentNo", 1000) + 1
'Enter the inv nbr in A1
[O3] = InvNo
'Save the inv nbr
SaveSetting "XLInvoices", "Invoices", "CurrentNo", InvNo
'Put todays date in O4
[O4] = Date
End If
End If
End If
If ans3 = vbNo Then
Application.Quit
End If
If ActiveWorkbook.Path = "" And Left(ActiveWorkbook.Name, 4) < "Book" Then
Sheets("Sales Invoice").CommandButton1.Enabled = True
Else
With ActiveWorkbook.Sheets("Sales Invoice")
.CommandButton1.Enabled = False
End With
With ActiveWorkbook.Sheets("Sales Invoice")
.Unprotect ("YourPassword")
.Cells.Locked = True
End With
With ActiveWorkbook.Sheets("Customer Invoice")
.Unprotect ("YourPassword")
.Cells.Locked = True
End With
Dim wst As Worksheet
For Each wst In ThisWorkbook.Worksheets
wst.Protect
Next wst
End If
End Sub
--------------------

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson