How to assign value to rows according to sheet order in VBA

Question

since I'm new to VBA and i got issue that the order of sheet name is not in place as expected. say: In the workbook i have sheet1 with cell A7= name of sheet1 A8= name of sheet2, A9= name of sheet3. Beside the sheet1, 2, 3 i also have other sheets on the workbook

i write the loop to fill the value of sheet name to the sheet 1 at B7,B8,B9 from sheet().name function, the thing is if i change the order of sheets by moving them, so the order of cell A1, A2, A2 is not matching to the sheet name anymore

Please help to fix my code to make the order of sheet name in right place accordingly

Private Sub Worksheet_Activate()

Dim lrow As Long
Dim i, j As Integer
Dim sh As Worksheet
Dim result As String
Dim shName As String
Dim sName As Long

Sheets("sheet1").Range("B7:B9").Clear
For i = 1 To Sheets.Count
    With ThisWorkbook
       Set sh = .Sheets(i)
       shName = .Sheets(i).Name
  '     MsgBox .Sheets(i).Name
    End With
    With sh
        lrow = .Range("R" & .Rows.Count).End(xlUp).row
    End With
    Select Case shName
        Case shName

          '7 is the starting row at sheet 1 
          For j = 7 To lrow
            If Sheets(shName).Cells(j, 1).value = shName(j) Then
                worksheet.sheet("sheet1")cells(j,2).value ="something here"
           endif

        Next j
    End Select
End Sub

joehanna joehanna · Accepted Answer · 2015-08-25T07:34:59

You need to use the Find function to locate the worksheet name your range on worksheet1 and then you can put the row count next to it.

Try this:-

Private Sub Worksheet_Activate()

  Dim lrow As Long
  Dim i, j As Integer
  Dim sh As Worksheet
  Dim result As String
  Dim shName As String
  Dim sName As Long
  Dim rng As Range

  Sheets("sheet1").Range("B7:B9").Clear

  For i = 1 To Sheets.Count
      With ThisWorkbook
         Set sh = .Sheets(i)
         shName = .Sheets(i).Name
    '     MsgBox .Sheets(i).Name
      End With
      With sh
          lrow = .Range("R" & .Rows.Count).End(xlUp).Row
      End With

      Set rng = Range("A7:A9").Find(shName)

      'Only write something out if the sheet name is listed
      If Not rng Is Nothing Then
        rng.Offset(0, 1).Value = lrow
      End If
  Next

End Sub

How to assign value to rows according to sheet order in VBA

1 Answers