Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Hi,
I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. I keep getting an error stating an object is required. Is it because I am trying to use a string? Is there a way to do this? wbkOld.Activate If OldShName = "" Then Set wsOld = ActiveSheet Else Set wsOld = Sheets(OldShName.Name) End If -- Thanks for your help. Karen53 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Hi
I think "wbkOld.Activate" is the problem. Do you have assigned any object to this variable, eg. set wbkOld = Workbooks("Book1") Regards, Per On 12 Apr., 02:28, Karen53 wrote: Hi, I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. *OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. *I keep getting an error stating an object is required. *Is it because I am trying to use a string? *Is there a way to do this? * * * * wbkOld.Activate * * * * If OldShName = "" Then * * * * * * Set wsOld = ActiveSheet * * * * Else * * * * * * Set wsOld = Sheets(OldShName.Name) * * * * End If -- Thanks for your help. Karen53 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Here is some code you can play around with to see how these properties
work... Open up two workbooks and put this Sub into an empty module. Then use your F8 button to step through the code and watch how it all works out... Mark Ivey Sub test() Dim wbkOld As Workbook Dim OldShName As String Set wbkOld = Application.ActiveWorkbook OldShName = ActiveSheet.Name MsgBox wbkOld.Name ' use this to test it out MsgBox OldShName ' use this to test it out ActiveWindow.ActivateNext MsgBox ActiveWorkbook.Name ' use this to test it out Workbooks(wbkOld.Name).Activate MsgBox ActiveWorkbook.Name ' use this to test it out End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
OldShName is really the Code name for a worksheet in in the Old workbook?
dim wsOld as worksheet dim wks as worksheet set wsold = nothing set wks = nothing for each wks in wbkOld.worksheets if lcase(wks.codename) = lcase(oldshname) then 'found it set wsold = wks exit for 'stop looking end if next wks if wsold is nothing then 'not found! else msgbox wsold.name & vblf & wsold.codename end if Karen53 wrote: Hi, I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. I keep getting an error stating an object is required. Is it because I am trying to use a string? Is there a way to do this? wbkOld.Activate If OldShName = "" Then Set wsOld = ActiveSheet Else Set wsOld = Sheets(OldShName.Name) End If -- Thanks for your help. Karen53 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Hi,
Thank you all for your replies. wbkOld are global variable which has been set in another procedure. OldShName is a variable which would contain one of three possible sheet code names. For example, OldShName may = shtAnnuity which IS the sheet's code name. I suspect the problem is that I'm using a variable for the different code names. Can I use it that way? -- Thanks for your help. Karen53 "Dave Peterson" wrote: OldShName is really the Code name for a worksheet in in the Old workbook? dim wsOld as worksheet dim wks as worksheet set wsold = nothing set wks = nothing for each wks in wbkOld.worksheets if lcase(wks.codename) = lcase(oldshname) then 'found it set wsold = wks exit for 'stop looking end if next wks if wsold is nothing then 'not found! else msgbox wsold.name & vblf & wsold.codename end if Karen53 wrote: Hi, I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. I keep getting an error stating an object is required. Is it because I am trying to use a string? Is there a way to do this? wbkOld.Activate If OldShName = "" Then Set wsOld = ActiveSheet Else Set wsOld = Sheets(OldShName.Name) End If -- Thanks for your help. Karen53 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Yep.
Did you notice this line: if lcase(wks.codename) = lcase(oldshname) then It's looking at the .codename--not the .name. Does this mean you're still having trouble???? Karen53 wrote: Hi, Thank you all for your replies. wbkOld are global variable which has been set in another procedure. OldShName is a variable which would contain one of three possible sheet code names. For example, OldShName may = shtAnnuity which IS the sheet's code name. I suspect the problem is that I'm using a variable for the different code names. Can I use it that way? -- Thanks for your help. Karen53 "Dave Peterson" wrote: OldShName is really the Code name for a worksheet in in the Old workbook? dim wsOld as worksheet dim wks as worksheet set wsold = nothing set wks = nothing for each wks in wbkOld.worksheets if lcase(wks.codename) = lcase(oldshname) then 'found it set wsold = wks exit for 'stop looking end if next wks if wsold is nothing then 'not found! else msgbox wsold.name & vblf & wsold.codename end if Karen53 wrote: Hi, I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. I keep getting an error stating an object is required. Is it because I am trying to use a string? Is there a way to do this? wbkOld.Activate If OldShName = "" Then Set wsOld = ActiveSheet Else Set wsOld = Sheets(OldShName.Name) End If -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set ws
Thank you, Dave.
-- Thanks for your help. Karen53 "Dave Peterson" wrote: Yep. Did you notice this line: if lcase(wks.codename) = lcase(oldshname) then It's looking at the .codename--not the .name. Does this mean you're still having trouble???? Karen53 wrote: Hi, Thank you all for your replies. wbkOld are global variable which has been set in another procedure. OldShName is a variable which would contain one of three possible sheet code names. For example, OldShName may = shtAnnuity which IS the sheet's code name. I suspect the problem is that I'm using a variable for the different code names. Can I use it that way? -- Thanks for your help. Karen53 "Dave Peterson" wrote: OldShName is really the Code name for a worksheet in in the Old workbook? dim wsOld as worksheet dim wks as worksheet set wsold = nothing set wks = nothing for each wks in wbkOld.worksheets if lcase(wks.codename) = lcase(oldshname) then 'found it set wsold = wks exit for 'stop looking end if next wks if wsold is nothing then 'not found! else msgbox wsold.name & vblf & wsold.codename end if Karen53 wrote: Hi, I have two workbooks open and I am trying to set wsOld to a sheet in the old workbook. OldShName is a string variable containing the sheet code name of the sheet I'm trying to set. I keep getting an error stating an object is required. Is it because I am trying to use a string? Is there a way to do this? wbkOld.Activate If OldShName = "" Then Set wsOld = ActiveSheet Else Set wsOld = Sheets(OldShName.Name) End If -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|