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

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

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

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

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

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