Macro to affect all sheets

I want to select all sheets in a workbook, insert a column, and put a formula 
in that column. It works fine when doing it manually, but if I try to have a 
macro do this, it only affects one sheet. Is there any way to do this?
0
Utf
6/4/2010 6:33:09 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1621 Views

Similar Articles

[PageSpeed] 50

The general principal is shown in the following code.
Pls note: the code places the formula: =2+2 in cell F1 of every sheet.
I'm sure it will b no problem 4 u yo change it 4 your needs.
-----------------------
Sub All_Sheets()
    For Each SH In ActiveWorkbook.Sheets
        SH.[F1].Formula = "=2+2"
    Next
End Sub
-------------
Micky


"REMB" wrote:

> I want to select all sheets in a workbook, insert a column, and put a formula 
> in that column. It works fine when doing it manually, but if I try to have a 
> macro do this, it only affects one sheet. Is there any way to do this?
0
Utf
6/4/2010 6:48:06 PM
I know that it doesn't make sense but Excel will NOT accept all sheets in a 
macro for this. Use the suggested loop. Fast
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"REMB" <REMB@discussions.microsoft.com> wrote in message 
news:E6B69A1E-F72E-4022-B7EB-46AF202FE8AA@microsoft.com...
>I want to select all sheets in a workbook, insert a column, and put a 
>formula
> in that column. It works fine when doing it manually, but if I try to have 
> a
> macro do this, it only affects one sheet. Is there any way to do this? 

0
Don
6/4/2010 8:03:31 PM
REMB;958360 Wrote: 
> I want to select all sheets in a workbook, insert a column, and put a
> formula 
> in that column. It works fine when doing it manually, but if I try to
> have a 
> macro do this, it only affects one sheet. Is there any way to do this?
Have you tried recording the Macro on Sheet 1 first, then holding down
shift, and hilighting all tabs and running the Macro?




-- 
njmikec
0
njmikec
6/4/2010 8:45:18 PM
Reply:

Similar Artilces:

How to run a Macro in MS Publisher from DOS command prompt?
Publisher 2003 with XP Prof. I am trying to run my macro as mspub.exe "document.pub" /mMyMacro where "MyMacro" is my macro but instead of running it, it's just MS Publisher opening "document.pub" file. How can I make it open the macro (this is for automate purposes). btw: my macro security setting are set to "low". Thanks joe1977@tlen.pl wrote: > Publisher 2003 with XP Prof. > I am trying to run my macro as mspub.exe "document.pub" /mMyMacro > where "MyMacro" is my macro but instead of running it, it's just MS >...

Macro help need with worksheet unprotect/protect and closing an externally referenced file...
Hi, I have the following macro that will shell out and open another spreadsheet to bring some data back into my workbook. Somehow, I need this macro to close that externally referenced file (without saving) once it opens it, manipulates some data and copies it to bring back into my workbook. Everything works now except the closing part. The second part of my problem is that one of the sheets in this workbook is protected because of a macro button I have on that sheet. It's protected without a password. I need to get the protection to temporarily be turned off to do some things and the...

Protecting while still allowing macros to run
Hi, I have a spreadsheet with macros that update formulas in cells. Is there any way of protecting the worksheet while still allowing the user to run the macros. Hi Tammy, If you use VBA to turn on protection you can look in the help system for the arguments for Protect and you will find one called UserInterfaceOnly:=True which allows the macro to run in a protected workbook, but doesn't allow users to modify stuff manually. Cheers, Shane Devenshire "Tammy" <Tammy@discussions.microsoft.com> wrote in message news:1D21508D-2564-4AFB-AB73-98C7ABC27A7C@microsoft.com...

Replace All within selection does all sheet in 2002
Is there a way to use the "Replace All" command on a selection of cells and not the whole sheet. I'm sure that this used to be possible on eralier versions of Excel. The "Find All" command give the correct cells in the results box. ...

Macros #7
I currently have a workbook that has 10 worksheets. Nine of the 10 worksheets are brought in from another application. The 10th worksheet is added at the beginning as a summary worksheet for the other 9. At this time someone has created a macro to look in column H of worksheets 2 - 10 (may not always be 10 worksheets, could be more, could be less) for a value of 1.00 and if it exists to delete that row. While this works great, it has to be manually applied to all 9 worksheets in the workbook. Is there a way to get the macro to run for all worksheets except the first one? Thanks in...

running macros in excel
Does anyone know if I can run a macro automatically when I open the spreadsheet ? thanks. Call the macro Auto_Open, or add your code to the Workbook_Open event. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bobuk" <anonymous@discussions.microsoft.com> wrote in message news:fbb201c3f219$2e99ee50$a401280a@phx.gbl... > Does anyone know if I can run a macro automatically when I > open the spreadsheet ? thanks. ...

Need help to set-up a formula on Excel work sheet?
I need help on seting-up a formula on "Excel Work Sheet." Example: I need to subtract Cell A14 from Cell A13 and Mutiply the answer by "24" on Cell B14. Try this: =(A13-A14)*24 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "wiskeyjoexxx" <wiskeyjoexxx@discussions.microsoft.com> wrote in message news:8DB42179-1185-4D3B-BA6F-707D5D34F6BC@microsoft.com... I need help on seting-up a formula on "Excel Work Sheet." Examp...

Macros upon opening excel
I have a problem, I have a worksheet that I created and it worked fine and when you opened it it opened normally. I recently had to protect some of the cells so people wouldnt mess with my formulas and now when I open the worksheet it always asks to enable or disable the macros but I do not have any macros made. Is there something I am missing? Thanks in advance. Upload files to see. <angela.gay@gmail.com> ??????:026394a2-3c02-4066-988a-0d69e82a740e@13g2000yql.googlegroups.com... >I have a problem, I have a worksheet that I created and it worked fine > and when you opened it it ...

(2007) Excel macro blocks Outlook???
Why does a long running macro in Excel 2007 block Outlook??? (No, the macro doesn't access Outlook in any way. It does use ADO to access a database). I've noticed similar things on several occassions... where one Office 2007 App will block another (for no reason). God I hate Office 2007. -- -C. Moya www.cmoya.com Blocks Outlook in what way? From starting up? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "C. Moya" <cmm@nospam.com> wrote in mess...

format 10 sheets on same file to look like first one
Is it possible to group worksheets where the format is all exactly the same. Then ungroup to make a change on worksheet without affecting the other worksheets. To group worksheets: keep Ctrl pressed while you click on the sheet tabs If you have many: Click on first tab, keep Shift pressed, click on last tab HTH -- AP "Michael Abraham" <MichaelAbraham@discussions.microsoft.com> a �crit dans le message de news: 0E1E42C0-4A8E-4D7C-BBEB-B292F1274D21@microsoft.com... > Is it possible to group worksheets where the format is all exactly the > same. > Then ungroup to ma...

find affected tables when creating a new record in CRM frontend
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! On Nov 9, 12:12=A0pm, VistaUser123 <sunmapleleaf...@yahoo.com> wrote: > How can I find out what tables which new record has been added on when I > enter data through a form in CRM front end? I try to find way to map form= in > the CRM frontend to the tables at the CRM database at backend? It is CRM = 3.0 > > Thank in...

macro trouble
hi i have recorded a macro for my workbook to cut paste data however what i have observed is that the procedure is than some how rough when is pasteing and coming back to the last sheet. may i know how to get it done very smoothly without shaken. here is the code Sub Transfer_() ' ' Transfer_ Macro ' ' Range("Form").Select Selection.Copy Sheets("Sales ").Select Range("Sales[[#Headers],[PRODUCT ID ]]").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("Sales[[#Headers],[PRODUCT ID ...

Macro to save file using vlookup...
Here's what I'm trying to do... I want a macro that will take a date value from a cell (say B2 on sheet1) and compare it to a list of closing dates (month's end closing). It needs to then make folders for the year and month (if they don't already exist) and save the file in that folder. The sheet with the closing dates is setup like this: Month Ending Fiscal Year November 11/28/04 2005 December 12/31/04 2005 And so on... Thanks for the help up front! ...

Posting a Sheet here
Is it permissible to post a worksheet here in this group to ask and illustrate a question/problem? This is a text only newsgroup. Binary attachments are frowned upon by most. If you could explain your problem in plain text, I bet you'll get more potential responses. But you could put the file on the internets and point at that location and maybe you'll find someone who will take the time to download it. But be aware that lots of people won't open workbooks--there's just too much chance that there could be malicious code in it. http://www.savefile.com is one of those (...

How do I print pg1 and pg2 (5x8) of my novel on 1 sheet(8.5x11)?
How do I print pg1 and pg2 (5x8) of my novel on 1 sheet(8.5x11)? This is what *I* would do. It doesn't mean it's the only way tho. Print the file with a .pdf driver. (If you don't have Acrobat and prefer not to make that investment, I suggest downloading PrimoPDF from www.primopdf.com.) Open the .pdf file in Adobe Reader. Set it to print multiple pages per sheet. Print it. -- JoAnn Paules MVP Microsoft [Publisher] "Marty" <Marty@discussions.microsoft.com> wrote in message news:6BADFCBB-1931-43DA-AB4A-3E07CB1EDA24@microsoft.com... > How do I print pg1 ...

converting PDF to Excel Sheet?
Hello, I would like to convert a PDF to Excel using a simple linux command line tool. Anyone know if that is possible? The tool coul also be a windows tool, but I am looking for a tool that is completel automated, with no user intevention. The reason is that I have a program that downloads PDFs every hour o so, and I would like to convert that pdf to an excel sheet so i coul grab data off it easily. Or maybe, does anyone know of an easier way to grab tables informatio off PDFS? thank -- Message posted from http://www.ExcelForum.com Take a look at PDF and Excel http://www.mvps...

Any Way to Save Eons of Typing? Automating 3D Reference to Sheet N
I have a workbook with 600 worksheets. Every worksheet is named by the same naming convention "Table1", "Table2", etc. and data is organized in the same fashion on every sheet. I need to return on a Summary sheet the values from cell B13 next to a column containing the hyperlink reference. I've been using a 3d Reference, "Table1!B13", "Table2!b13", etc. I have no problems changing or manipulating the cell value, B13, as needed. The problem is that I am endlessly typing "Table1, Table2...Table600". Or, I am hoping to ...

Macro to change view of sheets
I have a workbook with about 85 sheets happen that we have to change the view from Normal to Page break preview wvery month, I could record a macro (with the recorder), page by page and it works, but could somebody help me to get a condensed macro, because I have to set another macro to changed them back to Normal. Thanks for your help. Fernando One way: Public Sub ToggleViews() Dim wkSht As Worksheet Dim nView As Long Dim sOldActive As String With Application .ScreenUpdating = False .EnableEvents = False End Wi...

Macro to add numbers to create combinations that equal certain amo
Thank you for your assistance. I receive a list of numbers, usually over one hundred items. I need to find out which amounts will add up to a given total. How would I create a macro to do this? Is there already an Excel function to do this that I have not found? It would need to work with currency. Ex: 9, 12, 7, 4, 15, 8, 10, 3 total needed 18 Solution 8+10; 3+15 Lauren, With a hundred different items, there are over 2^100 possible ways to combine them. You would need a supercomputer - or maybe even multiple supercomputers. If you had fewer than 25, you could do it. HTH, Bernie...

Balance sheet
I was using a balance sheet to keep track of my expenses and incoming money. When the page was full I could no longer add any further transactions. How do I insert another page or make it larger? Hi Plowleg, How is your data set up? Can you be a bit more specific. -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=482550 We are using the template Balance Sheet. There is 5 columns, the date,...

passing values to other sheets
hi i am having the columns in the sheet1 date no company amount 01/01/01 1 a 120 01/02/01 2 a 240 01/03/01 3 b 250 in the sheet2 i like to have the values of the company a with the following columns date,no,amount You would need code to bring across just the rows containing Company a, but you can do a few things to show just Company a results 1) Copy the data to Sheet 2 and filter it (Data>Filter>Auto Filter) 2) Build a pivot ...

Where is the macro?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel A form field executes a macro upon exiting the field. The macro works as programmed. However, I now want to edit it and cannot find it on the dropdown list of macros. I do not recall if I saved it with the document or in a re-named template (or in Normal.dot). <br><br>Ron You probably saved it as "Private", in which case the only way to find it would be to use Find in the VB Editor to look for the name. It won't appear in any list. Cheers On 29/01/10 1:24 AM, in article 59bb1c2...

How to use Windows command within Excel macros
Hi, I want to select a bunch of text data separated by commas from a website, use Windows copy command to copy and paste it into a worksheet in Notepad and save it as a text file. How can I do that? That's all. In case you want to know further, I then can open the text file within Excel as a comma delimited data and manipulate it as much as I like. Thanks for any help. TIA Tom Hi Tom --From 'Insert' menu select 'Object' --Select 'Word Pad Document' --Select 'Display As Icon' if you want.. --Edit,save etc; as you want -- ...

One Chart, Same Data ranges, different source sheets
Hi All! I have a sheet with numerous charts. The charts are standard, much like a dashboard. The charts are fed from one sheet which is a rollup of summary data from many other sheets. I need to create a similar summary sheet for each of the remaining regions, but only need one sheet with charts. Is there a way to make the SHEET reference for the charts dynamic - say, populated by the user selecting an entry from a drop menu? The desired behavior is as follows: The Chart Sheet show 10 charts reflecting various data for Region A. I want to be able to change all the char...

Anyone developed an Excel sheet for Cost of Quality? #2
...