Deleting Sheets via Macro

[RLN]

RE: Excel 2003 SP3 / WinXP SP3

I have an Excel File that has 5 sheets: NewSheet1, NewSheet2,
NewSheet3, NewSheet4, and Sheet1.
Only 'Sheet1' one contains data, and in this case, the other four do
not. So I want to delete the other four sheets.

Problem is, after 'NewSheet1' is deleted, it jumps to 'NewSheet3' and
asks to delete that one an appears to skip over 'NewSheet2'. I need
to have the other four deleted if they do not contain any data. I've
been trying to debug this one for quite a while, and cannot see what
is wrong with the code. If there is another way to do this to delete
the empty sheets only, a code sample would be helpful.

Thanks.

Here is my code currently: (indented from original layout)
<begin code>
Sub DeleteExtraSheets()
' Excel Macro - coded to run inside of an Excel file.
' DeleteExtraSheets Macro
' Macro recorded 5/5/2009

Dim strMacroTitle As String
Dim SheetObject As Object
Dim x As Integer
Dim strMsgYes As String 'used for concatenation based on
condition
Dim strMsgNo As String
Dim intMsgResult 'dimmed as Variant so we can use vbYes/vbNo

strMacroTitle = "Excel Macro-DeleteExtraSheets"
For Each SheetObject In ActiveWorkbook.Sheets
x = x + 1
Sheets(Sheets(x).Name).Select 'select this sheet for deletion
prior to possible deletion
intMsgResult = MsgBox("Delete this sheet: '" & Sheets
(x).Name & "'", vbYesNo, strMacroTitle)

Select Case intMsgResult
Case vbYes
ActiveWindow.SelectedSheets.Delete
strMsgYes = strMsgYes & "Sheet " & "'" & Sheets
(x).Name & "' was deleted." & vbCrLf

Case vbNo
strMsgNo = strMsgNo & "Sheet " & "'" & Sheets
(x).Name & "' was not deleted." & vbCrLf

Case Else
'do nothing here
End Select

Next SheetObject

MsgBox strMsgYes & vbCrLf & strMsgNo, vbOKOnly, strMacroTitle

End Sub

<end code>

 

[meh2030]

RE: Excel 2003 SP3 / WinXP SP3

I have an Excel File that has 5 sheets:  NewSheet1,  NewSheet2,
NewSheet3, NewSheet4, and Sheet1.
Only 'Sheet1' one contains data, and in this case, the other four do
not.  So I want to delete the other four sheets.

Problem is, after 'NewSheet1' is deleted, it jumps to 'NewSheet3' and
asks to delete that one an appears to skip over 'NewSheet2'.  I need
to have the other four deleted if they do not contain any data.  I've
been trying to debug this one for quite a while, and cannot see what
is wrong with the code.  If there is another way to do this to delete
the empty sheets only, a code sample would be helpful.

Thanks.

Here is my code currently:  (indented from original layout)
<begin code>
Sub DeleteExtraSheets()
' Excel Macro - coded to run inside of an Excel file.
' DeleteExtraSheets Macro
' Macro recorded 5/5/2009

    Dim strMacroTitle As String
    Dim SheetObject As Object
    Dim x As Integer
    Dim strMsgYes As String   'used for concatenation based on
condition
    Dim strMsgNo As String
    Dim intMsgResult  'dimmed as Variant so we can use vbYes/vbNo

    strMacroTitle = "Excel Macro-DeleteExtraSheets"
    For Each SheetObject In ActiveWorkbook.Sheets
         x = x + 1
        Sheets(Sheets(x).Name).Select  'select this sheet for deletion
prior to possible deletion
         intMsgResult = MsgBox("Delete this sheet:  '" & Sheets
(x).Name & "'", vbYesNo, strMacroTitle)

         Select Case intMsgResult
                Case vbYes
                     ActiveWindow.SelectedSheets.Delete
                     strMsgYes = strMsgYes & "Sheet " & "'" & Sheets
(x).Name & "' was deleted." & vbCrLf

                Case vbNo
                     strMsgNo = strMsgNo & "Sheet" & "'" & Sheets
(x).Name & "' was not deleted." & vbCrLf

                Case Else
                    'do nothing here
         End Select

    Next SheetObject

    MsgBox strMsgYes & vbCrLf & strMsgNo, vbOKOnly, strMacroTitle

End Sub

<end code>

RLN,

The code you have written will loop through each sheet in the
workbook, moving to the right starting at the left-most sheet. I'm
not sure what you mean by the program "skipping over 'NewSheet2'"
because the loop will eventually get there. If you want the sheet
names to be in order, then you'll have to sort the sheets prior to
deletion. In terms of deleting "empty sheets," there are a few
different ways to do this based on your criteria. If your data is
always on 'Sheet1' and you always want all other sheets deleted then
you can something like what is listed below. The code assumes that
all of your sheets are worksheets.

Best,

Matthew Herbert

Sub DeleteSheets()
Dim Wks As Worksheet
Dim strSheet As String

'worksheet name to NOT delete
strSheet = "Sheet1"

For Each Wks In ActiveWorkbook.Worksheets
If Wks.Name <> strSheet Then

'turn off DisplayAlerts
Application.DisplayAlerts = False

'delete the worksheet
Wks.Delete

'turn off DisplayAlerts
Application.DisplayAlerts = True
End If
Next
End Sub

 

[RLN]

Matt,
Your solution for the sheet deletion worked very nicely here, thank
you very much!


One more thing I am trying to do:
I have a sheet that was created in Excel 2007 that we get from an
outside 3rd party. It has approx 257k rows in it, give or take 10-30k
from month to month.
The data is on "Sheet1". When we receive this file, "Sheet1" is the
only tab.
I am trying to write a routine that dynamically calculates the total
rows, then divides that by 65000 to get the number of sheets and then
copies and pastes blocks of rows in 65000 increments to all of the
smaller "sub-sheets" dynamically created, then deletes the larger
"sheet1" tab, then saves the file in Excel 2003 format.

I tried line #2 unsuccessfully using "longs" as variables.
lngBeginRange was equal to 1, while lngEndRange was equal to 65000 for
the first iteration of the loop.
Using line 1 with literals didn't seem to work either.
These seven lines of code are part of a loop that cycles through x
number of iterations. (where x=total number of sub sheets that will
contain 65k rows each from the main sheet)

1. Rows("1:65000").Select 'select rows from main sheet
2. ' Rows(lngBeginRange & ":" & lngEndRange).Select
Selection.Copy
Sheets("NEWTESTSheet" & intLooper).Select 'give this sheet focus for
the paste coming up...
Range("A1").Select 'place the cursor in the first cell of the new
sheet....
ActiveSheet.Paste 'paste the rows...
DoEvents 'make sure all prior lines of code are run before saving the
file....

Since Line 1 and 2 do not work, I'm a bit stumped, wondering if there
is a better way.

 

[Dave Peterson]

If you're counting rows, the using Long's should be ok.

Maybe you can incorporate some of this into your code:

Option Explicit
Sub testme()

Dim pCtr As Long
Dim lCtr As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim wks As Worksheet
Dim DestCell As Range

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
myStep = 30000

pCtr = 0
For lCtr = 1 To LastRow Step myStep
Set DestCell _
= Worksheets.Add(after:=Sheets(Sheets.Count)).Range("A1")
pCtr = pCtr + 1
DestCell.Parent.Name = "Split_" & Format(pCtr, "000")
.Rows(lCtr).Resize(myStep).Copy _
Destination:=DestCell
Next lCtr
End With

Application.DisplayAlerts = False
'wks.Delete 'uncomment after you test!
Application.DisplayAlerts = True

End Sub

 

[meh2030]

Matt,
Your solution for the sheet deletion worked very nicely here, thank
you very much!

One more thing I am trying to do:
I have a sheet that was created in Excel 2007 that we get from an
outside 3rd party.  It has approx 257k rows in it, give or take 10-30k
from month to month.
The data is on "Sheet1".  When we receive this file, "Sheet1" is the
only tab.
I am trying to write a routine that dynamically calculates the total
rows, then divides that by 65000 to get the number of sheets and then
copies and pastes blocks of rows in 65000 increments to all of the
smaller "sub-sheets" dynamically created, then deletes the larger
"sheet1" tab, then saves the file in Excel 2003 format.

I tried line #2 unsuccessfully using "longs" as variables.
lngBeginRange was equal to 1, while lngEndRange was equal to 65000 for
the first iteration of the loop.
Using line 1 with literals didn't seem to work either.
These seven lines of code are part of a loop that cycles through x
number of iterations.  (where x=total number of sub sheets that will
contain 65k rows each from the main sheet)

1.  Rows("1:65000").Select   'select rows from main sheet
2.  ' Rows(lngBeginRange & ":" & lngEndRange).Select
Selection.Copy
Sheets("NEWTESTSheet" & intLooper).Select  'give this sheet focus for
the paste coming up...
Range("A1").Select  'place the cursor in the first cell of the new
sheet....
ActiveSheet.Paste  'paste the rows...
DoEvents  'make sure all prior lines of code are run before saving the
file....

Since Line 1 and 2 do not work, I'm a bit stumped, wondering if there
is a better way.

RLN,

Dave gave you some good code to use. Simply repost if you are still
having issues.

Best,

Matt

 

[RLN]

Dave,
This example worked very nicely...thank you!

I do have a couple of questions on a few lines of code you provided,
not because I doubt your expertise here, but only because I want to
understand a little more what Excel is doing.
As I stepped through the code with the debugger, your line added the
new blank sheet to the workbook and worked fine.
However, when I recorded a macro to do the same thing, Excel gave me
these three lines of code:
-Sheets.Add
-Sheets("Sheet11").Select
-Sheets("Sheet11").Name = "NEWSHEET10" 'renamed the sheet

I'm just wondering why Excel wouldn't provide better code where you
can set properties ("after:=") on the same line. I haven't written
too many macros inside of Excel and it would have been good for Excel
to provide a line such as what you gave here in your example. (It's
Microsoft...I know, and that is why we have these newsgroups.)

I did not know that this:
-Sheets("Sheet11").Name = "NEWSHEET10" 'renamed the sheet
is the same as this....
-DestCell.Parent.Name = "Split_" & Format(pCtr, "000")
....only in your example I like how you used the format function here.
I was not aware of the "parent.Name" property.

Finally....
.Rows(lCtr).Resize(myStep).Copy Destination:=DestCell
I didn't realize this would do a copy/paste in the same line of code.
All of my macros I tried to record did them in two steps and they did
not work very well when performing it in a bulk dump fashion to
multiple sheets.

Again Dave, thank you for your example, it works very nicely, and I
appreciate your assistance here.

 

[Dave Peterson]

xl could be recording in steps, because you have to do each thing one step at a
time.

But once you get more familiar with code, you'll find that you record macros to
get syntax/keywords/arguments, but then change them to something that is easier
to understand (and takes less time to run).

But that comes through experience.

And almost all objects are children to something.

Ranges are in a worksheet.
Worksheets are in a workbook.
Workbooks are in the application.
And the application is in...

Sometimes, it's nice to refer to the worksheet with its own variable. Sometimes
(if you're not using it very much), it's just as easy to use .parent (of a
range).

=====
ps. One of the way to learn is to frequent this newsgroup. You'll see lots of
styles -- some you like and some you don't.

But after you know more, you'll realize how difficult it is to modify any macro
that was developed by recording a macro while doing it manually. So much
depends on the selection (and the layout of the data in the worksheet), that it
can be a miserable job to make it pretty.