Active Sheet name as variable and looping to next sheet

[ilikered100]

Ok... The code works fine on one sheet, but I can't get it to move to the next worksheet and continue carrying out the code.

What the code does is start on a worksheet after a known worksheet name (EvaluationData). On all sheets after EvaluationData, the code loops down through cells starting on AF10 and deletes data that is NOT LIKE the sheet name.

This works fine on the first sheet after DataEvaluation, but I can't get it to move to the next sheet. Also when I can get it to go to the next worksheet, will the variable change to that new sheet name?... which I need it to do. I need the variable (strActiveSheetName) to change to the active worksheet name and then use that sheet name... etc.

I hope this makes sense.

Any help is appreciated.

Carolyn

-----------------

Sub Details_5()


Dim intFirstws As Integer
Dim strActiveSheetName As String
Dim i As Integer


intFirstws = Worksheets("EvaluationData").Index + 1
strActiveSheetName = ActiveSheet.Name


Worksheets(intFirstws).Select
Range("AF10").Select

'Range of sheets from the sheet after DataEvaluation to the end of all the sheets in the workbook
For i = intFirstws To Sheets.Count


Do Until ActiveCell.Value = ""


'If data is LIKE the sheet name then do nothing and move down one cell
If ActiveCell.Value Like strActiveSheetName & "*" Then
ActiveCell.Offset(1, 0).Select

'If data is NOT LIKE sheet name then delete that cell and the two cells to the left and shift other cells up
Else: Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -2)).Select
Selection.delete Shift:=xlUp

'Move back the the last cell active cell to continue evaluation of cells for LIKE or NOT LIKE sheet name
ActiveCell.Offset(0, 2).Select


End If


Loop


'Move onto next worksheet
Next i


End Sub

 

[Joe4]

Here is a loop that shows you how you can loop through those sheets you want:

Dim strActiveSheetName As String
Dim i As Integer


For i = Worksheets("EvaluationData").Index + 1 To Worksheets.Count
    Sheets(i).Activate
    strActiveSheetName = ActiveSheet.Name
    'YOUR CODE HERE
Next i

 

[ilikered100]

Thank you. It does loop the the next sheet now, but the variable strActiveSheetName doesn't change to the new sheet name. Each time it moves to the next sheet I need that variable to store and use the new sheet name that it is now on.

Thank you for your help with the first part.

If you know of way to accomplish what I need... much appreciated.

Carolyn

 

[Joe4]

Did you look at the next two lines after the For line? It does precisely that very thing.
To prove it, run this little macro:

Sub MyTest()

Dim strActiveSheetName As String
Dim i As Integer

For i = Worksheets("EvaluationData").Index + 1 To Worksheets.Count
    Sheets(i).Activate
    strActiveSheetName = ActiveSheet.Name
    MsgBox "Sheet index: " & i & vbCrLf & "Sheet name: " & strActiveSheetName
Next i

End Sub

This will loop through all the sheets after EvaluationData and return their Index number and sheet name (via the variable) in a Message Box.

 

[ilikered100]

OK... got it. Thank you.

It works now.

Carolyn