MACRO - DELETE SHEET'S MESSAGE

CJ

Board Regular
Joined
Feb 22, 2002
Messages
77
Hi
I have written/recorded a marco during the running of the macro I delete 2 sheets, when I run the macro I get the conformation message box before the sheets are deleted, I have to click OK.
Is there any code I can place in my macro that will delete the sheets but not display the conformation box.
Thanks
CJ
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, CJ,

Code:
Application.DisplayAlerts = False
Sheets(1).delete
Application.DisplayAlerts = True

be CAREFUL no warnings of any kind will popup, so don't put too much code between these commands ...
example
Thisworkbook.close ==> closing without changes !!

kind regards,
Erik
 

CJ

Board Regular
Joined
Feb 22, 2002
Messages
77
Hi Erik
Thanks for th espeedy reply, thats works a dream,
Can I be so bold as to ask another question without posting another topic.

The macro I have imports data from a text file but the text file always has a different name so I always have to name the sheet SIT before I run my macro, so is there any way I can name the sheet SIT at the start of the macro, independant of whatever the text file name is
I tried the below but once again it only renames if the original is called DATA JUN
Back to the drawing board

Sheets("DATA JUN").Select
Sheets("DATA JUN").Name = "SIT"

Thanks
CJ
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
please post a bit of code to make me grasp the problem
can you give a small explanation of your specific situation ?
and also a small example

you'll get further!
kind regards,
Erik

(probably going to sleep in a few minutes)
 

CJ

Board Regular
Joined
Feb 22, 2002
Messages
77
Hi
I import a text file into Excel every week run a macro to perform formatting & pivot table's & deleteing sheet's which I no longer need (which you have already helped)
The pivot table references a sheet name which I origanally called SIT, the sheet name defaults in as the text file name, so the first step I perform is to rename my sheet SIT before running my macro.
What I'm looking for is some code I can incorparate into my macro the will rename my sheet SIT iraspective of the original file name.

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'SIT'!R1C1:R5000C10", TableDestination:="", TableName:= _
"PivotTable1"

Hope this helps

CJ
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
CJ,

so you want instead of the "hardcoded" Sheets("DATA JUN").Name = "SIT" something like
Code:
ActiveSheet.Name = "SIT"

try to play a bit with coding to set hard-coding to variables
When you record a macro you get:
Code:
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "5"
cleanup to
Code:
    Range("A1") = 5

you can edit this to
Code:
ActiveCell = 5
or
Code:
i = 1
Range("A"& i) = 5
this involves new possibilities
Code:
For i = 1 to 1000 Step 10
Range("A"& i) = 5 'or Cells(i, 1) = 5
Next i
On the Main page of Excel Questions you'll find a link to recomended Add-Ins and links

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,975
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top