Hi All

I add sheets through code, and when I have printed what I want I want to
delete all sheets except the two on the left in this case Sheet1(Sheet2) and
Sheet5(Sheet1) doing it with the recorder I got the following, but this will
not work after rebuilding the sheets.

Any ideas ??

Thanks Bart


Sheets("D3").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
"DVG", "ISA", "3", "2", _
"ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
"DVK")).Select
Sheets("D3").Activate
Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
"UAL")).Select Replace:= _
False
Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
"DVG", "ISA", "3", "2", _
"ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
"UAL")).Select
Sheets("UAL").Activate
Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
"HOME")).Select Replace:= _
False
ActiveWindow.SelectedSheets.Delete
Range("F6").Select

Hi

Look at this:

Sub DeleteSheeets()
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
sh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

Regards,
Per

"ian bartlett" <(E-Mail Removed)> skrev i meddelelsen
news:uSVwk.152886$nD.140587@pd7urf1no...
> Hi All
>
> I add sheets through code, and when I have printed what I want I want to
> delete all sheets except the two on the left in this case Sheet1(Sheet2)
> and Sheet5(Sheet1) doing it with the recorder I got the following, but
> this will not work after rebuilding the sheets.
>
> Any ideas ??
>
> Thanks Bart
>
>
> Sheets("D3").Select
> ActiveWindow.ScrollWorkbookTabs Position:=xlLast
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "DVK")).Select
> Sheets("D3").Activate
> Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
> "UAL")).Select Replace:= _
> False
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "UAL")).Select
> Sheets("UAL").Activate
> Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
> "HOME")).Select Replace:= _
> False
> ActiveWindow.SelectedSheets.Delete
> Range("F6").Select
>

This should delete sheets to the right of the active sheet.

Sub delshtsbyindex()
sc = Sheets.Count
'MsgBox sc
ms = ActiveSheet.Index
'MsgBox ms
For i = ms To sc
Sheets(i).Delete
'MsgBox Sheets(i).Name
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ian bartlett" <(E-Mail Removed)> wrote in message
news:uSVwk.152886$nD.140587@pd7urf1no...
> Hi All
>
> I add sheets through code, and when I have printed what I want I want to
> delete all sheets except the two on the left in this case Sheet1(Sheet2)
> and Sheet5(Sheet1) doing it with the recorder I got the following, but
> this will not work after rebuilding the sheets.
>
> Any ideas ??
>
> Thanks Bart
>
>
> Sheets("D3").Select
> ActiveWindow.ScrollWorkbookTabs Position:=xlLast
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "DVK")).Select
> Sheets("D3").Activate
> Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
> "UAL")).Select Replace:= _
> False
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "UAL")).Select
> Sheets("UAL").Activate
> Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
> "HOME")).Select Replace:= _
> False
> ActiveWindow.SelectedSheets.Delete
> Range("F6").Select
>

> For i = ms To sc
> Sheets(i).Delete
> Next i

Did you test that code? It doesn't work because 'i' will go past Sheet.Count
midway through the loop.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> This should delete sheets to the right of the active sheet.
>
> Sub delshtsbyindex()
> sc = Sheets.Count
> 'MsgBox sc
> ms = ActiveSheet.Index
> 'MsgBox ms
> For i = ms To sc
> Sheets(i).Delete
> 'MsgBox Sheets(i).Name
> Next i
> End Sub
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "ian bartlett" <(E-Mail Removed)> wrote in message
> news:uSVwk.152886$nD.140587@pd7urf1no...
>> Hi All
>>
>> I add sheets through code, and when I have printed what I want I want to
>> delete all sheets except the two on the left in this case Sheet1(Sheet2)
>> and Sheet5(Sheet1) doing it with the recorder I got the following, but
>> this will not work after rebuilding the sheets.
>>
>> Any ideas ??
>>
>> Thanks Bart
>>
>>
>> Sheets("D3").Select
>> ActiveWindow.ScrollWorkbookTabs Position:=xlLast
>> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
>> "DVG", "ISA", "3", "2", _
>> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
>> "DVK")).Select
>> Sheets("D3").Activate
>> Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
>> "UAL")).Select Replace:= _
>> False
>> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
>> "DVG", "ISA", "3", "2", _
>> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
>> "UAL")).Select
>> Sheets("UAL").Activate
>> Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
>> "HOME")).Select Replace:= _
>> False
>> ActiveWindow.SelectedSheets.Delete
>> Range("F6").Select
>>
>

Don

Chip is right it seems when I run your code I get the error
Run time error 9
Subscript out of range.

Any other ideas ??

Try code like the following.

Sub AAA()
On Error GoTo ErrH:
Application.DisplayAlerts = False
With ThisWorkbook.Worksheets
Do Until .Count <= 2
.Item(.Count).Delete
Loop
End With
ErrH:
Application.DisplayAlerts = True
End Sub



--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"ian bartlett" <(E-Mail Removed)> wrote in message
news:HAWwk.153184$nD.64076@pd7urf1no...
> Don
>
> Chip is right it seems when I run your code I get the error
> Run time error 9
> Subscript out of range.
>
> Any other ideas ??
>
>

Give this a try (I'd put it in a Module)...

Sub DeleteWorksheets()
Dim WS As Worksheet
Worksheets(1).Select
On Error GoTo Whoops
Application.DisplayAlerts = False
For Each WS In Worksheets
If WS.Index > 2 Then WS.Delete
Next
Whoops:
Application.DisplayAlerts = True
End Sub

--
Rick (MVP - Excel)


"ian bartlett" <(E-Mail Removed)> wrote in message
news:HAWwk.153184$nD.64076@pd7urf1no...
> Don
>
> Chip is right it seems when I run your code I get the error
> Run time error 9
> Subscript out of range.
>
> Any other ideas ??
>
>

another way maybe

Sub DeleteSheets()
Dim Arr() As String
'Create an Array
'of all Sheets
Dim N As Long
Application.DisplayAlerts = False
With ThisWorkbook
With .Worksheets
If .Count < 3 Then Exit Sub
ReDim Arr(3 To .Count)
For N = 3 To .Count
Arr(N) = .Item(N).Name
Next N
End With
.Worksheets(Arr).Delete
End With
Application.DisplayAlerts = True
End Sub
--
jb


"ian bartlett" wrote:

> Hi All
>
> I add sheets through code, and when I have printed what I want I want to
> delete all sheets except the two on the left in this case Sheet1(Sheet2) and
> Sheet5(Sheet1) doing it with the recorder I got the following, but this will
> not work after rebuilding the sheets.
>
> Any ideas ??
>
> Thanks Bart
>
>
> Sheets("D3").Select
> ActiveWindow.ScrollWorkbookTabs Position:=xlLast
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "DVK")).Select
> Sheets("D3").Activate
> Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
> "UAL")).Select Replace:= _
> False
> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
> "DVG", "ISA", "3", "2", _
> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB", "6",
> "UAL")).Select
> Sheets("UAL").Activate
> Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
> "HOME")).Select Replace:= _
> False
> ActiveWindow.SelectedSheets.Delete
> Range("F6").Select
>
>
>

Chip, Rick & John

Thanks very much I tried all three and all performed as stated

Thanks again.

Bart

Since I didn't really want to delete sheets I only tested with
sheets(i).select
MY BAAD!

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>> For i = ms To sc
>> Sheets(i).Delete
>> Next i
>
> Did you test that code? It doesn't work because 'i' will go past
> Sheet.Count midway through the loop.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
>
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> This should delete sheets to the right of the active sheet.
>>
>> Sub delshtsbyindex()
>> sc = Sheets.Count
>> 'MsgBox sc
>> ms = ActiveSheet.Index
>> 'MsgBox ms
>> For i = ms To sc
>> Sheets(i).Delete
>> 'MsgBox Sheets(i).Name
>> Next i
>> End Sub
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "ian bartlett" <(E-Mail Removed)> wrote in message
>> news:uSVwk.152886$nD.140587@pd7urf1no...
>>> Hi All
>>>
>>> I add sheets through code, and when I have printed what I want I want to
>>> delete all sheets except the two on the left in this case Sheet1(Sheet2)
>>> and Sheet5(Sheet1) doing it with the recorder I got the following, but
>>> this will not work after rebuilding the sheets.
>>>
>>> Any ideas ??
>>>
>>> Thanks Bart
>>>
>>>
>>> Sheets("D3").Select
>>> ActiveWindow.ScrollWorkbookTabs Position:=xlLast
>>> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
>>> "DVG", "ISA", "3", "2", _
>>> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB",
>>> "6", "DVK")).Select
>>> Sheets("D3").Activate
>>> Sheets(Array("10", "12", "3M", "ADM", "180", "PX3", "HOME",
>>> "UAL")).Select Replace:= _
>>> False
>>> Sheets(Array("D3", "5", "ATC", "CRU", "AA", "8", "DVJ", "DVH", "1",
>>> "DVG", "ISA", "3", "2", _
>>> "ISC", "7", "ISD", "D4", "9", "D2", "DVF", "4", "ISE", "ISB",
>>> "6", "UAL")).Select
>>> Sheets("UAL").Activate
>>> Sheets(Array("DVK", "10", "12", "3M", "ADM", "180", "PX3",
>>> "HOME")).Select Replace:= _
>>> False
>>> ActiveWindow.SelectedSheets.Delete
>>> Range("F6").Select
>>>
>>
>