Set Active Sheet as Variable

J

jlclyde

Sub FirstMoveToQuoteSheet()
Dim QuoteSht As Worksheet
Dim Log As Worksheet
Dim Opn As String

Set QuoteSht = AvtiveSheet
Opn = "G:\New Items\Tracking Lists\" & QuoteSht.Range("R15") = _
Left(QuoteSht.Range("H2"), 2) & " Quotation Tracking Log.xls"
MsgBox QuoteSht
Workbooks.Open Filename:=Opn


End Sub
Here is the code. It bugs out on msgbox. The Quotesheet is set to
nothing. any help would be greatly appreciated.
Thanks,
Jay
 
J

Jarek Kujawa

1. should be:
Set QuoteSht = ActiveSheet
(instead of AvtiveSheet)

2. MsgBox QuoteSht
what do you need this message box for? you only can place a string in
MsgBox (sth. like QuoteSht.Name)
QuoteSht cannot be placed in a MsgBox since it is an object
 
J

jlclyde

1. should be:
Set QuoteSht = ActiveSheet
(instead of AvtiveSheet)

2. MsgBox QuoteSht
what do you need this message box for? you only can place a string in
MsgBox (sth. like QuoteSht.Name)
QuoteSht cannot be placed in a MsgBox since it is an object
Click to expand...

I am sorry that I wasted your time. I am definitly having a Monday.
I had not typed correctly. In the code I had ActiveSheet and not
Avtive. Plus the biggest problem was that I had a poor reference to
what I wanted to open. After I removed the Range(r15) = something
part, it works great.

Set QuoteSht = ActiveSheet

Opn = "G:\New Items\Tracking Lists\" & Left(QuoteSht.Range("H2"),
2) & _
" Quotation Tracking Log.xls"
Workbooks.Open Filename:=Opn
Sorry,
Jay
 
J

jlclyde

I have another problem. I hope it is not as stupid. VBA is not
letting me set Log = WrkBk.Sheet1. Why?

Opn = "G:\New Items\Tracking Lists\" & Left(QuoteSht.Range("H2"),
2) & _
" Quotation Tracking Log.xls"
Set WrkBk = Workbooks.Open(Opn)
Set Log = WrkBk.Sheet1

Thanks,
Jay
 
J

Jarek Kujawa

try:
Set Log = WrkBk.Sheets("Sheet1")

be sure not to put any dot at the end of the line
 
J

jlclyde

try:
Set Log = WrkBk.Sheets("Sheet1")

be sure not to put any dot at the end of the line
Click to expand...

The sheet is not called Sheet1 it is sheet1 of the workbook that I am
opening. Here is all the code so you can see what I am trying to do.

Thanks,
Jay

Sub FirstMoveToQuoteSheet()
Dim Log As Worksheet
Dim WrkBk As Workbook
Dim QuoteNum As String
Dim Comp As String
Dim Desc As String
Dim Dt As Date
Dim Cont As String
Dim Opn As String
Dim QuoteRow As Long

Comp = Range("K1")
Desc = Range("I3")
Dt = Range("H1").Value
Cont = Range("K2")
QuoteNum = Range("H2")
Opn = "G:\New Items\Tracking Lists\" & Left(Range("H2"), 2) & _
" Quotation Tracking Log.xls"
Set WrkBk = Workbooks.Open(Opn)
Set Log = WrkBk.Sheets("Sheet1")
QuoteRow = Log.Range("A:A").Find(What:=QuoteNum,
LookIn:=xlValues).Row
Log.Range("B" & QuoteRow) = Comp
Log.Range("C" & QuoteRow) = Desc
Log.Range("D" & QuoteRow) = Dt
Log.Range("K" & QuoteRow) = Cont
WrkBk.Close Savechanges:=True
End Sub
 
Top