Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

Beejay,

It looks like you are using a "1" rather than a "l" character.

Here's what is should be:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Your original:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

Initially, if you didn't have optionexplicit set, VBA will probably have
interpreted an undeclared variable (x1values) as a zero.

Robin Hammond
www.enhanceddatasystems.com

"BEEJAY" wrote in message
...
Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was
happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping
me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each
saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

--

Dave Peterson

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

"Dave Peterson" wrote:

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

--

Dave Peterson

You could just use the two variables. Set them once, do the copy. Change them
and do the second copy.

I'm confused about what's going on in the rest of your post and what you're
really trying to do.

Maybe one more description attempt using the workbook names and worksheet names
as well as the addresses of the cells would be useful.

BEEJAY wrote:

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

"Dave Peterson" wrote:

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

--

Dave Peterson


--

Dave Peterson

I just finished my last week of holidays for the year, hence the delay in my
response to this post.

It has become clear to me that there is a potential large problem at the
start of my "procedure". Just so I don't further compound any confusion I
have caused so far, I'd like to see if we can clear this up first.

Lets say salesman has 3 work-books open.
Book A is his .................
Book B is his ...................
Book C is the Quote he is finishing for customer.

The Quote is completed.
Salesman selects INSERT QUOTE NUMBER from Special Menu
(Called"XMENU", which I have supplied him with)
(in order to allow the salesman to control when and where a quote
number
is required, I don't want to "automate" it at file open, or
file print)
The VB instructions in module 1-1 of "XMENU" opens a file called QCNUM.xls.
QCNUM.xls is the file where the Quote number is created, and copied from.
(I presume this is now the ActiveWorkBook.)

The Quote number is to be copied from QCNUM.xls (Sheet 1, Cell H6) and to be
pasted into Book C, Sheet 1 called "Contract", into Cell C5.

My Question: What (if any) instruction can I use that will make Book C
the active workbook again, in order to be able to paste the quote number into
Book C (and NOT into Book A or Book B. Since there are currently 12 different
quotation "masters", the original file name cannot be specified within the VB
instructions.

I first thought I could use "ActiveWorkBook", but since Book C is NOT the
calling workbook I expect that won't work.

For purposes of design and maintenance, I thought the best way to set up the
procedure would be to "attach" the instructions in ONE location (the
"XMENU"), rather than with each quotation master.

I hope I haven't muddied the waters further.


"Dave Peterson" wrote:

You could just use the two variables. Set them once, do the copy. Change them
and do the second copy.

I'm confused about what's going on in the rest of your post and what you're
really trying to do.

Maybe one more description attempt using the workbook names and worksheet names
as well as the addresses of the cells would be useful.

BEEJAY wrote:

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

"Dave Peterson" wrote:

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

--

Dave Peterson


--

Dave Peterson

If you open the workbooks in code, you could use:

dim WkbkA as workbook
dim wkbkB as workbook
dim wkbkC as workbook

set wkbkA = workbooks.open(filename:="C:\a.xls")
set wkbkb = workbooks.open(filename:="C:\b.xls")
set wkbkc = workbooks.open(filename:="C:\c.xls")

Then refer to the workbooks by the variable that refers to them.

WkbkA.worksheets(1).range("a1").value = "hi there"

===
If your workbook with the code is the one that's important, you can refer to it
via:

Thisworkbook.worksheets(1).range("a1").value = "ok"

But I think the waters are pretty muddy.

========
Another way might be to have your macro open the workbook that needs to be
modified. You can get the filename from the user and do all the work:

dim myFileName as variant
dim wkbk as workbook

myfilename = application.getopenfilename("Excel files, *.xls")
if myfilename = false then
'user hit cancel
exit sub
end if

set wkbk = workbooks.open(filename:=myfilename)
'do all the work against the opened workbook.
wkbk.worksheets(1).range("a1") = "done"
wkbk.save
wkbk.close savechanges:=false

=======
Just to add some more mud!



BEEJAY wrote:

I just finished my last week of holidays for the year, hence the delay in my
response to this post.

It has become clear to me that there is a potential large problem at the
start of my "procedure". Just so I don't further compound any confusion I
have caused so far, I'd like to see if we can clear this up first.

Lets say salesman has 3 work-books open.
Book A is his .................
Book B is his ...................
Book C is the Quote he is finishing for customer.

The Quote is completed.
Salesman selects INSERT QUOTE NUMBER from Special Menu
(Called"XMENU", which I have supplied him with)
(in order to allow the salesman to control when and where a quote
number
is required, I don't want to "automate" it at file open, or
file print)
The VB instructions in module 1-1 of "XMENU" opens a file called QCNUM.xls.
QCNUM.xls is the file where the Quote number is created, and copied from.
(I presume this is now the ActiveWorkBook.)

The Quote number is to be copied from QCNUM.xls (Sheet 1, Cell H6) and to be
pasted into Book C, Sheet 1 called "Contract", into Cell C5.

My Question: What (if any) instruction can I use that will make Book C
the active workbook again, in order to be able to paste the quote number into
Book C (and NOT into Book A or Book B. Since there are currently 12 different
quotation "masters", the original file name cannot be specified within the VB
instructions.

I first thought I could use "ActiveWorkBook", but since Book C is NOT the
calling workbook I expect that won't work.

For purposes of design and maintenance, I thought the best way to set up the
procedure would be to "attach" the instructions in ONE location (the
"XMENU"), rather than with each quotation master.

I hope I haven't muddied the waters further.

"Dave Peterson" wrote:

You could just use the two variables. Set them once, do the copy. Change them
and do the second copy.

I'm confused about what's going on in the rest of your post and what you're
really trying to do.

Maybe one more description attempt using the workbook names and worksheet names
as well as the addresses of the cells would be useful.

BEEJAY wrote:

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

"Dave Peterson" wrote:

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson