Loop Through Index Worksheets Using For Loop And Select Clause

Nov 4, 2013

I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.

I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.

Sub WorksheetLoop()
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet


View 2 Replies


Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Loop Through Worksheets Not Working (delete Some Hyperlinks In Column A On 50+ Worksheets)

Jan 16, 2009

Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?

View 2 Replies View Related

Loop Through Range By Color Index

Jun 16, 2007

I have a bit of VBA code that loops through a range and looks at the color index. If it is color index three then it will put a "1" in the cell six columns over. This code works, but I see over and over again that loops are bad and inefficient. Since I am working on my code being more efficient I wanted suggestions for altrenate code that would do basically the same thing.

Dim Bcell As Range
For Each Bcell In Range("D2:D304")
If Bcell.Interior.ColorIndex = 3 Then
Bcell.Offset(0, 6) = "1"
Bcell.Offset(0, 6).ClearContents
End If
Next Bcell

View 4 Replies View Related

Loop Across Worksheets: Perform The Same Process To All The Worksheets In My Workbook

Aug 10, 2009

I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:

View 2 Replies View Related

Loop Select Case

Sep 24, 2009

How do I loop a select case so that it ends at a specific cell.

View 14 Replies View Related

Select Sheets In Loop

Mar 22, 2012

I have a list of worksheets that I want to select from a list.

Region 1
Region 2
Region 3

These sheets are in a workbook that contains other non Region sheets.

How do I select all of them without hardcoding the sheet names?

View 5 Replies View Related

Loop To Select And Delete A Row

Jun 21, 2012

Below is the loop. An error occurs at Rows("y:y").Select

The y value that would be found in cell C500 would always match the row that would be selected and deleted. For example if cell C500 had the value 13, I would want Rows("13:13").select and then deleted.

Sub Macro2()
' Macro2 Macro
For y = 1 To 100


View 5 Replies View Related

Select Case And Loop?

Aug 9, 2013

I have a subroutine involving a Select Case and a loop.

I am of the opinion that writing the loop within the Select Case would be faster than having the Select Case within the loop.

Am I correct or does it not make a difference?

View 3 Replies View Related

Using Select Case In A Loop?

Sep 18, 2013

I am trying to loop through and read values from 4 combo boxes which I have renamed SaveOptionBox1 to 4. They are ActiveX controls. The code throws no errors but I can't output the values of my array 'dirname' on the sheet to prove it worked.

Sub Directory()
Dim ROOT_PATH As String
Dim cnt, dirnumber As Integer


View 3 Replies View Related

Loop To Select Different Sheets

May 2, 2006

I've put the following code together;

Sub RemoveStars()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

There are more sheets that this is run on, but I am sure you get the idea. I guess that this can be done using a loop, I would prefer to code the sheet names in, there are 5 in total.

View 6 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long


View 4 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Loop Through Cells And Ranges Reverse Order With Backwards Loop

Aug 30, 2006

I am looping through each cell in a range and I would like to loop in reverse order.

Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
'Code here to delete a row based on criteria

I have tried:

For Each CELL In Range("C" & TotalRows, "C1")

and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.

View 7 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

How Do I Loop Through Worksheets

Apr 8, 2008

I have a number of sheets in my workbook which I'd like to run the same code against. Rather than calling each by name is there a way to define each sheet as an array number and use that to loop through?

For this example we'll use Sheet1, Sheet2, Sheet3 and Sheet4

View 9 Replies View Related

Loop Through Worksheets

Dec 22, 2006

I have a simple macro with a loop which selects sheets in a workbook. sheets are named "Sheet 1" to Sheet 6". Two sheets are missing (say sheet 3 and sheet 5). The On Error code works when the macro tries to find sheet 3 but crashes on the second error (when it tries to find sheet 5).

Sub SelectSheet()
For i = 1 To 6
MySheet = "Sheet" & i
On Error Goto 10
Next i
End Sub

View 3 Replies View Related

Select Columns To Merge Within For Loop

Nov 18, 2011

My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.

Example of what I have so far:

Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub

View 1 Replies View Related

Case Select And Loop Procedure

Jun 10, 2007

I am learning about Case Select-
I want to loop through every cell in col. V the range of
i = 6 to LRow
and find any value that equals 13, if it equals 13 then the value in (i,"W") will be "True"

View 9 Replies View Related

Loop Code Through Certain Worksheets?

Nov 15, 2013

I am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?

The 15 worksheets are:


View 3 Replies View Related

Loop Through The Worksheets In WorkbookA

Oct 14, 2008

Iím trying to copy some data from each sheet in WorkbookA, except for the first sheet which is called ďMenuĒ, into a single sheet in WorkbookB. Iím trying to loop through the worksheets in WorkbookA but donít know how to exclude the first sheet. The code for copying and pasting works fine. Itís just the looping (as always) Iím having trouble with. The code Iím trying is:

View 2 Replies View Related

Loop Thru Worksheets Not Looping?

Aug 12, 2009

Why wont this loop thru all worksheets? It seems it runs worksheet named "Monday Wk (1)" for each sheet and no others.

View 9 Replies View Related

Loop Through Cells And Then Through All Worksheets

Jan 30, 2010

Not sure what is missing here, but this will only highlight duplicates on the active sheet and won't cycle thru all worksheets in the workbook.

View 2 Replies View Related

VBA To Loop Through Multiple Worksheets

May 10, 2013

Code to complete the same task across a number of worksheets.

Basically I have 20 Worksheets currently. The first one is called "index", then I have 17 called App1, App2, App3 etc up to App17 and a final two called Collate and register.

What I want to do is to copy cells A2:E2 from App1 and paste it in the next blank row of "index". I then want to do the same in App2 and so on to App17 and then stop. I don't want it to do the same in index,Collate or Register.

in the past i have used something like (this is from something else I am using at the moment)

HTML Code:

Application.CutCopyMode = False

[Code] ....

Is there a way without having to write code for each sheet (which seems very inefficient) to complete the same task but ignore the three other tabs.

View 4 Replies View Related

Loop Through All Worksheets - Failing?

May 14, 2013

This macro should copy all data from all worksheets and past them into the sheet named "Consol" however It is not looping and only pastes the one sheet.

Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
FinalRow = Range("A65536").End(xlUp).Row
Range("A2:U" & FinalRow).Copy


View 6 Replies View Related

Loop Through Multiple Worksheets?

Jan 17, 2014

I have over 200 worksheets in my workbook. I made a macro to have the sheet change to landscape orientation and adjust the column widths how I wanted them. I don't know how to make the macro loop to all the sheets in the workbook.

Sub Macro5()
' Macro5 Macro


View 3 Replies View Related

Cut/Paste Loop Between Worksheets

Apr 13, 2009

I have a worksheet ("Issues Report"). Based on the value in column A, I'm trying to cut the entire row and paste it on another worksheet ("Closed Issues").

Here's what I've written so far:

Dim C As Range
Dim xlSheet As Worksheet
Set xlSheet = Worksheets("Issues Report")
Set C = xlSheet.Range("A:A")

With xlSheet
For Each cell In C
If cell.Value = "Ready to Close" Then
Worksheets("Closed Issues").Range("A65536").Select
End If
Next cell

This seems logical to me, but it's not working as planned. The code gets hung up on the 11th line of code.

View 9 Replies View Related

Referencing Worksheets In A Loop

May 2, 2006

Trying to write a macro that will reference one cell in about sixteen different worksheets and return the value of each of those cells. Is there an easy way to do this?


For n = 1 To n = 15

Worksheet(n + 1).Cell("A1")

Return A1

I know this isn't even close to the right code but this should give you an idea of what I'm trying to do.

View 9 Replies View Related

Loop Through Worksheets & Sort

Jul 22, 2006

I want to loop through all worksheets and sort all columns in each worksheet. Here is what I have, but for some reasson, it only sort the first sheet. Any suggestions?

Sub test2()

Dim ws As Worksheet
For Each ws In Worksheets

Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub

View 4 Replies View Related

Declare And Set Worksheets In A Loop

Jan 31, 2007

I want to declare and 'Set' a number of worksheets for later use. Like this ...

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved