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
Sheets("Number").Activate
Range("A1:A3").Select
Selection.Copy
Sheets(I).Select
Range("A1").Select
Selection.Paste
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
ADVERTISEMENT
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
[Code]....
View 3 Replies
View Related
Nov 20, 2009
=COUNTIF(A2&"_RES","Yes")
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
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....
SHEET_SC_CONFIG.Range(NamedRange)
(cannot reference by sheet name, as end user may change these)
View 6 Replies
View Related
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
Jul 7, 2013
my 1st sheet like this : A1="Reg.No."
B1="Name"
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
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
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
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
Code:
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
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.
Sheets("sheet1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Sheets("sheet2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
Sheets("sheet3").Select
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
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
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
ThisWorkbook.Activate
For Each ws In AtiveWorkbook
ws.Activate
code here
Next ws
View 4 Replies
View Related
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
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
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
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
View 2 Replies
View Related
Mar 22, 2012
I have a list of worksheets that I want to select from a list.
Region 1
Region 2
Region 3
etc....
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
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
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
Me.cboxCashFlow.SetFocus
MsgBox "Please select a Cash Flow."
Exit Sub
End If...............
View 9 Replies
View Related
May 2, 2006
I've put the following code together;
Sub RemoveStars()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Sheet2").Select
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Summary").Select
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
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
Sheets(x).Activate
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
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:
sht01.name="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
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
CodNameYearDescTotal1Total2ProvCnt
1234Apples200xxxyyyzzz
3412Bananas400xxxyyyzzz
2358Oranges500xxxyyyzzz
8956Pines800xxxyyyzzz
[Code]....
View 6 Replies
View Related
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
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
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
Range("A1").Select
ActiveCell.FormulaR1C1 = a
Sheets("3 ANLV").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = a
ActiveWorkbook.Save
ActiveWindow.Close
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
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.
_________________
TheWordExpert
[ This Message was edited by: Dreamboat on 2002-04-11 10:20 ]
View 9 Replies
View Related
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
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
Jan 30, 2014
In column C I got some data like this:
Number of .csv
01
02
03
04
Number of .csv
05
06
07
08
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
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
