VBA looping excel sheets

Question

I am actually a vba beginner and created my first code, but I need do it more flexible ... I would like to select more excel sheets, when I use the makro. Actually I use only the sheet called "sector_share_se", but how could I run the same makro for selected sheets e.g. "sheet_1", "sheet_5" and other? Furthermore is there a way to count the filled rows in a cloumn? Then I could create a flexible range instead of Range("A400") and Range("A4:A400").

What did i do here?

I want to set the yearly data on a monthly scala. I create 11 empty rows between the filled rows and copy the monthly scale from the sheet called "MSCI". The last step is to fill the empty cells with NA's (in a way 11 NA's for each row with a value)

   Sub Year_to_Month()
    ' emtpy space in selected sheet
    Sheets("sector_share_se").Select
    Dim i As Long
    i = sector_share_se
        For i = Range("A400").End(xlUp).Row To 5 Step -1
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).EntireRow.Insert
        Next i

    ' Create time frequency
           Sheets("MSCI").Range("A4:A400").Copy
           Sheets("sector_share_se").Range("A4:A400").PasteSpecial

    ' replace empty with NA
        Range("A4:AI310").Select
        Selection.Replace What:="", Replacement:="NA", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub

Thanks in advance René

Edit with Information of @MatthewD:

' I understand iIndex as number of the excel sheet and it is possible to 
' adress the correct sheet with it but how can I use here more sheets that I
' can enter only the Number (or Name) of the sheet e.g. iIndex = 1,2,3,7,9,15 


Dim lRow As long
Dim iIndex As long
For iIndex = 1 To ActiveWorkbook.Worksheets.count
    Set ws = Worksheets(iIndex)
    For ws = Range("A400").End(xlUp).Row To 5 Step -1
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
        Cells(i, 1).EntireRow.Insert
    Next i

' I hope that I set the maximum lenght with "ws.UsedRange.Rows.count" 
' and copy it row by row
   For lRow = 1 To ws.UsedRange.Rows.count
     Sheets("MSCI").Range("1:lRow").Copy
     Sheets("banking_sector").Range("1:lRow").PasteSpecial
   Next lRow

' I think that this is quiet okay (I recorded it only), it should 
' select the hole data and replace the NA's
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="", Replacement:="NA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Answer 1

You could record a macro then just repeat the flow. press f8 to run it line by line.. You could understand it better if you'll do that.

Answer 2

Here is a sub that will loop sheets and make one active by name.

Dim ws As Excel.Worksheet
Private Sub loopsheets(strSheetName As String)
    iFoundWorksheet = 0
    For iIndex = 1 To ActiveWorkbook.Worksheets.count
        Set ws = Worksheets(iIndex)
        If UCase(ws.Name) = UCase(strSheetName) Then
            iFoundWorksheet = iIndex
            Exit For
        End If
    Next iIndex
    If iFoundWorksheet = 0 Then
        MsgBox "No worksheet was found with the name RESULTS (this is not case sensetive). Aborting."
    End If
    Set ws = ea.Worksheets(iFoundWorksheet)
    ws.Activate

End Sub

Or just loop through the sheets and do something.

For iIndex = 1 To ActiveWorkbook.Worksheets.count

    Set ws = Worksheets(iIndex)
    'Do something here.

    'Or call you sub
    Year_to_Month

Next iIndex

And usedRange is a better way to loop through the contents of a sheet as opposed ot using Range("A400") and Range("A4:A400").

 'Loop through the columns.
 Dim lRow as long
 For lRow = 1 To ws.UsedRange.Rows.count
     'Do something here.
 Next lRow

To check if something is filled you would do something like this.

If  ws.cells(RowIndex, ColIndex).Interior.ColorIndex > 0 then
     'We have a filled cell
End if

You can address sheets by name or index

Set ws = Worksheets(3)

or

Set ws = ActiveWorkbook.Sheets("Sheet1")