Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
On May 5, 12:03*pm, RLN wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
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 RLN wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
On May 5, 3:09*pm, RLN wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
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. Set DestCell = Worksheets.Add(after:=Sheets(Sheets.Count)).Range( "A1")<< 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets via Macro
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. RLN wrote: 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. Set DestCell = Worksheets.Add(after:=Sheets(Sheets.Count)).Range( "A1")<< 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Custom Views when Deleting Sheets | Excel Programming | |||
Deleting Sheets | Excel Programming | |||
Deleting all sheets except one | Excel Programming | |||
Deleting Sheets | Excel Programming | |||
deleting sheets | Excel Discussion (Misc queries) |