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

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

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

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

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

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

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