Hi, What I currently do is create a

[yumbelie]

Hi, What I currently do is create a new workbook, copy the sheet 'snapshot' to the new workbook, delete 'sheet1' in the new workbook (for the asthetics), save the work book and exit. Problem is, when the code removes sheet1 it prompts to confirm, I don't want it to do this - and I can't quite figure out what you need to put it so it doesn't issue a prompt.

Private Sub CommandButton3_Click()

Dim Response As String
Dim datestring As String
Dim filename As String
Dim path As String

Set MyWorkbook = ActiveWorkbook
Set MySheet = ActiveWorkbook.Worksheets("Patient Delay&quot
Dim SnapshotSheet As Object
Set SnapshotSheet = CreateObject("Excel.Sheet&quot

datestring = (Trim(Str(Day(Now())) + "-" + Str(Month(Now())) + "-" + Str(Year(Now()))))
path = "C:\"
filename = path + "Snapshot " + datestring + ".xls"
Response = MsgBox(filename)

MyWorkbook.Sheets("Snapshot&quot.Select
MyWorkbook.Sheets("Snapshot&quot.Copy After:=SnapshotSheet.Worksheets("Sheet1&quot
SnapshotSheet.Sheets("Sheet1&quot.Select
ActiveWindow.SelectedSheets.Delete

SnapshotSheet.SaveAs (filename)
MyWorkbook.Sheets("Patient Delay&quot.Activate

End Sub

Thats the code - Any help appericated guys

 

[Loomah]

Swith off display alerts at the beginning of your routine and switch back on at the end ie

Application.displayalerts = false/true

It's snowing in London (incase you don't realise, that NEVER happens!!)

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[plawrence]

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

Look up DisplayAlerts in the help to get the full context.

Another good one is Application.ScreenUpdating. Set it to false and you won't see the macro bouncing around the sheets. Be sure to set it back to true at the end of the macro or in your error handling.

Patrick

 

[RobBroekhuis]

You can also, instead of creating the new workbook and then copying, simply use:

MyWorkbook.Sheets("Snapshot&quot.Copy

This will create a new workbook with just one worksheet, which I think is just what you want.
Rob

 

[yumbelie]

I have just added an additional sheet copy request over, so it copies Snapshot1 and Snapshot2 to a new sheet, then supposed to delete sheet1 and vanish. Problem is now if you issue the 'delete sheet1' statement, it seems to delete all the sheets. Any ideas?

 

[RobBroekhuis]

What statement are you using to delete the sheet?
Rob

 

[yumbelie]

I'm using ActiveSheet.Delete after selecting the sheet I want removed. I'll paste the code in again:

MyWorkbook.Sheets("Snapshot - MDT&quot.Select
MyWorkbook.Sheets("Snapshot - MDT&quot.Copy After:=SnapshotSheet.Worksheets("Sheet1&quot
MyWorkbook.Activate
MyWorkbook.Sheets("Snapshot - DTC&quot.Select
MyWorkbook.Sheets("Snapshot - DTC&quot.Copy After:=SnapshotSheet.Worksheets("Sheet1&quot

'SnapshotSheet.Sheets("Sheet1&quot.Select Replace:=True
SnapshotSheet.Sheets("Sheet1&quot.Select
ActiveSheet.Delete

SnapshotSheet.SaveAs (filename)
MyWorkbook.Sheets("Patient Delay&quot.Activate

Application.DisplayAlerts = True

 

[RobBroekhuis]

try this to do the job:

dim CopySheets(1 to 2) as string
CopySheets(1)="Snapshot - MDT"
CopySheets(2)="Snapshot - DTC"
sheets(CopySheets).copy
activeworkbook.saveas filename
MyWorkbook.activate
Sheets("Patient Delay&quot.Activate
Rob

 

[Zathras]

Pardon me for butting in so late, but I can't help wondering... Why not just rename the sheet?