loop through all worksheets

My workbook has over 100 worksheets. I want to be able to execute the code 
below, have it cycle through all worksheets and when done leave the 
worksheet that was active when the code was started active.

Application.ScreenUpdating = False
Dim n As Single
    For n = 1 To Sheets.Count
        With Sheets(n)
            .Activate
            .Range("A3").Activate
        End With
    Next n
Application.ScreenUpdating = True 

0
ordnance1
5/19/2010 5:27:15 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
708 Views

Similar Articles

[PageSpeed] 21

Sub LoopAllWS()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart = ActiveSheet
For Each ws In Worksheet
  With ws
    'No need to use Select use .With code here
  End With
Next ws
wsStart.Activate
End Sub




-- 
Regards
Dave Hawley
www.ozgrid.com
"ordnance1" <ordnance1@comcast.net> wrote in message 
news:F7A036D7-7091-435B-8573-96B9B6AF75DE@microsoft.com...
> My workbook has over 100 worksheets. I want to be able to execute the code 
> below, have it cycle through all worksheets and when done leave the 
> worksheet that was active when the code was started active.
>
> Application.ScreenUpdating = False
> Dim n As Single
>    For n = 1 To Sheets.Count
>        With Sheets(n)
>            .Activate
>            .Range("A3").Activate
>        End With
>    Next n
> Application.ScreenUpdating = True 

0
ozgrid
5/19/2010 5:42:35 AM
Thanks, but I get an Object Required error and the following line of text is 
highlighted:

For Each ws In Worksheet



"ozgrid.com" <dave@ozgrid.com> wrote in message 
news:ulvgcYx9KHA.3176@TK2MSFTNGP05.phx.gbl...
> Sub LoopAllWS()
> Dim ws As Worksheet
> Dim wsStart As Worksheet
>
> Set wsStart = ActiveSheet
> For Each ws In Worksheet
>  With ws
>    'No need to use Select use .With code here
>  End With
> Next ws
> wsStart.Activate
> End Sub
>
>
>
>
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "ordnance1" <ordnance1@comcast.net> wrote in message 
> news:F7A036D7-7091-435B-8573-96B9B6AF75DE@microsoft.com...
>> My workbook has over 100 worksheets. I want to be able to execute the 
>> code below, have it cycle through all worksheets and when done leave the 
>> worksheet that was active when the code was started active.
>>
>> Application.ScreenUpdating = False
>> Dim n As Single
>>    For n = 1 To Sheets.Count
>>        With Sheets(n)
>>            .Activate
>>            .Range("A3").Activate
>>        End With
>>    Next n
>> Application.ScreenUpdating = True
> 
0
ordnance1
5/19/2010 5:56:02 AM
There is a typo in the statement, use this:

For Each ws In Worksheets

Regards,
Per

"ordnance1" <ordnance1@comcast.net> skrev i meddelelsen 
news:#VWy4fx9KHA.4816@TK2MSFTNGP02.phx.gbl...
> Thanks, but I get an Object Required error and the following line of text 
> is highlighted:
>
> For Each ws In Worksheet
>
>
>
> "ozgrid.com" <dave@ozgrid.com> wrote in message 
> news:ulvgcYx9KHA.3176@TK2MSFTNGP05.phx.gbl...
>> Sub LoopAllWS()
>> Dim ws As Worksheet
>> Dim wsStart As Worksheet
>>
>> Set wsStart = ActiveSheet
>> For Each ws In Worksheet
>>  With ws
>>    'No need to use Select use .With code here
>>  End With
>> Next ws
>> wsStart.Activate
>> End Sub
>>
>>
>>
>>
>> -- 
>> Regards
>> Dave Hawley
>> www.ozgrid.com
>> "ordnance1" <ordnance1@comcast.net> wrote in message 
>> news:F7A036D7-7091-435B-8573-96B9B6AF75DE@microsoft.com...
>>> My workbook has over 100 worksheets. I want to be able to execute the 
>>> code below, have it cycle through all worksheets and when done leave the 
>>> worksheet that was active when the code was started active.
>>>
>>> Application.ScreenUpdating = False
>>> Dim n As Single
>>>    For n = 1 To Sheets.Count
>>>        With Sheets(n)
>>>            .Activate
>>>            .Range("A3").Activate
>>>        End With
>>>    Next n
>>> Application.ScreenUpdating = True
>> 
0
Per
5/19/2010 7:06:21 AM
On May 19, 10:27=A0am, "ordnance1" <ordnan...@comcast.net> wrote:
> My workbook has over 100 worksheets. I want to be able to execute the cod=
e
> below, have it cycle through all worksheets and when done leave the
> worksheet that was active when the code was started active.
>
> Application.ScreenUpdating =3D False
> Dim n As Single
> =A0 =A0 For n =3D 1 To Sheets.Count
> =A0 =A0 =A0 =A0 With Sheets(n)
> =A0 =A0 =A0 =A0 =A0 =A0 .Activate
> =A0 =A0 =A0 =A0 =A0 =A0 .Range("A3").Activate
> =A0 =A0 =A0 =A0 End With
> =A0 =A0 Next n
> Application.ScreenUpdating =3D True

Probably you want to activate the first cell of each sheet so that
user get it right A1 on reaching sheets


You can use following code for speed

Sub YourSubName()
Dim ws As Worksheet
Dim wsStart As Worksheet

Set wsStart =3D ActiveSheet
For Each ws In Worksheet
         If ws.Visible =3D xlSheetVisible Then
            Application.GoTo ws.Range("a1"), True
         End If
Next ws
wsStart.Activate
End Sub


0
Javed
5/19/2010 7:22:21 AM
Thanks

"Per Jessen" <per.jessen@mail.dk> wrote in message 
news:O8oAKHy9KHA.1892@TK2MSFTNGP05.phx.gbl...
> There is a typo in the statement, use this:
>
> For Each ws In Worksheets
>
> Regards,
> Per
>
> "ordnance1" <ordnance1@comcast.net> skrev i meddelelsen 
> news:#VWy4fx9KHA.4816@TK2MSFTNGP02.phx.gbl...
>> Thanks, but I get an Object Required error and the following line of text 
>> is highlighted:
>>
>> For Each ws In Worksheet
>>
>>
>>
>> "ozgrid.com" <dave@ozgrid.com> wrote in message 
>> news:ulvgcYx9KHA.3176@TK2MSFTNGP05.phx.gbl...
>>> Sub LoopAllWS()
>>> Dim ws As Worksheet
>>> Dim wsStart As Worksheet
>>>
>>> Set wsStart = ActiveSheet
>>> For Each ws In Worksheet
>>>  With ws
>>>    'No need to use Select use .With code here
>>>  End With
>>> Next ws
>>> wsStart.Activate
>>> End Sub
>>>
>>>
>>>
>>>
>>> -- 
>>> Regards
>>> Dave Hawley
>>> www.ozgrid.com
>>> "ordnance1" <ordnance1@comcast.net> wrote in message 
>>> news:F7A036D7-7091-435B-8573-96B9B6AF75DE@microsoft.com...
>>>> My workbook has over 100 worksheets. I want to be able to execute the 
>>>> code below, have it cycle through all worksheets and when done leave 
>>>> the worksheet that was active when the code was started active.
>>>>
>>>> Application.ScreenUpdating = False
>>>> Dim n As Single
>>>>    For n = 1 To Sheets.Count
>>>>        With Sheets(n)
>>>>            .Activate
>>>>            .Range("A3").Activate
>>>>        End With
>>>>    Next n
>>>> Application.ScreenUpdating = True
>>> 
0
ordnance1
5/19/2010 7:45:58 AM
Reply:

Similar Artilces:

Copying cells from another worksheet
Hi i have a worksheet that has part no, desciption and cost in then on the other worksheet i have a list of items with part no, description, and cost on workshhet one if i select the description i want it to fill the other cells with the part number and cost . i have created a avaidation that gives me a list box with the equipment in but how do i link it to the cells. Thanks Mark Hi At first, be sure that the key column (description) is leftmost in item list, i.e. when you want part no to appear automatically when you select description, then redesign items list, p.e. Discription, PartN...

worksheet password #2
Hey guys, After a year of not using this file, I suddenly have urgent need to open it. Problem: I do not have the password any longer. I tried everyone that I have in my list, but to no avail. Please help! I may miss a deadline at work because of this. Thanks in advance, Vincent Worksheet password or open the workbook password??? If it's the worksheet, you can find a macro that'll help at J.E. McGimpsey's site: http://www.mcgimpsey.com/excel/removepwords.html If it's the File|open password, there are commercial password breakers available: http://www.lostpassword.com/ (I...

Comparing Two Worksheets for changes
Here is the problem: I have two people on a committee that work at two different companies. They each need to work on a list of attendees for a future technology conference. Some information will be changed for the various attendees and some new attendees will be added. With "Mary" and "Betty" each working on her list it becomes difficult to maintain an updated and current list with all the attendee information. If they were on the same network, they both could access the original file and all changes would be to the original. Howver, they are not on the same network...

How do I exchanging data between worksheet and worksheet?
I'm trying to move some data from a cell to a totally different page. How do I do that? Hi! Copy and paste works between worksheets. If you want a more permanent connection, use formulae in the secon sheet to "draw down" data from the first. e.g. ='Sheet1'!A2 in a cell on sheet2 will fill that cell with th value in A2 on Sheet1 (You don't need the ' s in this case, but you will if your sheet name have spaces in them. Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?ac...

write vba to a worksheet via a macro
i have a macro that deletes tabs a, b, c, and then re-creates them with the current information i enter into tab d. i have a worksheet (beforedoubleclick) procedure that i want to incorporate into worksheet b...but the procedure is deleted each time the macro is run because the deletion of worksheet b is part of the macro. is there a way for the macro to write the beforedoubleclick procedure to worksheet b after it has been deleted and re-added? Hi You probably need to use the SheetBeforeDoubleClick within the ThisWorkbook module. This gives you the option of passing it the...

Trying to merge two excel worksheets
I was on http://office.microsoft.com/en-us/assistance/HP052012651033.aspx and was trying to merge two documents. I shared them both out, took a file I had, and copied it to my desktop, copied the file, renamed it a.xls and b.xls. I made changes to b.xls and saved it and then tried to merge. I tried to merge a.xls first into b.xls and vice versa. No luck. I posted this as a comment to Microsoft. It won't let me merge. I have copied a document to "test" and added basic text and it gives error message "b.xls cannot be merged into the current workbook. Workbooks to ...

How can I create a table of contents(worksheets) for a large work.
I have a workbook with over 100 worksheets. I want to creat a table of contents on 1 sheet to link, by name of the worksheet, instead of scrolling the tabs. Possible? Hi see: http://www.mvps.org/dmcritchie/excel/buildtoc.htm -- Regards Frank Kabel Frankfurt, Germany "brupub" <brupub@discussions.microsoft.com> schrieb im Newsbeitrag news:2199FD6C-4095-4550-8921-21CA2768314B@microsoft.com... > I have a workbook with over 100 worksheets. I want to creat a table of > contents on 1 sheet to link, by name of the worksheet, instead of scrolling > the tabs. Possible? th...

Can I "unlock" just one cell in a protected worksheet?
Is there any way I can unlock just one cell in a protected worksheet without removing the protection from the entire worksheet? I am working on a fairly complex worksheet that has 10-15 data entry cells. The rest of the worksheet contains formulas and informational text. I have the formulas pretty much debugged and working. I am now down to making mostly cosmetic changes to the text and formatting. I have marked all of the data entry cells in a different color to help me keep from overwriting a formula, but it still happens occasionally. I usually catch it right away and can recover with ...

Can a SUMPRODUCT array formula be used across multiple worksheets?
Hi - I have a workbook with 32 worksheets (Summary and 1-31) ... all sheets are formatted the same way, with names in column A and then data- values in B:L. The list of names on the Summary Sheet is all- inclusive; however, these names may or may not appear on sheets 1-31. Of course, the Summary should add up the values (per name) on sheets 1-31, and handle the fact that a) the name may not exist on a specific sheet, and b) that the value on the sheet may be an error (#N/A). Here's what I have so far, which doesn't work: =SUM(IF(ISNUMBER(VLOOKUP($A28,'1:31'!$A$2...

How do I automatically save an Excel Worksheet?
Anyone know if there is a way to automatically save an Excell Worksheet say every 5 minutes? I'm not refering to Auto Recovery. I need an actual save. -- Tony Download and install the AutoSafe Add-in by Jan Karel Pieterse: http://www.bmsltd.ie/MVP/Default.htm (note: Make sure you download AutoSafe.zip, not AutoSaveVBE.zip) In article <21FD75DF-E9A1-4B25-83B3-F8CFC0C5C29A@microsoft.com>, "Tony" <Tony@discussions.microsoft.com> wrote: > Anyone know if there is a way to automatically save an Excell Worksheet say > every 5 minutes? I'm not referin...

Copy a worksheet
H Guys can you help. i want to copy a sheet from a closed book into an open book via vba macro. Example is - run code to open the workbook, copy the only worksheet in that book and paste it into an existing sheet in a workbook that is already open. Can u help Thanks Nev You left a lot of unanswered questions so I took some liberties. You will have to change the name "Somefile" to the actual workbook name that you want to copy from. Sub cpySht() Dim wb1 As Workbook, wb2 As Workbook Set wb1 = ActiveWorkbook myPath = wb1.Path fName= m...

reference to other worksheets in FormulaR1C1
Hi All, I want to create a formula: ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])" where '01', '02', '03' are worksheet names in the same workbook. Recording a macro gives the same reference format to other sheets in the workbook. If I create this formula manually, it works after re-opening the workbook. However, if I create the formula with the line above, Excel interprets the '01'!, '02!', '03!' references as those to other workBOOKS instead of workSHEETS. It informs me that the wo...

Maximum # of worksheets in workbook
Hey Gang, Anybody know if there is a limit on the number of worksheets you can create in a single workbook? Hi nik59 Like some other features with Excel this is limited by the available memory on your system. HTH Cordially Pascal "nik59" <nik59@discussions.microsoft.com> a �crit dans le message de news:12932492-BDE1-454E-9788-51786A8B33AB@microsoft.com... > Hey Gang, > > Anybody know if there is a limit on the number of worksheets you can create in a single workbook? There is a max of 256 worksheets per workbook. >-----Original Message----- >Hey Gang, >...

Loop to copy worksheet fails irregularly
I recorded a simple macro to copy a worksheet to the same workbook multiple times. I want to be able to copy the worksheet up to 100 times. The loop works fine but fails at different intervals (mostly after 39 successful copies. I get the 1004 Copy method of worksheet fails. Why does it work fine then fail? Also why does it fail at different times? Anyone that could shed some light on this one? I run MS Office 2000 on MS XP. *****Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum & Business Software***** ...

Lookups using worksheet names
Is there anyway of automatically picking up a figure from a specific cell on various worksheets buy using the worksheet name? I have a spreadsheet that has worksheets with the clients contract numbers, e:g each worksheet would be named something like this: 000/12345678/123 I also have a spreadsheet with all the above contract numbers listed which looks up the clients names and addresses etc. The data on each sheet is always in a specific cell on each worksheet for the sake of argument cell A1. I would like to use the spreadsheet with the contract numbers listed to look up cell A1 on each s...

Change to worksheet 501 (full item count)
Currently, worksheet 501 (Request a Full Item Count) grabs the quantity from every item in your store. Our apparel stores have literally tens of thousands of SKUs, with thousands more added every six months. When I try to get the full item count, it times out. Also, not all stores carry all products, but the worksheet does not pay attention to the Inactive flag, so it tries to retreive quantities for those items as well. All of my items are subdivided by department, category, and some other custom criteria (product season, color group, fabric). Our store managers do not do a full phys...

Need more columns! How to exceed default limit for number of columns per worksheet
My worksheet has reached the limit to the number of columns it wil allow me to utilize (IV is the last lettered one): how can I overrid that limit and add about 600 more columns?? Thanks. Sally Embe -- Message posted from http://www.ExcelForum.com Hi Sally no chance with Excel -- Regards Frank Kabel Frankfurt, Germany > My worksheet has reached the limit to the number of columns it will > allow me to utilize (IV is the last lettered one): how can I override > that limit and add about 600 more columns?? > > Thanks. > > Sally Ember > > > --- > Message pos...

Unexplained Line on worksheet
I have a file that has colored lines that I did not add nor do I want as part of the file. At first I thought they were graphics that could be removed - not true. If I delete the line on which they seem to be attached, they attach to the line above. They look like a cell border, but there are no borders on any of the cells in question and it appears to start in the middle of a cell. When it runs through a chart, if you click on the chart to make any edits, it come to the foreground and covers the line. But the second you are done with the chart, the line is once again in the foreground. ...

How can I reference a worksheet in a formula based on a cell value
Row 1 contains labels / headings: A B C Sheet 1 Sheet 2 Sheet 3, etc. In row 2, part of the formula will look to a cell on the worksheet that corresponds to the heading. For example, "='Sheet 1'!A3+'Sheet 1'!A5" I'd like to "insert" the reference to the worksheet by referencing the cell in row 1. I think there must be an easy way to do it, and it would save me a lot of time. Thanks in advance! Here's the formula for B1: =INDIRECT("'"&A$1&"'!$A$3")+IND...

Change Color of Worksheet Tabs?
Excel 2003. Can I change the colors of the tabs along the bottom with worksheets names on them so they will stand out from one another? Thanks in advance... Bob Yes- Right click on the tab and select tab color. "Bob Newman" wrote: > Excel 2003. Can I change the colors of the tabs along the bottom with > worksheets names on them so they will stand out from one another? > > Thanks in advance... Bob > > > Had a play around found it must have something to do with the " N" (th leading space) If I take out the space it works, has VB not got a $ ...

How do i recover data in a worksheet that is still in a chart?
I inadvertently deleted a column of data from an Excel worksheet. A chart of these data is still present, and is properly plotted. Is there a way to recover the data? (The Excel file must store the data somewhere if it still appears in a chart.) If you right-mouse the Chart, does it have a 'Source Data' option, and if so, what range does that point to. That should be your data. Martin63 Wrote: > I inadvertently deleted a column of data from an Excel worksheet. A > chart of > these data is still present, and is properly plotted. Is there a way > to > recover...

Linking data from worksheet to worksheet
I have the first page of my workbook as my summary page. I then have 50 other worksheets that have individual data on them. How do I create a template (if you can) for the worksheets to tie their data into the main summary page. For example: I want to publish the following example data from each of the worksheets to the summary page: Project # Project Name Contract Amount Location How is your data in other sheets layout look like? -- Hope this help Please click the Yes button below if this post have helped in your needs Thank You cheers, francis &qu...

print report with data from specific worksheet
I have a workbook with multiple sheets. The first sheet contains a report that I need to print using data from a specific sheet that changes each month. For example, the report for January needs to pull data from the sheet named Jan. When it's June the report needs to pull data from the June sheet. Since I know the date how can I automate getting data from the correct sheet? I think you want to use the =indirect() function. You could put the month abbreviation in A1 of that sheet. Then use formulas like: =indirect("'" & $a$1 & "'!B99") to retrie...

Linked worksheets #4
I have a single cell in 34 worksheets linked to another worksheet. The data in the linked cells is text. In the worksheet that the cells are linked to only some of the data is shown until the linked worksheet is opened, why? After openning the worksheets all data is shown until the linked sheet is closed and excel is closed then the data is incomplete again eventhough the complete file was saved, WHY? Excel brings a maximum of 255 characters back from a closed workbook. It'll bring back the complete string when the workbook is open. John Kelly wrote: > > I have a single...

Creating new worksheet from existing
I would like to create a worksheet with unique values (rows of data, records) only from a worksheet which contains rows with multiple values. There is an identifier at the beginning of each row, I only want to see each identifier 1x in the new table. Help is appreciated. See Chip Pearson's page on working with duplicates and uniques: http://www.cpearson.com/excel/duplicat.htm -- Regards, Tom Ogilvy "ddpen" <ddpen@discussions.microsoft.com> wrote in message news:E5C4D350-1D4D-4EDC-AFCD-AC880A83DF9F@microsoft.com... > I would like to create a worksheet with unique...