Code #2

The following code works well, however how can I change 
this to run with a command button.
Also is there any way I could delete some columns from 
the sheets as well.  

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations 
summary", "RCA incl RIM", "First Qtr", "Second 
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

End Sub

Thanks

Mark

0
anonymous (74722)
3/14/2005 8:51:22 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
882 Views

Similar Articles

[PageSpeed] 36

Hi Mark

This example will delete column A and B in each sheet

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you use Alt-F8 you get a list of your macro's
Select "test" and press Run

Or right click on a commandbar and click on Forms
Add a button on your sheet and assign the macro to it

Sub test()
    Dim wb As Workbook
    Dim sh As Worksheet
    Worksheets.Copy
    Set wb = ActiveWorkbook
    Application.DisplayAlerts = False
    wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth 
Qtr")).Delete
    Application.DisplayAlerts = True

    For Each sh In wb.Worksheets
        sh.Columns("A:B").EntireColumn.Delete
    Next
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark" <anonymous@discussions.microsoft.com> wrote in message news:68f601c52872$ff91dfa0$a501280a@phx.gbl...
> The following code works well, however how can I change
> this to run with a command button.
> Also is there any way I could delete some columns from
> the sheets as well.
>
> Private Sub Workbook_Open()
> Dim wb As Workbook
> 'copy all sheets
> Worksheets.Copy
> Set wb = ActiveWorkbook
> Application.DisplayAlerts = False
> 'delete the sheets you want
> wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
> summary", "RCA incl RIM", "First Qtr", "Second
> Qtr", "Third Qtr", "Fourth Qtr")).Delete
> Application.DisplayAlerts = True
>
> End Sub
>
> Thanks
>
> Mark
> 


0
rondebruin (3789)
3/14/2005 9:16:22 AM
Soz i have posted the first part of this query twice.
i now have the command button working. 
so can anyone help with the second part ie deleting 
colums. Is there anyway you can stop the command button 
from transfering to the new sheet.

thanks for this 

mark.
>-----Original Message-----
>The following code works well, however how can I change 
>this to run with a command button.
>Also is there any way I could delete some columns from 
>the sheets as well.  
>
>Private Sub Workbook_Open()
>Dim wb As Workbook
>'copy all sheets
>Worksheets.Copy
>Set wb = ActiveWorkbook
>Application.DisplayAlerts = False
>'delete the sheets you want
>wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations 
>summary", "RCA incl RIM", "First Qtr", "Second 
>Qtr", "Third Qtr", "Fourth Qtr")).Delete
>Application.DisplayAlerts = True
>
>End Sub
>
>Thanks
>
>Mark
>
>.
>
0
anonymous (74722)
3/14/2005 9:17:39 AM
Soz i have posted the first part of this query twice.
i now have the command button working. 
so can anyone help with the second part ie deleting 
colums. Is there anyway you can stop the command button 
from transfering to the new sheet.

thanks for this 

mark.
>-----Original Message-----
>The following code works well, however how can I change 
>this to run with a command button.
>Also is there any way I could delete some columns from 
>the sheets as well.  
>
>Private Sub Workbook_Open()
>Dim wb As Workbook
>'copy all sheets
>Worksheets.Copy
>Set wb = ActiveWorkbook
>Application.DisplayAlerts = False
>'delete the sheets you want
>wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations 
>summary", "RCA incl RIM", "First Qtr", "Second 
>Qtr", "Third Qtr", "Fourth Qtr")).Delete
>Application.DisplayAlerts = True
>
>End Sub
>
>Thanks
>
>Mark
>
>.
>
0
anonymous (74722)
3/14/2005 9:17:41 AM
this work great thanks for your help.

>-----Original Message-----
>Hi Mark
>
>This example will delete column A and B in each sheet
>
>Alt-F11
>Insert>Module from the menubar
>paste the sub in there
>Alt-Q to go back to Excel
>
>If you use Alt-F8 you get a list of your macro's
>Select "test" and press Run
>
>Or right click on a commandbar and click on Forms
>Add a button on your sheet and assign the macro to it
>
>Sub test()
>    Dim wb As Workbook
>    Dim sh As Worksheet
>    Worksheets.Copy
>    Set wb = ActiveWorkbook
>    Application.DisplayAlerts = False
>    wb.Sheets(Array("Suspense", "RCA exc 
RIM", "Operations summary", "RCA incl RIM", "First 
Qtr", "Second Qtr", "Third Qtr", "Fourth 
>Qtr")).Delete
>    Application.DisplayAlerts = True
>
>    For Each sh In wb.Worksheets
>        sh.Columns("A:B").EntireColumn.Delete
>    Next
>End Sub
>
>
>-- 
>Regards Ron de Bruin
>http://www.rondebruin.nl
>
>
>
>"Mark" <anonymous@discussions.microsoft.com> wrote in 
message news:68f601c52872$ff91dfa0$a501280a@phx.gbl...
>> The following code works well, however how can I change
>> this to run with a command button.
>> Also is there any way I could delete some columns from
>> the sheets as well.
>>
>> Private Sub Workbook_Open()
>> Dim wb As Workbook
>> 'copy all sheets
>> Worksheets.Copy
>> Set wb = ActiveWorkbook
>> Application.DisplayAlerts = False
>> 'delete the sheets you want
>> wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
>> summary", "RCA incl RIM", "First Qtr", "Second
>> Qtr", "Third Qtr", "Fourth Qtr")).Delete
>> Application.DisplayAlerts = True
>>
>> End Sub
>>
>> Thanks
>>
>> Mark
>> 
>
>
>.
>
0
anonymous (74722)
3/14/2005 9:39:06 AM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Outlook 2003 and Word XP #2
Is it possible to use Word XP as an email editor in Outlook 2003? Hello , I recently asked this same question in this group an MS rep said no its not possible Sorry , But this answer kind of upsets me , why should I have to purchase all MS products to use the interactivity with previous versions. I only purchased the New outlook to limit the amount of spam recieved. An email client should have no bearing on the other products. After installing outlook 2003 my mail merges do not work , there is no interactivity with office xp. Please Microsoft stop forcing me to buy all your prod...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

Inventory spreadsheet #2
Does anyone know if there is a Inventory spreadsheet available so that I can do cycle counts? Preferably a free one. ...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Page 1, Page 2..shows up in the middle of the worksheet.
How can I get Page 1, page 2...to not show up in the middle of the worksheet on the screeen. It does not print, but it bothers me when trying to work. there are also borders that are showing up with the page 1... Please help. -- Web Traveler You are in Page Break Preview mode. Go to the View menu, and choose 'Normal'. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tink54" <Tink54@discussions.microsoft.com> wrote in message news:A37A4591-0119-4A67-BABD-C59DD3D92381@microsoft.com... > How can I get Pag...

outlook network ports #2
Anybody familiar with network ports Outlook 2002 uses to establish a connection to Exchange 2000 (including the AD). Netstat reveals some information but I'm looking for some documentation from Microsoft. Thanks ...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

Separating 2 exchange servers to use two different internet source
I have 2 servers. One is connected to a t1, the other is connected to a dsl line which has different dns address and firewall than our normal network. This server has two nics. How do I setup this server only to send outbound email using the dsl line. Right now I show 3 bridgehead servers these two and one in another state. The reason I am doing this is to lower the outbound traffice on the t1. Are you trying to change the outbound interface on one Exchange server, or restrict outbound smtp traffic for your Exchange routing group/organization to the one server? It also would have he...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

currency #2
I have a cash expenses table. and some of it is in pounds and some in $. is there a possibility to make a sum that recognizes the sign of the currency? so I'll have a column of the total in pound and total in $? for example A B C $45 $55 �20 $100 sum: $200 �20 How do those currency symbols get put into the cell? Take a look at the formula bar when you select one of these cells. Do you see the currency symbol on any of them? If the value of the cell is just a number and the cell is given a special format, then you'd need a macro to look at the .tex...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Ending Cash Balance in Investment Transactions Register vs Cash Transactions #2
This subject is the same as a previous topic. There didn't seem to be a conclusion in the last discussion of this problem, so I decided to address it once again for those who may still be suffering from this issue. Nonetheless, it's probably worth it for you to read the previous discussion for additional background and context information. THE PROBLEM: In MS Money 2004, for whatever reason, some transactions are stored as Opening Balance transactions and they're hidden from the user such that the opening balance will always be greater than zero even when there are no transaction...

Outlook Shutdown #2
I am able to send/receive emails thru Outlook 2003. However, the program will not completely shutdown (the icon remains in the lower right)and I have to use the task manager to complete the shutdown. Then, when I restart outlook, I receive the message to start in "safe mode". What could be keeping outlook from completely shutting down? (OS=XP prof, 56K modem shared on a home nw) Thanks, Tracy ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Sorting Values #2
I would like to sort out a list of options. You can see me example at http://spreadsheets.google.com/ccc?key=rtwCZLHXFwrJu-gj88zwQPg I don't know if this should be a function in the sheet or a macro. Pretty much want to look at and CONCATENATE the 1st 3 columns and drop out any duplicates. May results should look like cloumns G & H Diana http://people.tribe.net/littleredhairedgirl littleredhairedgirl wrote: > I would like to sort out a list of options. You can see me example at > http://spreadsheets.google.com/ccc?key=rtwCZLHXFwrJu-gj88zwQPg > > I don't know if thi...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

Display names #2
planning a migration from exchange 5.5 to a live 2003 organisation, both in seperate organistations. The 2003 uses a display name of 'surname, firstname' and the 5.5 uses 'firstname, surname', wanted to know the easiest way of bulk changing the 5.5 display names to 'surname, firstname'? -- Many Thanks Nikrep <Nikrep@discussions.microsoft.com> wrote: >planning a migration from exchange 5.5 to a live 2003 organisation, both in >seperate organistations. The 2003 uses a display name of 'surname, >firstname' and the 5.5 uses 'firstname...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...

Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...

help with/for pivottable code
Hi everyone! I have code that takes a large workbook and divides it into a number of separate new workbooks based on data that is in column A. What i need is to create code that will create a pivot table in the new workbook. here is my code: Sub Regionalize() Dim wks As Worksheet Dim wksNew As Worksheet Dim wbk As Workbook Dim rng As Range Dim cell As Range Dim lRow As Long Dim sFileName As String Dim sFolder As String Dim sRegion As String Set wks = Sheets("region") Set rng = wks.Range("regiondata") ...