Refer To Sheets Created In Loop

Oct 16, 2007

I need to creat a bunch of sheets and do the same work for each of the sheets. My problem is that I do not know how to refer them by the created name. Here is an example of my codes:

Sub test()
Dim I As Integer

For I = 1998 To 2010
Sheets.Add.Name = I

Next I

End Sub

The error is "Subscript out of Range". I believe it is because when I refer a sheets(I), (I) does not recognize as the name of the sheet but the number of that sheet, and there is no sheet numbered 1998 or bigger. However, If I refer the sheet as sheets("I"), it can not find the sheet named "I" either.

How should I refer those sheets name so that I can do some work?

View 7 Replies


Refer To Workbook With Name That Is Created By Macro?

Sep 28, 2011

I have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.

What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.

the code below.

HTML Code:
Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String


View 3 Replies View Related

New Lines To Refer To Different Sheets

Nov 20, 2009


A2 refers to a linked cell but I have tried without the link there still no joy. Together they would generate for example "DCA_RES" which would refer to a column range in a different sheet on the same workbook.

I want this to work so that I can implement more easily new lines to refer to different sheets. (Drag to copy) instead of having to copy and edit a formula 100 times.

View 6 Replies View Related

Sheet Indirection. Refer To Sheets With Changing Names

Feb 25, 2008

I've been referencing sheets in my code directly with things like

Sheet1. Range(NamedRange)

however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to

#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2

'and then use these throughout the code....

(cannot reference by sheet name, as end user may change these)

View 6 Replies View Related

Excel 2010 :: How Many Sheets Can Be Created

Jul 26, 2013

How many sheets can be created in Excel 2010? Is it depend on memory PC or is any limit within Excel?

View 2 Replies View Related

Hyperlink To Specific Sheets Which Created Automatically

Jul 7, 2013

my 1st sheet like this : A1="Reg.No."


i want -when type a No.&Name in A2 & B2 to inserted a new sheet (sheet2)which is it's name is that No.&Name and also a link between the cell and sheet...

View 1 Replies View Related

New Sheets Created With Template And Populated With Data When Conditions Are Met

Mar 24, 2014

I have a workbook with three sheets: Collections, Client and Interest New sheets created from a range in Sheets("Interest"). The range in Sheets("Interest") holds the client names.

Then the template in Sheets("Client") is copied and pasted into the new sheets

For each new sheets cell B6 holds the client name and .Range("A10:A1317") holds the dates
Sheets("Collections").Range("D10:D1317") holds the dates and .Range("D8:WC8") holds the Client Names
if Sheets("Collections").Range("D8:WC8") = new sheet.cells(6, 4) then
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if

move down on row and repeat the steps.

View 8 Replies View Related

Assign Code To Toolbar Created In VB To Hide Sheets And Then Rehide

Jan 22, 2009

I have code which creates a tool bar which works Ok, however I can't get the statement correct to assign code to it.

View 8 Replies View Related

Loop Through Sheets 2-4 Only?

Aug 2, 2012

How do I tell a loop command to stop after a certain number of sheets? The code below works for what I want to do but it saves all the sheets in my workbook bar the first one. I just want to save sheets 2, 3 & 4

Option Explicit
Sub mysaver()
Application.Calculation = xlManual
Dim counter As Integer
counter = 2
' counter is for the number of sheets in the workbook
Do While counter

View 1 Replies View Related

For Each Loop- How Can I Specify Which Sheets To Do This For And Then Loop Thru All Sheets To Do It

Apr 5, 2007

I think I need a For Each Loop, but I'm not sure. I want to collapse all grouping to the highest level for certain sheets in a book.

ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1

How can I specify which sheets to do this for and then loop thru all sheets to do it?

View 9 Replies View Related

How Do I Loop Through Sheets

Mar 14, 2008

I want to perform an action on all but one of my worksheets. I've heard the array function can be used for this but I'm unfamiliar with its use.

View 9 Replies View Related

For Loop Through All Sheets

Apr 24, 2007

why this code does not work on all the sheets in the active workbook? Actually it just work on the current active worksheet.

Dim ws As Worksheet
For Each ws In AtiveWorkbook

code here

Next ws

View 4 Replies View Related

Loop Through All Sheets, PasteValues

Oct 28, 2008

the code for looping through ALL the sheets in a workbook, copying ALL the cells and pasting the values?

View 5 Replies View Related

Loop Sheets In More Than One Workbook

Jan 28, 2009

I have a small macro that searches the sheets in a workbook and sends the info (if qualifies) to a new workbook before saving that workbook using a name date time format for records.

I woud like this macro to be able to repeat action in 8 more selected workbooks in a folder.
Question - can I name the workbooks I want to search - and - can I search all 9 workbooks before the data sheet saves and names itself, limiting access.

View 13 Replies View Related

Loop Through All Sheets And Execute

Nov 19, 2011

I have this very simple code below that I use to delete a row if its marked as 'false' in column M. This works quite well, but I want to expand it. I use this in a workbook that can have name different sheet names in a month, and I want it to automatically go through all the sheets and do this...except for 2 sheets named addressess and sheet1. Is there something I can add to this macro that will loop through all the other sheet names (regardless of name) and execute this?

Sub DelRow()
With ActiveSheet
.AutoFilterMode = False
With Range("m1", Range("m" & Rows.Count).End(xlUp))
.AutoFilter 1, "false"
On Error Resume Next
End With
.AutoFilterMode = False
End With
End Sub

View 2 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 Through Some Sheets In Workbook

Feb 27, 2013

Is it possible that a VBA code could loop through some sheets in a workbook and save each one as an individual CSV file. The CSV filename would be the same as the sheet name.

View 2 Replies View Related

VBA: Loop Through Sheets And Run The Same Code

Apr 5, 2007

I am trying get a set of code to run through the sheets in the workbook... All sheets EXCEPT 1 named "Summary".

How can I code the proper statement? This is my current
Private Sub cmdAddDistribution_Click()

Dim ws As Worksheet
Dim lCount As Long
Dim rFoundCell As Range

'check for selected cash flow
If Trim(Me.cboxCashFlow.Value) = "" Then
MsgBox "Please select a Cash Flow."
Exit Sub
End If...............

View 9 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 All Columns In All Sheets

Nov 28, 2006

I am trying to loop all columns in each of a number of sheets using current region.

It selects the current region OK but the column counter only shows 1 as the number of columns in any sheet.

The Cells(6,1) likely has something to do with it but I do not know any other way to point to the current region I need. I do not know in advance how many rows or columns I am starting with but each has the required Row 6.

For x = 1 To Sheets.Count

Cells(6, 1).CurrentRegion.Select
Selection.CurrentRegion.Name = "Mydata"

'Loop all columns in sheet

For y = 1 To Range("MyData").End(xlToRight).Column
Cells(5, y).Select

Next y

Next x

View 6 Replies View Related

Rename Sheets In For Next Loop

Jun 18, 2008

I have a WorkBook with many Sheets "imagine Sheets.count=50"

9 of the CodeNames for this Workbook's sheets are: sht01. sht02, sht03, sht04, sht05, sht06...

If I want to change the name "not the CodeName" of say "sht01" I can use:"New Name"

but is there a way of doing this whitin a For Next this way:

For X = 1 To 9
Sheets("sht" & X.CodeName) = X ' the Name X is just for this eg
Next X

View 9 Replies View Related

Loop To Write Sheets Name In Column

Apr 23, 2014

I have a workbook with 180 Sheest. I need to copy sheet name and paste to column Name. In the Column Year write 2013.Finally I need to all sheets as show below in in Sheet Farms. Doing this one by one is time consuming and with error risk. I think a loop can do this,nevertheless, I don't Know how to do it. Column size can be different in each Sheet

Sheet name Famr1


View 6 Replies View Related

Loop A Macro For Specific Sheets

Aug 21, 2014

How do I build a "For specific worksheets" loop?

I have a macro that works for a single sheet but I want it to loop over several. Currently, my code looks like this:

[Code] ....

View 6 Replies View Related

Loop Through The Workbook Missing Out Specified Sheets

Dec 21, 2009

However it will only add data to the active sheet when i am asking it to loop through the workbook missing out specified sheets. Would anyone be able to look over the code to see where the error is as to why it will not loop through the remaining sheets in the work book.

View 5 Replies View Related

Loop: Changes A Cell Value On 2 Sheets In A Workbook

May 29, 2008

I am trying to loop a procedure that changes a cell value on 2 sheets in a workbook. I recorded a macro on one workbook and it worked fine. I then tried to modify the macro to loop this on more workbooks that have identical worksheet names. The macro is in a workbook named LIST, which column A has a list of all the workbook names. Currently there are 55 workbooks, but in the future I am sure there will be a few more. Here is a copy of the macro:

Sub Macro1()
' Macro1 Macro
' Macro recorded 5/28/2008 by MT
Dim STATEstr As String
Dim a As Long
a = Range("C1")
For STATEstr = A1 To A55
Workbooks.Open Filename:="C:ALLSTATES" & STATEstr & ".XLS"
Sheets("3 ANL").Select
ActiveCell.FormulaR1C1 = a
Sheets("3 ANLV").Select
ActiveCell.FormulaR1C1 = a
Next STATEstr
End Sub
The first error I got was a TYPE MISMATCH on 'For STATEstr = A1 To A55'.
There may be more things wrong with this looping. The only experience I have with macros is recording them and then modifying and combining them.

View 9 Replies View Related

Loop Through Data To Copy To Other Sheets

Apr 11, 2002

Input Workseet:

Col A: Date
Col B through M: Headings are employee names, data is how many hours of vacation per DATE.

User will enter a date in column A, and then the corresponding number of vacation hours a person took that day. There are a dozen or so employees, so we're only entering a record on the dates that someone has taken vacation time. Dates are mm/dd/yyyy format. The hours are number/two decimals.

What I would like:

An update command button (hey, I can actually do that part!) that has an on-click that:

Loops through each column B through M, and copies the information to the employee's individual sheet.

The individual sheets:

Columns are:
A = Date of vacation
B through M are months Jan through Dec.

Data starts *paste* in cell A12, where the date of the first vacation day they take should appear. If it was a half-day in February, .5 (or .50) will appear in cell A14.

If it's not clear, I'm happy to send the file! If you put your email here, I'll send it right away. If you email me at home, it'll be a few hours before I can send.

No rush on my part.

Really appreciate it!! I'm not a coder. I know small bits and pieces. When you start talking about Dim, I think of chinese food.


[ This Message was edited by: Dreamboat on 2002-04-11 10:20 ]

View 9 Replies View Related

Continuous Loop Of Sheets In Various Open Workbooks?

Mar 22, 2013

How do I continuously loop various sheets in different workbooks pausing then for 5 seconds on each sheet (not all sheets in each workbook).

View 2 Replies View Related

Loop Through Sheets To Gather Info On First Sheet

Apr 4, 2014

I've started building a macro that loops the sheets and collects the information onto the first worksheet. I've been using Activesheet and activecell references but i'm afraid looping will change these references.

[Code] .....

View 1 Replies View Related

Rename Sheets By Using Dynamic Loop For Rows?

Jan 30, 2014

In column C I got some data like this:

Number of .csv

Number of .csv

Notice that there is an empty cell in between.The data starts at C12 up to C21. The data is in Sheet2.There are also 8 more sheets( Sheet3 to Sheet10). I want to rename each sheet, starting from Sheet3 according to each cell. For example the Sheet3 to be renamed to 01, Sheet4 to 02.

What I can do is something like this:

[Code] .....

And repeat this code for every block of data I got by changing everytime the i and the a. But this method is not so optimized because there are cases that the number of rows for each block is not the same and I have to change everytime the i counter. Is there any way to do 1 loop for all the sheets using maybe Worksheets.Count and another dynamic loop for the rows ? The data always start every 6 rows eg( C12, C18 etc). Also I was thinking to define an integer representing the number of rows for the loop...

View 6 Replies View Related

Writing A Loop To Parse Multiple Sheets.

Sep 19, 2007

writing a loop that will perform the same action on 11 worksheets but stop if it gets to a sheet with no data in cell A1. The data in A1 will be text. The code I wrote below below does what I want it to, but I have to run it on each individual sheet.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved