PDA

View Full Version : [SOLVED] if sheet 9 exists, clear it otherwise create one



asdzxc
08-29-2014, 05:54 AM
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
08-29-2014, 07:52 AM
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
08-29-2014, 07:50 PM
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
08-29-2014, 09:14 PM
Change the typo in the line
On Error GFoto 0 and run the code again

asdzxc
08-29-2014, 09:52 PM
http://www.vbaexpress.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAANYAAAA5CAIAAADFk0IuAAAEjElEQVR4nO1abbLj IAzjTnvBXmNPy/54b7IU27IMIaSpNZ1OaowtGwX6VWoisRVlN4HEtyMlmNiMlGBiM1KCic1ICSY24yMlWErprlvLW fFllsQKmJ0tDdB8wudckPpzKZV3uDH5GsnWJX6g9yi6AXyiBNVQICZZY 6dUQwu3vyUMYR0wGyEocihgAkSMQla50u3lu2OcqhBHk/dqJpI2qmqNAnyWXDYMQe1dsso/R MgATBngFeqgecVKH0ZBJZ4CUYijwsQdAQxvhs7JegNUulwUuQ2UJuKMEqWvR43FSCvCXEc9iT9E kJDuCEluGXAxKM6pIhX6cPYpUJpjdc5vfor1oSrMZZJo3lHZb9GD2e5azWR81 uKF63nOp8TFzEFnNhTkAI8jO8HkMPrLU61doRUYr5lfpr36oBB8Acqf8BnxXtYkbIiWY2AzwceR VymskYnl1jwl6s9mvTG2hJXMrYjeB9Y741V3Egjazrm/33uwqWtnNt/RhUL9TeIGXVNCt/UoJfhZYCXZHiRNUa7cawQorDyyegHUQY6PLjY9pNaR9Vqer5/UN31qci8AuyN/KeMFUZ4uAqkIMudJWzKixi8xvt/x0q8OPFN8PYhK0fKwITB9VUbrX0ezWXkLeAJaGyP0pNN26Uf3KPxNrJcjYL5MgM8W6drcxnpI7P SVYK7GKkytqOc8kxVPucBBXW4J5EGsD8PMBOHq680U9d1RnNYjraWWXPAeydw7HM4jJUMJsZRZZ y5NgSjCRuAYpwcRmpAQTm5ES/DAU8V/rW/25S1Jy6TkfR5a BS6lnN6 I2Zp/ji9gtIK8kzS7lpyKA3qSp5tFpcSimNED3wVMqPRFd3pJLiO0jz5aAR3vdXtZwVPkOh8CRL8UoKD iO7QYz6XSTBK7HfInqN/jWx91ccIsTsjaiMU50YRwM5Vk6B6cEgCrUWd7pJXScq5VjnADqoGbit4Wtc8q/9DcNqL aGC/tFC37q7FZ2HKkHQPmul3Y6r5C1PHIoZ5fuj3jyLeMoGWpTMIWKys fxBzG s0 UoLzvo81V7Qx5ddtg9hKQCDgDuGo7i elEsQOJHg1tENWd0AKJpElNSuFGyekHlwy74wjLOVJEgtLkP/9vtISdBsRbbGba1iC5JRJI87ujqq4nuek/iqQIPnvgdbu8rC2/fYlDkJCPSDU8wVQUu0MeZnIMlZRtUwk68KFX8wT82Fon7bxJK7BWXfpZfBvm2t4JBIWFAnZuPMx 8JjJRgSnAzTAm215Md37jqA5TmybvZEy10CdamfWNroF5fpj9MA1NqOzBAXiZKYOgfR6yeDizDL v1NUhouoaYEg2AleNilA1iG r4eMoIVVjIhCVhDKiU1kRXH8kwJTiKwC7ZDFa4ECCUngoyWiEkFuJSsa/zSrT0lGEJMgtFlsEJho5UIJ5Wp21rcKjDVgdpTgiTWSpCxS6OVCCcdmAscwEu 9poSJBD4RGxdn6iDeQky5FOCtwL7vSAe7dYABLTCVrHkpCfgAKLhId6IO2A1JNEifx1Z/khgpARTgpvxD8V7xpQTzA61AAAAAElFTkSuQmCC
Syntax error

Aussiebear
08-30-2014, 01:29 AM
Yes. Have a good look at the syntax. There is a simple typo error there.

asdzxc
08-30-2014, 09:39 PM
sloved

Aussiebear
08-31-2014, 01:56 AM
For your reference, kindly go to the Thread Tools drop down and mark the thread as solved from there.

snb
08-31-2014, 04:58 AM
Sub M_snb()
On Error Resume Next
Worksheets("Sheet9").UsedRange.ClearContents
if err.number <>0 then sheets.add.name="Sheet9"
End Sub

jolivanes
08-31-2014, 07:20 AM
Crosspost.


http://www.excelforum.com/excel-programming-vba-macros/1034322-if-sheet-9-exists-clear-it-otherwise-create-one.html

Aussiebear
08-31-2014, 03:02 PM
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
08-31-2014, 03:18 PM
excelforum notified

Bob Phillips
09-01-2014, 12:09 AM
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
09-01-2014, 02:44 AM
Seems the OP is getting the same type of answer there Bob.

jolivanes
09-01-2014, 07:56 AM
Chrome is blocking me also when I try to access excelforum. It says malware.
Anyone knows what the problem is?

Bob Phillips
09-02-2014, 06:13 AM
It appears to be fixed now.