Help in VBA

[Meko1]

Function Filling_Summary_Sheet()

  Sheet1.Range("B4") = ActiveSheet.Range("C6")
  Sheet1.Range("C4") = ActiveSheet.Range("B7")
  Sheet1.Range("D4") = ActiveSheet.Range("B12")
  Sheet1.Range("E4") = ActiveSheet.Range("F7")
  Sheet1.Range("F4") = ActiveSheet.Range("I30")
End Function

This is my code and I am trying to add the following:
Imagine my ActiveSheet is called "paid1"; After running it, I will move on the next sheet in excel which is called "paid2" and want to run same macro, just Cell Data now instead of B4, C4, D4, E4, F4 to go in B5, C5,D5, E5 F5. And this to happen for 500Activesheets or from "paid1" to "paid500".

if the provided info is not enough let me know

Thanks in advance.

 

[MARK858]

Do your sheets go in order from left to right starting at the leftmost sheet?

 

[Meko1]

ActiveSheets start from sheet6. and goes from left to right in order

 

[MARK858]

And is Sheet6 the 6th sheet from the left?

 

[Meko1]

it is like this.
Data needs to go from "Basic infos" into summary

 

[MARK858]

Please answer the question, I am not coding based a partial image of your workbook.

 

[Meko1]

Yes 6th sheet is 6th from the left

 

[MARK858]

Try the code below (I have only made it a Sub rather than a function as it is easier to test, don't know why you had it as a function to start with though as you aren't feeding it any parameters).

Sub Filling_Summary_Sheet()
    Dim i As Long
    Application.ScreenUpdating = False

    For i = 6 To 505
        With Sheets(i)
            Sheet1.Cells(.Index - 2, "B") = .Range("C6")
            Sheet1.Cells(.Index - 2, "C") = .Range("B7")
            Sheet1.Cells(.Index - 2, "D") = .Range("B12")
            Sheet1.Cells(.Index - 2, "E") = .Range("F7")
            Sheet1.Cells(.Index - 2, "F") = .Range("I30")
        End With
    Next
    
    Application.ScreenUpdating = True
End Sub