Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
On Dec 8, 8:22*am, Jarek Kujawa wrote:
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
welcome
I also hate Mondays ;-) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
try:
Set Log = WrkBk.Sheets("Sheet1") be sure not to put any dot at the end of the line |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
On Dec 8, 9:18*am, Jarek Kujawa wrote:
try: Set Log = WrkBk.Sheets("Sheet1") be sure not to put any dot at the end of the line 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set Active Sheet as Variable
don't know
I'd activate sheet1 before it's range e.g. Log.Activate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet and make new sheet active | Excel Discussion (Misc queries) | |||
Set a variable to the active cell | Excel Discussion (Misc queries) | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions | |||
Using the Active cell in one sheet for another sheet | Excel Discussion (Misc queries) |