MACRO - DELETE SHEET'S MESSAGE

[CJ]

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

 

[erik.van.geit]

Hi, CJ,

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]

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]

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]

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]

CJ,

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

ActiveSheet.Name = "SIT"

try to play a bit with coding to set hard-coding to variables
When you record a macro you get:

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "5"

cleanup to

    Range("A1") = 5

you can edit this to

ActiveCell = 5

or

i = 1
Range("A"& i) = 5

this involves new possibilities

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