Funktion for hvert worksheet i excel

Hej - her er mit bud.

Sub hjaelp()
Dim shtName As Variant, currentRow As Integer

currentRow = 10 'Start at row 10

Sheets(1).Select
AntalArk = Worksheets.Count - 1

For Tael = 1 To AntalArk
   
    ActiveSheet.Next.Select
    shtName = ActiveSheet.Name

    Sheets(1).Cells(currentRow, 1).Value = shtName
    Sheets(1).Cells(currentRow, 2).FormulaLocal = "='" & shtName & "'!B4"
    Sheets(1).Cells(currentRow, 3).FormulaLocal = "='" & shtName & "'!B6"
    Sheets(1).Cells(currentRow, 4).FormulaLocal = "='" & shtName & "'!B9"
   
    currentRow = currentRow + 1

Next Tael

Sheets(1).Select
Range("A10").Select

End Sub

Hov - det var jo et svar...

Sub hjaelp()
Dim shtName As Variant, currentRow As Integer

currentRow = 10 'Start at row 10

Sheets(1).Select
AntalArk = Worksheets.Count - 1

For Tael = 1 To AntalArk
   
    ActiveSheet.Next.Select
    shtName = ActiveSheet.Name

    Sheets(1).Cells(currentRow, 1).Value = shtName
    Sheets(1).Cells(currentRow, 2).FormulaLocal = "='" & shtName & "'!B4"
    Sheets(1).Cells(currentRow, 3).FormulaLocal = "='" & shtName & "'!B6"
    Sheets(1).Cells(currentRow, 4).FormulaLocal = "='" & shtName & "'!B9"
   
    currentRow = currentRow + 1

Next Tael

Sheets(1).Select
Range("A10").Select

End Sub

tak for det... det virker ;)

Der er helt sikkert en smartere måde at gøre på det end at løbe alle ark igennem for at finde navnet... men jeg er ikke nået dertil i mit selvstudium endnu...

Sådan - så slipper man for at løbe alle arkene igennem for at få navnet...
Tak for spørgsmålet - jeg kan godt selv komme til at bruge den fremover :-)

Sub LidtBedre

Dim currentRow As Integer

currentRow = 10 'Start at row 10

Sheets(1).Select
AntalArk = Worksheets.Count

For arkNr = 2 To AntalArk
   
    Sheets(1).Cells(currentRow, 1).Value = Sheets(arkNr).Name
    Sheets(1).Cells(currentRow, 2).FormulaLocal = "='" & Sheets(arkNr).Name & "'!B4"
    Sheets(1).Cells(currentRow, 3).FormulaLocal = "='" & Sheets(arkNr).Name & "'!B6"
    Sheets(1).Cells(currentRow, 4).FormulaLocal = "='" & Sheets(arkNr).Name & "'!B9"
   
    currentRow = currentRow + 1

Next arkNr

Range("A10").Select

End Sub