Need help with for each statement

A

ashish

New Member
Joined
Feb 1, 2005
Messages
2
I have an excel sheet with 2 tables ranging over 12 weeks of data in 1 sheet ex. sheet name "80.6616 (5DX)". I have to update both tables on each sheet on a weekly basis by incrementing the week number and setting their ranges to 0.

I figured out how to increment the week and set ranges to 0. The problem I have is that there are 78 sheets of the exact same format and the same code applies to each and every sheet.

So I selected each sheet and pasted that code for it. When I try to compile it, I get and error that says "procedure too long". Is there a way to just list all the sheets in 1 line and then ask the macro to run the code for each sheet in that line?

I am pasting part of the code I wrote. I will be very grateful for any help or direction I get.

Thank you.


There are a lot of sheets named "80.6616 (5DX)", "80.6616 (FPT)", "81.71326-13T-GBE (BRN)", "81.71326-13T-GBE (FCT)", "81.71326-13T-GBE (FCT)", "81.71326-13T-GBE (JTAG)", ......... etc. 78 in total.

Code: 
Sheets("80.6616 (5DX)").Select   
        Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("80.6616 (FPT)").Select
        Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-GBE (BRN)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-GBE (FCT)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-GBE (JTAG)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-IR (BRN)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-IR (FCT)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-IR (JTAG)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If
Sheets("81.71326-13T-SR (BRN)").Select
       Range("T5:Y15").Select
        Selection.Copy
        Range("T4").Select
        ActiveSheet.Paste
        Range("T15:W15").Select
        Selection.ClearContents
        Range("Y15").Select
        Selection.ClearContents
        Range("V19:AF24").Select
        Selection.Copy
        Range("U19").Select
        ActiveSheet.Paste
        Range("AF19:AG25").Select
        Selection.ClearContents
           If Range("T14") > 51 Then  ' for trend data
              Range("T15") = 1
              Range("U15") = 0
              Range("V15") = 0
              Range("Y15") = 0
           Else: Range("T15") = Range("T14") + 1
                 Range("U15") = 0
                 Range("V15") = 0
                 Range("Y15") = 0
           End If
           If Range("AE19") > 51 Then  ' for TDE data
              Range("AF19") = 1
              Range("AF20:AF24") = 0
           Else: Range("AF19") = Range("AE19") + 1
                 Range("AF20:AF24") = 0
           End If                                      '................. for 78 sheets
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Firstly you need to take a step back and rethink this code now - before you become reliant on it - it will be a monster that comes back to haunt you (beleive me I think most of us have been there).

You need to break your code down into chunks. Anything repeating - shouldnt. You should cut out one block and make it its own sub - then pass it the object you want it to work on eg:



Private Sub DoAJob(MyWorksheet as worksheet)

with MyWorksheet

Selection.ClearContents
If Range("T14") > 51 Then ' for trend data
Range("T15") = 1
Range("U15") = 0
Range("V15") = 0
Range("Y15") = 0
Else: Range("T15") = Range("T14") + 1
Range("U15") = 0
Range("V15") = 0
Range("Y15") = 0
End If


end with

end sub
 
Upvote 0
This example Just has you list all the sheets you want to goto as an array at the top of the procedure. :oops: THOUGH .... since you have 78 sheets to go thru you will probably want to use V2 or V3 listed in the next postings .... it does NOT require you to specify each sheet name.... :wink:

Public Sub ResetSheets()
SheetArray = Array("80.6616 (5DX)", "80.6616 (FPT)", "81.71326-13T-GBE (BRN)", _
"81.71326-13T-IR (BRN)", "81.71326-13T-IR (FCT)")
For Each Sh In SheetArray
With Sheets(Sh)
.Range("T5:Y15").Copy
.Paste Destination:=.Range("T4")
.Range("T15:W15").ClearContents
.Range("Y15").ClearContents
.Range("V19:AF24").Copy
.Paste Destination:=.Range("U19")
.Range("AF19:AG25").ClearContents
If .Range("T14") > 51 Then ' for trend data
.Range("T15") = 1
Else: .Range("T15") = Range("T14") + 1
End If
.Range("U15") = 0
.Range("V15") = 0
.Range("Y15") = 0
If .Range("AE19") > 51 Then ' for TDE data
.Range("AF19") = 1
Else: .Range("AF19") = Range("AE19") + 1
End If
.Range("AF20:AF24") = 0
End With
Next Sh
End Sub
 
Upvote 0
This version resets ALL the sheets in your activeworkbook that have a bracket in its name ... In other words it will reset ALL 78 SHEETS :wink:

Public Sub ResetSheetsv2()
For Each Sh In Worksheets
If InStr(1, Sh.Name, ")") > 0 Then
With Sheets(Sh.Name)
.Range("T5:Y15").Copy
.Paste Destination:=.Range("T4")
.Range("T15:W15").ClearContents
.Range("Y15").ClearContents
.Range("V19:AF24").Copy
.Paste Destination:=.Range("U19")
.Range("AF19:AG25").ClearContents
If .Range("T14") > 51 Then ' for trend data
.Range("T15") = 1
Else: .Range("T15") = Range("T14") + 1
End If
.Range("U15") = 0
.Range("V15") = 0
.Range("Y15") = 0
If .Range("AE19") > 51 Then ' for TDE data
.Range("AF19") = 1
Else: .Range("AF19") = Range("AE19") + 1
End If
.Range("AF20:AF24") = 0
End With
End If
Next Sh
End Sub

:eek: IN OTHER WORDS .... lets find something common in each worksheet name and use that as a criteria for "resetting" that sheet.
eg ... If InStr(1, Sh.Name, ")") > 0 Then
The above "InStr" tests to see if there is a bracket in the name of the current sheet ... if there is then that sheet is "reset"

You then go through each sheet ... "For Each Sh In Worksheets " ... testing the sheet name against the criteria ... InStr(1, Sh.Name, ")") > 0 Then :wink:
 
Upvote 0
Same concept as Version 2 ... just cleaned up a bit more ...

Public Sub ResetSheetsv3()
For Each Sh In Worksheets ' LOOK AT EACH SHEET IN WORKBOOK
If InStr(1, Sh.Name, ")") > 0 Then ' SEE IF SHEET NAME HAS BRACKET IN IT
With Sheets(Sh.Name) ' IF THERE IS BRACKET IN NAME THEN RESET VALUES
.Range("T5:Y15").Copy
.Paste Destination:=.Range("T4")
.Range("T15:W15, Y15, AF19:AG25").ClearContents
.Range("V19:AF24").Copy
.Paste Destination:=.Range("U19")
If .Range("T14") > 51 Then ' for trend data
.Range("T15") = 1
Else: .Range("T15") = Range("T14") + 1
End If
.Range("U15, Y15, V15") = 0
If .Range("AE19") > 51 Then ' for TDE data
.Range("AF19") = 1
Else: .Range("AF19") = Range("AE19") + 1
End If
.Range("AF20:AF24") = 0
End With
End If
Next Sh
End Sub
 
Upvote 0
Nimrod,

I cannot thank you enough for this help. (y) (y) (y)

Man I owe you a couple Molson's, I got that from a canadian buddy I work with. He too is learning VBA and we were just blown away.

I am a Quality engineer working for a company called Sanmina-SCI, Kenosha, WI and do a lot of report and Data stuff on Excel and slowly trying to lear Macro.

This stuff is kool.

The code really works for all the sheets as it has ")" common in it. I would have never thought of that to use in a string search.

Neways here is the CODE for all other guys who might ever need it.

Again thank you Nimrod.


Code: 
Sub updaterange()
'
' updaterange Macro
' Macro recorded 9/23/2004 by athaker


For Each Sh In Worksheets
If InStr(1, Sh.Name, ")") > 0 Then
With Sheets(Sh.Name)
        .Range("T5:Y15").Copy
        .Paste Destination:=.Range("T4")
        .Range("T15:W15").ClearContents
        .Range("Y15").ClearContents
        .Range("V19:AF24").Copy
        .Paste Destination:=.Range("U19")
        .Range("AF19:AG25").ClearContents
           If .Range("T14") > 51 Then  ' for trend data
              .Range("T15") = 1
           Else: .Range("T15") = .Range("T14") + 1
           End If
                 .Range("U15") = 0
                 .Range("V15") = 0
                 .Range("Y15") = 0
           If .Range("AE19") > 51 Then  ' for TDE data
              .Range("AF19") = 1
           Else: .Range("AF19") = .Range("AE19") + 1
           End If
                 .Range("AF20:AF24") = 0
End With
End If
Next Sh

End Sub
 
Upvote 0
Hello ashish
Glad this work out for you. It's always nice to know when the suggestions I make are appreciated ... Cheers mate :biggrin: :wink:

... PM me if you feel I can help you further ....
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
A value used in the formula is of the wrong data type.
Replies
4
Views
1K
Peter_SSs
Peter_SSs
N
  • Question
Changing recorded macro when trying to format a sheet - Need dynamic selection of cells
Replies
2
Views
289
nidhinair22
N
S
  • Question
Please Help! Trying to get my function to repeat for each row of my table range but it only does the first row and stops
Replies
1
Views
304
davesexcel
davesexcel
B
  • Question
Create single list of value from same cells across multiple sheets
Replies
2
Views
269
BigKeyes13K
B
U
  • Question
Chart creation - best presentation?
Replies
2
Views
122
Fluff
Fluff

Forum statistics

Threads
1,203,600
Messages
6,056,204
Members
444,850
Latest member
dancasta7

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top