VERY Basic Define Variables Help Needed

[BEEJAY]

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.

 

[Bernie Deitrick]

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

 

[Vacation's Over]

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

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

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]

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 )

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

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

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.

 

[Bernie Deitrick]

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

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 )

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

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


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]

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

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

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 )

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

:

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


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.

 

[Bernie Deitrick]

BeeJay,

Try using

Application.EnableEvents = False

as your first line. The event code of QCNUM may be messing things up.

Then use
Application.EnableEvents = True
as the last line to turn it back on.

HTH,
Bernie
MS Excel MVP

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

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

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 )

:

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

:

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


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]

When walking thru the code (F8), it seems much smoother/quicker now.
It still jams up with/when QCNUM is opened. I've tried saving QCNUM
cursor in Cell F15 (to see if it moved). Sometimes it stays on F15, sometimes
it moves to F3.
The program does not jump out to another project anymore, at least, not for
the
few dozen times I've tried it with the EnableEvents statements.
For the record, the False statement is to be right below SUB....
The True statement is to be immediately above END SUB - Correct?

Any other thougths?

BeeJay,

Try using

Application.EnableEvents = False

as your first line. The event code of QCNUM may be messing things up.

Then use
Application.EnableEvents = True
as the last line to turn it back on.

HTH,
Bernie
MS Excel MVP

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

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


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 )

:

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

:

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


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.

 

[Bernie Deitrick]

BeeJay,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub SeqNum2()
' 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

Application.EnableEvents = False

'The Contract WB must be (will be) the active WB when initiating macro
Set myBook = ActiveWorkbook

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

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

myBook.Worksheets("Contract").Range("E5").Value = _
myQCNUM.Worksheets("Sheet1").Range("F6").Value

' 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

With myQCNUM.Worksheets("Sheet1")
..Range("F3").Value = .Range("F4").Value
End With

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

Application.EnableEvents = True

End Sub

When walking thru the code (F8), it seems much smoother/quicker now.
It still jams up with/when QCNUM is opened. I've tried saving QCNUM
cursor in Cell F15 (to see if it moved). Sometimes it stays on F15, sometimes
it moves to F3.
The program does not jump out to another project anymore, at least, not for
the
few dozen times I've tried it with the EnableEvents statements.
For the record, the False statement is to be right below SUB....
The True statement is to be immediately above END SUB - Correct?

Any other thougths?

BeeJay,

Try using

Application.EnableEvents = False

as your first line. The event code of QCNUM may be messing things up.

Then use
Application.EnableEvents = True
as the last line to turn it back on.

HTH,
Bernie
MS Excel MVP

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


:

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


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 )

:

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

:

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


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]

The modified sub has the same results.
Works great while stepping thru the VB module with F8
Real time use, the QCNUM opens up, the cursor usually sits on F4 or F3
(rather than F6), and everything stands still.

I'm totally at the mercy of your expertise.

BeeJay,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub SeqNum2()
' 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

Application.EnableEvents = False

'The Contract WB must be (will be) the active WB when initiating macro
Set myBook = ActiveWorkbook

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

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

myBook.Worksheets("Contract").Range("E5").Value = _
myQCNUM.Worksheets("Sheet1").Range("F6").Value

' 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

With myQCNUM.Worksheets("Sheet1")
..Range("F3").Value = .Range("F4").Value
End With

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

Application.EnableEvents = True

End Sub

When walking thru the code (F8), it seems much smoother/quicker now.
It still jams up with/when QCNUM is opened. I've tried saving QCNUM
cursor in Cell F15 (to see if it moved). Sometimes it stays on F15, sometimes
it moves to F3.
The program does not jump out to another project anymore, at least, not for
the
few dozen times I've tried it with the EnableEvents statements.
For the record, the False statement is to be right below SUB....
The True statement is to be immediately above END SUB - Correct?

Any other thougths?

BeeJay,

Try using

Application.EnableEvents = False

as your first line. The event code of QCNUM may be messing things up.

Then use
Application.EnableEvents = True
as the last line to turn it back on.

HTH,
Bernie
MS Excel MVP


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


:

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


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 )

:

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

:

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


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

 

[Bernie Deitrick]

BeeJay,

Send me a copy of QCNUM and your regular workbook, taking out any private information. Reply to
this message, then take the spaces out of my email and change the dot to .

I'll take a look when I can.

HTH,
Bernie
MS Excel MVP

 

[BEEJAY]

Thanks. Copys to follow.

BeeJay,

Send me a copy of QCNUM and your regular workbook, taking out any private information. Reply to
this message, then take the spaces out of my email and change the dot to .

I'll take a look when I can.

HTH,
Bernie
MS Excel MVP

 

[BEEJAY]

Bernie:
For the benefit of anyone else reading this post, I just want to say
that the sub you provided me with work great in everyway EXCEPT when
I tried to use a keyboard shortcut. This is obviously a problem with my
equipment
and not with the code. Therefore, if anyone is looking for something like
the code
presented, you are safe to use it.
Thanks Bernie
Your persistent helpfulness helped me keep the little bit of hair I have
left.