VBA Code Working Incorrectly, Loops

[Sherifa]

I have a list of values which I want to be part of my Header names.
The problems is the code cycles through all the names for each worksheet, rather than matching worksheet 3 to d8 and 4 to d9 etc.

Sub RenameRows()    Dim Wkb As Workbook
    Dim ws As Sheets
    
    Set Wkb = ThisWorkbook
    Set ws = ThisWorkbook.Worksheets




Dim i As Integer
Dim j As Integer
       
ws_count = Wkb.Worksheets.Count


For i = 3 To ws_count
For j = 8 To 55
            
            
        Wkb.Worksheets(i).Range("A1") = ws(i).Name & " " & Sheets("Cost of Cover").Cells(j, 4)
            
        
    Next
 
 Next
     
End Sub

 

[Fluff]

Untested, but try

Sub RenameRows()
    Dim Wkb As Workbook
    Dim i As Long
    Dim j As Long

    Set Wkb = ThisWorkbook

    j = 8
    For i = 3 To Wkb.Worksheets.Count
        Wkb.Worksheets(i).Range("A1") = ws(i).Name & " " & Sheets("Cost of Cover").Cells(j, 4)
        j = j + 1
    Next i
     
End Sub

 

[Sherifa]

The code doesn't work, any other suggestions. I defined ws but it still didn't work

 

[Sherifa]

I've just defined it. I think I didn't do it properly the first time.

Sub RenameRowstest()

    Dim Wkb As Workbook
    Dim ws As Sheets
    
    Set Wkb = ThisWorkbook
    Set ws = ThisWorkbook.Worksheets


    Dim i As Long
    Dim j As Long


    Set Wkb = ThisWorkbook


    j = 8
    For i = 3 To Wkb.Worksheets.Count
        Wkb.Worksheets(i).Range("A1") = ws(i).Name & " " & Sheets("Cost of Cover").Cells(j, 4)
        j = j + 1
    Next i
     
End Sub

How do you know when to use Long in VBA?

 

[Fluff]

How do you know when to use Long in VBA?

There's very little point in using Integer these days, as the compiler will convert them to long