if sheet 9 exists, clear it otherwise create one

[asdzxc]

Columns("A:B").Select
 Selection.Copy
if sheet9 exists  then  sheet9.clear
else Sheets.Add After:=Worksheets(ActiveSheet.Name)
  ActiveSheet.Name = "sheet9"
  end if
   ActiveSheet.Paste

plse correct the above code

[Bob Phillips]

Columns("A:B").Copy 
On Error Resume Next
Set sh = Worksheets("Sheet9")
On Error GFoto 0
If Not sh Is Nothing Then
    sh.UsedRange.ClearContents
Else 
    Set sh = Sheets.Add(After:=ActiveSheet.Name) 
    sh.Name = "Sheet9" 
End If 
ActiveSheet.Paste

[asdzxc]

Columns("A:B").Copy 
On Error Resume Next
Set sh = Worksheets("Sheet9")
On Error GFoto 0
If Not sh Is Nothing Then
    sh.UsedRange.ClearContents
Else 
    Set sh = Sheets.Add(After:=ActiveSheet.Name) 
    sh.Name = "Sheet9" 
End If 
ActiveSheet.Paste

If Not sh Is Nothing Then

the above code is not working

[Aussiebear]

Change the typo in the line

On Error GFoto 0

and run the code again

[asdzxc]

Syntax error

[Aussiebear]

Yes. Have a good look at the syntax. There is a simple typo error there.

[asdzxc]

sloved

[Aussiebear]

For your reference, kindly go to the Thread Tools drop down and mark the thread as solved from there.

[snb]

Sub M_snb()
   On Error Resume Next 
   Worksheets("Sheet9").UsedRange.ClearContents 
   if err.number <>0 then sheets.add.name="Sheet9"
End Sub

[jolivanes]

Crosspost.


http://www.excelforum.com/excel-prog...reate-one.html

[Aussiebear]

Interesting.... The OP is obviously frustrated with our help and posted elsewhere. When you look at the information posted here it's hard to see how we can do more given that "the above code isn't working" could mean anything.

[SamT]

excelforum notified

[Bob Phillips]

Interestingly. Chrome is blocking access to ExcelForum for me. ESET has long told me that ExcelForum is suspect when I follow a Google search that links there, but I always went there anyway. But this is more dramatic.

[Aussiebear]

Seems the OP is getting the same type of answer there Bob.

[jolivanes]

Chrome is blocking me also when I try to access excelforum. It says malware.
Anyone knows what the problem is?

[Bob Phillips]

It appears to be fixed now.