×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Incrementing called worksheet numbers

Incrementing called worksheet numbers

Incrementing called worksheet numbers

(OP)
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.

RE: Incrementing called worksheet numbers

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

RE: Incrementing called worksheet numbers

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.

RE: Incrementing called worksheet numbers

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")
.... 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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources