Incrementing called worksheet numbers 1

[Morglisn]

Hey, guys.

I have a fairly large workbook, consisting of about two hundred individual worksheets, with the first being a summation of data calculated in each of the others. The remaining spreadsheets are labelled by part numbers, simplified to three digits, 218, 219, etc. They are in sequential order.

I'm attempting to bring the data from each worksheet into the appropriate columns on Sheet1. My formulas look something like this : ='218'!$AA$38, with the next row being : ='219'!$AA$38.

Is there any way to copy this formula down my rows, while incrementing to sheet #s? My fingers are getting quite tired.

Thanks in advance for any help.

 

[asmenut]

try this code for sorting the sheets. Then add another sub to copy the cells. (Depending on the version of excel, you can code in a Refedit control that allows you to choose the range.

Sub SortSheets()
'Application.ScreenUpdating = False

'This routine Sorts the sheets of the
'Active WorkBook in Ascending Order.
Dim SheetNames() As String
Dim SheetHidden() As Boolean
Dim i As Integer
Dim SheetCount As Integer
Dim VisibleWins As Integer
Dim Item As Object
Dim OldActive As Object

'No Active Workbook
If ActiveWorkbook Is Nothing Then Exit Sub

'Check to see if the Active WorkBook is Protected
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is Protected. Please remove protection and try again. ", _
vbCritical, "Cannot sort Sheets."
Exit Sub
End If

'Disable the Ctrl+Break
Application.EnableCancelKey = xlDisabled

'Get the Number of Sheets
SheetCount = ActiveWorkbook.Sheets.Count

'Redim the Arrays
ReDim SheetNames(1 To SheetCount)
ReDim SheetHidden(1 To SheetCount)

'Store a reference to the Active Sheet
Set OldActive = ActiveSheet

'Fill the array with Sheet Names
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i

'Fill the array with the HIDDEN status of sheets
For i = 1 To SheetCount
SheetHidden(i) = Not ActiveWorkbook.Sheets(i).Visible
' Unhide hidden sheets
If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = True
Next i

'Sort the Array in ascending order.
Call BubbleSort(SheetNames)

'Move the Sheets.
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:=ActiveWorkbook.Sheets(i)
Next i

'Rehide the Sheets
For i = 1 To SheetCount
If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = False
Next i

'Reactivate the original active sheet
OldActive.Activate

'Application.ScreenUpdating = True

End Sub

Sub BubbleSort(List() As String)
' Sorts the List array in Ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub

 

[bpeirson]

Try this.
Fill a series across the top of your worksheet to represent the sheet names (1,2,3,4...250).

In cell B1 type in the formula [=CONCATENATE("+'",A1,"'$AA$38)]. Leave out the square brackets.
Drag this formula across row B below your sheet numbers in row A.

Select row B, copy, then paste special 'values' into row C. You should have the appearance that row B and C are the same but row C should have the CONCATENATE statement stripped out.

Last step. Select row C and use the Find/Replace function to change all + to = in one step.

If your version of excel works like mine then the values from the individual sheets will replace the text in row C.

You can delete rows A and B at this point if you like.

 

[bltseattle]

Here is a very simple way, no VBA required:

Say the part number/sheet name is in column A. Use the INDIRECT function with text operations as follows:

A B
1 Name of part Value
2 219 =INDIRECT("'"&A2&"'!$AA$38&quot
.... etc fill down

Note that the & symbol concatenates text, and pay attention to the single ' symbols in the equation. The first ' symbol is between double "". The second is in front of the !.

If you are unfamiliar, the INDIRECT function converts a text string to a cell/range address.

Good luck!