What am I doing wrong? Sending values from one worksheet to another in new workbook

[RPIJG]

Well, I have this and it works on one template, but for some reason whe
I tried to adapt it to another I ran into a snag... it works so far a
to open up the other workbook as it is supposed to, but then it give
me a subscript out of range error? I'm confused, I'm trying to sen
from the "Customer Quote" worksheet in one workbook, to the "Sale
Invoice" worksheet of another workbook...The cells are in the sam
location on one worksheet as they are on the other, did I do somethin
wrong?


Code
-------------------
Private Sub CommandButton1_Click()
Dim iLastRow As Long
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\SyntheticShieldInvoice.XLT"
With ActiveWorkbook
Sheets("Sales Invoice").Range("D13").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("D13").Value
Sheets("Sales Invoice").Range("D14").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("D14").Value
Sheets("Sales Invoice").Range("D15").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("D15").Value
Sheets("Sales Invoice").Range("D16").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("D16").Value
Sheets("Sales Invoice").Range("G15").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("G15").Value
Sheets("Sales Invoice").Range("G16").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("G16").Value
Sheets("Sales Invoice").Range("L13").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("L13").Value
Sheets("Sales Invoice").Range("L14").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("L14").Value
Sheets("Sales Invoice").Range("L15").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("L15").Value
Sheets("Sales Invoice").Range("L16").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("L16").Value
Sheets("Sales Invoice").Range("O15").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("O15").Value
Sheets("Sales Invoice").Range("N16").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("N16").Value
Sheets("Sales Invoice").Range("C19:C35").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("C19:C35").Value
Sheets("Sales Invoice").Range("D1935").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("D1935").Value
Sheets("Sales Invoice").Range("E38").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E38").Value
Sheets("Sales Invoice").Range("E40").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E40").Value
Sheets("Sales Invoice").Range("E42").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E42").Value
Sheets("Sales Invoice").Range("E44").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E44").Value
Sheets("Sales Invoice").Range("E47").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E47").Value
Sheets("Sales Invoice").Range("E49").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E49").Value
Sheets("Sales Invoice").Range("E51").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E51").Value
Sheets("Sales Invoice").Range("E53").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("E53").Value
Sheets("Sales Invoice").Range("I47").Value = ThisWorkbook.Sheets("Customer Quote"). _
Range("I47").Value
ActiveWindow.ActivatePrevious
ActiveWorkbook.Close False
End With
End If
End Sub

 

[Frank Kabel]

Hi
not tested but maybe in the lines
With ActiveWorkbook
Sheets("Sales Invoice").Range("D13").Value =
ThisWorkbook.Sheets("Customer Quote"). _
Range("D13").Value
.....

you missed the dot in front of 'Sheets'
Private Sub CommandButton1_Click()
Dim wbk as workbook
Dim iLastRow As Long
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\SyntheticShieldInvoice.XLT"
set wbk = ActiveWorkbook
with wbk
.Sheets("Sales Invoice").Range("D13").Value =
ThisWorkbook.Sheets("Customer Quote"). _
Range("D13").Value
'...


--
Regards
Frank Kabel
Frankfurt, Germany

Well, I have this and it works on one template, but for some reason when
I tried to adapt it to another I ran into a snag... it works so far as
to open up the other workbook as it is supposed to, but then it gives
me a subscript out of range error? I'm confused, I'm trying to send
from the "Customer Quote" worksheet in one workbook, to the "Sales
Invoice" worksheet of another workbook...The cells are in the same
location on one worksheet as they are on the other, did I do something
wrong?


Code:
--------------------
Private Sub CommandButton1_Click()
Dim iLastRow As Long
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\SyntheticShieldInvoice.XLT"
With ActiveWorkbook
Sheets("Sales Invoice").Range("D13").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("D13").Value
Sheets("Sales Invoice").Range("D14").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("D14").Value
Sheets("Sales Invoice").Range("D15").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("D15").Value
Sheets("Sales Invoice").Range("D16").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("D16").Value
Sheets("Sales Invoice").Range("G15").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("G15").Value
Sheets("Sales Invoice").Range("G16").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("G16").Value
Sheets("Sales Invoice").Range("L13").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("L13").Value
Sheets("Sales Invoice").Range("L14").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("L14").Value
Sheets("Sales Invoice").Range("L15").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("L15").Value
Sheets("Sales Invoice").Range("L16").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("L16").Value
Sheets("Sales Invoice").Range("O15").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("O15").Value
Sheets("Sales Invoice").Range("N16").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("N16").Value
Sheets("Sales Invoice").Range("C19:C35").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("C19:C35").Value
Sheets("Sales Invoice").Range("D1935").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("D1935").Value
Sheets("Sales Invoice").Range("E38").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E38").Value
Sheets("Sales Invoice").Range("E40").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E40").Value
Sheets("Sales Invoice").Range("E42").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E42").Value
Sheets("Sales Invoice").Range("E44").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E44").Value
Sheets("Sales Invoice").Range("E47").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E47").Value
Sheets("Sales Invoice").Range("E49").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E49").Value
Sheets("Sales Invoice").Range("E51").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E51").Value
Sheets("Sales Invoice").Range("E53").Value =

ThisWorkbook.Sheets("Customer Quote"). _

Range("E53").Value
Sheets("Sales Invoice").Range("I47").Value =

ThisWorkbook.Sheets("Customer Quote"). _

 

[RPIJG]

That didn't fix it either, I'm still getting a subscript out of rang
error at the same place

 

[RPIJG]

OK, so I shortened up the code quite a bit since it was a pain to loo
at, I thought perhaps there might have been too much for Excel t
handle also, however, I don't see that as being the problem, since I'
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\SyntheticShieldInvoice.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 Su
-------------------


Is there perhaps a conflict between the code on this page, and the cod
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 Su

 

[Dave Peterson]

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\SyntheticShieldInvoice.XLT")
wbk.Worksheets("Sales Invoice").Range("I47").Value _
= ThisWorkbook.Sheets("Customer Quote").Range("I47").Value
ThisWorkbook.Close savechanges:=False
End If
End Sub

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\SyntheticShieldInvoice.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