find existing worsheet
 

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 Befo=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

find existing worsheet
 

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" wrote in message
...
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 Befo=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

find existing worsheet
 

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" wrote in message
...
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" wrote in message
...
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 Befo=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

find existing worsheet
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 Befo=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

find existing worsheet
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 Befo=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

find existing worsheet
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 Befo=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

find existing worsheet
 

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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 Befo=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

FOUND A PROCEDURE
 

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

find existing worsheet
 

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 Befo=.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 Befo=.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!

find existing worsheet
 

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!

find existing worsheet
 

hi Shailesh...

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

thanks again...
Monika
"Shailesh Shah" wrote in message
...

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!