The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as tough
as this is (to me)
Thanks in advance.

Bee,

Your first line of error-producing code looks like a worksheet function used
to return the current file name.

You should use VBA to get that:

Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path

Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.

Post back with what it is that you want to do rather than with just the code
that you tried to use.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.

What Bernie said plus:

FYI
Syntax for VBA is very different from Excel formulas

=G42 in excel becomes
Activeworkbook.Range("G42") or
Activeworkbook.Cells(42,7)

and so on...

"Bernie Deitrick" wrote:

Bee,

Your first line of error-producing code looks like a worksheet function used
to return the current file name.

You should use VBA to get that:

Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path

Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.

Post back with what it is that you want to do rather than with just the code
that you tried to use.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.

I'll try to explain.
Salesman has a number of WorkBooks open.
He's currently working on a CONTRACT.
When he's done, he will use a macro to open file QCNUM.xls,
which is where the sequencial quote number is stored and therefore will be
extracted from. The difficulty, I think, is in how to direct the copied
material back to CONTRACT, and not to one of the other open WB's.
I had hoped that by putting the file name in cell G42 of CONTRACT,
copying it to QCNUM, cell F8, that, after copying the data from QCNUM,
the copied material could be directed back to the correct WB by referencing
cell F8 in QCNUM.
Note that the actual name for CONTRACT will change everytime that WB gets
used. That's why the first part of the macro has to initiate the procedure to
put
the actual WB name into cell G42.
All my previous attempts at explaining the complete procedure that I'm trying
to acomplish has done little more than confuse everyone, including myself.
Actually, that's not quite true. I have learned a lot of interesting things
from everyones attempts to help me.
I hope this clarifies what I'm attempting. ( at least the first steps )

"Vacation's Over" wrote:

What Bernie said plus:

FYI
Syntax for VBA is very different from Excel formulas

=G42 in excel becomes
Activeworkbook.Range("G42") or
Activeworkbook.Cells(42,7)

and so on...

"Bernie Deitrick" wrote:

Bee,

Your first line of error-producing code looks like a worksheet function used
to return the current file name.

You should use VBA to get that:

Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path

Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.

Post back with what it is that you want to do rather than with just the code
that you tried to use.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.

BeeJay,

You don't need to store the workbook name anywhere. Simply use a workbook object reference:

Dim myBook As Workbook
Dim myQCNum As Workbook
Set myBook = ActiveWorkbook 'Will be CONTRACT if CONTRACT is the actve workbook
Set myQCNum = Workbooks.Open("C:\Folder\QCNUM.xls")

Then you can switch back and forth (though you don't really need to - you can do everything without
activating)

myBook.Activate 'This is CONTRACT again
myQCNum.Activate 'This is QCNum again

Not sure whether you are putting data into QCNUM or taking data out of it.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
I'll try to explain.
Salesman has a number of WorkBooks open.
He's currently working on a CONTRACT.
When he's done, he will use a macro to open file QCNUM.xls,
which is where the sequencial quote number is stored and therefore will be
extracted from. The difficulty, I think, is in how to direct the copied
material back to CONTRACT, and not to one of the other open WB's.
I had hoped that by putting the file name in cell G42 of CONTRACT,
copying it to QCNUM, cell F8, that, after copying the data from QCNUM,
the copied material could be directed back to the correct WB by referencing
cell F8 in QCNUM.
Note that the actual name for CONTRACT will change everytime that WB gets
used. That's why the first part of the macro has to initiate the procedure to
put
the actual WB name into cell G42.
All my previous attempts at explaining the complete procedure that I'm trying
to acomplish has done little more than confuse everyone, including myself.
Actually, that's not quite true. I have learned a lot of interesting things
from everyones attempts to help me.
I hope this clarifies what I'm attempting. ( at least the first steps )

"Vacation's Over" wrote:

What Bernie said plus:

FYI
Syntax for VBA is very different from Excel formulas

=G42 in excel becomes
Activeworkbook.Range("G42") or
Activeworkbook.Cells(42,7)

and so on...

"Bernie Deitrick" wrote:

Bee,

Your first line of error-producing code looks like a worksheet function used
to return the current file name.

You should use VBA to get that:

Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path

Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.

Post back with what it is that you want to do rather than with just the code
that you tried to use.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.

WOW!! With everyones help, especially yours, I feel like I'm SO close now.

1 I have walked thru the code numerous times using F8 with no problems.
2 When I try a real life run, something jams up. It seems to be in
different places, at times, although usually the QCNUM file is opened on the
screen and everything just seems to stop.
3. Some times the code jumps over to another project and hi-lites the
following code within that module.

Private Sub xlApp_SheetSelectionChange(ByVal sh As Object, ByVal Target As
Range)
UpdateTB sh
End Sub

The code I now have is as follows:
(Your critical eye is obviously required)

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q

Dim myBook As Workbook
Dim myQCNUM As Workbook
Set myBook = ActiveWorkbook

'The Contract WB must be (will be) the active WB when initiating macro

Set myQCNUM = Workbooks.Open("C:\Excel Add_Ins\QCNUM.xls")
' QCNUM is now Open (available)

Dim RngToCopy As Range
Dim DestCell As Range

' Go to QCNUM cell F6, Copy
' (this is a concatenated number of 3 different cells)
' Go to myBook, Sheet "Contract", Cell E5, Paste

With myQCNUM.Worksheets("Sheet1")
Set RngToCopy = .Range("F6")
End With

With myBook.Worksheets("Contract")
Set DestCell = .Range("E5")
End With

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

' The following increments the number in QCNUM.XLS for use on next Contract
' Copy Cell F4 of QCNUM.xls - Paste to Cell F3 of QCNUM.xls
' This copies the most recent used number (F4) into the "starting number"
' cell (F3), which in turn makes H4 increment by 1 for use next time

Dim Rng1ToCopy As Range
Dim Dest1Cell As Range

With myQCNUM.Worksheets("Sheet1")
Set Rng1ToCopy = .Range("F4")
Set Dest1Cell = .Range("F3")
End With

Rng1ToCopy.Copy
Dest1Cell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Save and Close QCNUM.xls
With myQCNUM
.Save
.Close SaveChanges:=True
End With

End Sub


"Bernie Deitrick" wrote:

BeeJay,

You don't need to store the workbook name anywhere. Simply use a workbook object reference:

Dim myBook As Workbook
Dim myQCNum As Workbook
Set myBook = ActiveWorkbook 'Will be CONTRACT if CONTRACT is the actve workbook
Set myQCNum = Workbooks.Open("C:\Folder\QCNUM.xls")

Then you can switch back and forth (though you don't really need to - you can do everything without
activating)

myBook.Activate 'This is CONTRACT again
myQCNum.Activate 'This is QCNum again

Not sure whether you are putting data into QCNUM or taking data out of it.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
I'll try to explain.
Salesman has a number of WorkBooks open.
He's currently working on a CONTRACT.
When he's done, he will use a macro to open file QCNUM.xls,
which is where the sequencial quote number is stored and therefore will be
extracted from. The difficulty, I think, is in how to direct the copied
material back to CONTRACT, and not to one of the other open WB's.
I had hoped that by putting the file name in cell G42 of CONTRACT,
copying it to QCNUM, cell F8, that, after copying the data from QCNUM,
the copied material could be directed back to the correct WB by referencing
cell F8 in QCNUM.
Note that the actual name for CONTRACT will change everytime that WB gets
used. That's why the first part of the macro has to initiate the procedure to
put
the actual WB name into cell G42.
All my previous attempts at explaining the complete procedure that I'm trying
to acomplish has done little more than confuse everyone, including myself.
Actually, that's not quite true. I have learned a lot of interesting things
from everyones attempts to help me.
I hope this clarifies what I'm attempting. ( at least the first steps )

"Vacation's Over" wrote:

What Bernie said plus:

FYI
Syntax for VBA is very different from Excel formulas

=G42 in excel becomes
Activeworkbook.Range("G42") or
Activeworkbook.Cells(42,7)

and so on...

"Bernie Deitrick" wrote:

Bee,

Your first line of error-producing code looks like a worksheet function used
to return the current file name.

You should use VBA to get that:

Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path

Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.

Post back with what it is that you want to do rather than with just the code
that you tried to use.

HTH,
Bernie
MS Excel MVP


"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.

Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).

Dim RngToCopy As Range
Dim DestCell As Range

'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined

Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)


' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)


' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With

End Sub

'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.