find existing worsheet

[monika]

hi...

I am writing a code using VBA....`Everytime i click the button its first
copies a worksheet from a workbook "X" to workbook "Y". Based on this
worksheet i make another 2 worksheets in workbook "Y". I want that when i
copy my worksheet...it should check whether the worksheet with a name "RAW"
already exits or not ... if exists then it should replace it. right now it
makes a copy by the name raw (2)???
here is my code for r that part:

BookPath = "G:\11 RFC\+06 FY 03_04\++04 Jan04\TCR\"
BookName = "abc_Jan04.xls"
OpenWorkBook

Sheets("Raw_Data").Select
Sheets("Raw_Data").Copy Before:=Workbooks("best.xls").Sheets(1)
filename = Dir("G:\11 RFC\+06 FY 03_04\++04 Jan04\TCR\abc_Jan04.xls")
....
..


Sub OpenWorkBook()
On Error Resume Next
Err.Clear
Windows(BookName).Activate
If Err.Number <> 0 Then
Err.Clear
Workbooks.Open filename:=BookPath & BookName
If Err.Number <> 0 Then
Err.Clear
MsgBox ("Unable to locate " & BookName)
End If
End If

pls suggest what change i need to do?

thanks
monika

 

[Bob Phillips]

Monika,

Add code like this to check for the worksheet

On Error Resume Next
Set oSh = Workbooks("best.xls").Worksheets("Raw_Data")
On error goto 0
If Osh Is Nothing Then
'the worksheet doesn't already exist
Else
'the worksheet does already exist
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[monika]

thanks for the response Bob...

but Also I wanted to know as to how can replace the existing one. I will
check if its existing or not. IF its existing then i wan tto replace it.

thanks
monika

 

[Bob Phillips]

Monika,

Give a mouse a cookie, and he wants a piece of cheese<vbg>

Try this

Dim oSh As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
'With Workbooks("best.xls").Worksheets
With ActiveWorkbook.Worksheets
Set oSh = .Item("Raw_Data")
On Error GoTo 0
If Not oSh Is Nothing Then
.Item("Raw_Data").Delete
End If
.Add
.Item(.Count).Name = "Raw_Data"
End With
Application.DisplayAlerts = False

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[monika]

hi bob..

the code u mentioned is not so clear...
what i can comprehend is if a sheet RAW_DATA is found then it deletes by
.Item("Raw_Data").Delete
else it makes a copy...
.Add
.Item(.Count).Name = "Raw_Data"

but nowhere do i specify the source and destination file....i slightly
modified the code as per my understanding:
Dim oSh As Worksheet



Application.DisplayAlerts = False
On Error Resume Next
Windows("Assembly_RFC0401_Mon.xls").Activate
With ActiveWorkbook.Worksheets
Set oSh = .Item("CM")
On Error GoTo 0
Windows("best.xls").Activate
If Not oSh Is Nothing Then
MsgBox ("worksheet found")
.Item("CM").Delete
Else
MsgBox ("not found")
End If
.Add
.Item(.Count).Name = "CM"
End With

Application.DisplayAlerts = False

but this code deletes the sheet CM , from the original file
Assembly_RFC0401_Mon
would really appreciate if u can provide some solution

thanks

 

[Bob Phillips]

Monika,

The problem with this code is that you do a ' With
ActiveWorkbook.Worksheets' after activating
Windows("Assembly_RFC0401_Mon.xls"), so all the dot operations after that
will refer to this workbook, even though you activate 'Windows("best.xls")'
afteerwards, including the .Item.Delete.

I am thus confused as to why you Activate one workbook, then another. Which
workbook should we look CM up in, and which should we delete from?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[monika]

hi Bob

sorry to be not so clear...

I want Sheet CM to be copied from workbook Assembly_RFC0401_Mon.xls to
best.xls. Which I am able to do so... but i want to add a verification that
if worksheet CM is already existing in best.xls then that worksheet CM
should be replaced by the recent one in workbook Assembly_RFC0401_Mon.xls.

what's happening is that everytime i click the button....it copies from
Assembly_RFC0401_Mon.xls to best.xls; and when it finds CM worksheet already
present then it makes another copy as CM2...whereas i don't want that..i
want worksheet to be replaced///

i hope i am clear
thanks a lot
Monika

 

[monika]

I GUESS I Found a way to resolve it... it now seems quite simple..

for the interest of others... i will check if that worksheet is alreay
existing .. if yes then i will delete it. ther is nothign like replacing a
worksheet.

thanks
monik

 

[Shailesh Shah]

Hi Monica,

Try this,

With Workbooks("Assembly_RFC0401_Mon.xls").Worksheets
.Parent.Activate
Set osh = .Item("CM")
End With
With Workbooks("best.xls").Worksheets
.Parent.Activate
On Error Resume Next
If .Item(osh.Name) Is Nothing Then
If Err.Number <> 0 Then
MsgBox "This will copy Worksheet to another workbook."
osh.Copy Before:=.Item(1)
End If
Else
On Error GoTo 0
MsgBox "This will replace your existing worksheet with newer
version."
Application.DisplayAlerts = False
.Item("sk").Delete
Application.DisplayAlerts = True
osh.Copy Before:=.Item(1)
End If
End With


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Shailesh Shah]

One correction :

Change

.Item("sk").Delete
to
.Item("CM").Delete



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[monika]

hi Shailesh...

thanks for the wonderful solution... thanks a lot..its working superbly

thanks again...
Monika