Error when creating a new tab in an excel macro loop

[sparko92]

I'm trying to create a macro loop that includes a new tab being created for each line of data in the table.

Recording the macro formula, the vba records "sheet 1" before the renaming of the and so the macro fails at the second line of the table because it is looking for sheet 1 again.

Any help greatly appreciated, vba code below:

Sub IRRPayback()
'
' IRRPayback Macro
'

Dim Table As Range

Set Table = Range("A2", Range("A2").End(xlToRight).End(xlDown))

For Each Row In Table.Rows

Next Row

End Sub

Function IRRPaybacks()

'
    Sheets("New Data").Select
     Range("A2").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "19375B"
    Sheets("IRR Format").Select
    Range("B2:M11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("19375B").Select
    Range("B2").Select
    ActiveSheet.Paste
    Sheets("New Data").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("19375B").Select
    Range("B2:L2").Select
    ActiveSheet.Paste
    Range("H5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='New Data'!R[-3]C[5]"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "='New Data'!R[-4]C[11]"
    Range("D6").Select
    Selection.AutoFill Destination:=Range("D6:D8"), Type:=xlFillDefault
    Range("D6:D8").Select
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "='New Data'!R[-4]C[9]"
    Range("E7").Select
    Sheets("New Data").Select
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "='19375B'!R[8]C[-8]"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "='19375B'!R[8]C[-8]"
    Range("R2").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("S2").Select
    Selection.NumberFormat = "0.000000000"
    Selection.NumberFormat = "0.00000000"
    Selection.NumberFormat = "0.0000000"
    Selection.NumberFormat = "0.000000"
    Selection.NumberFormat = "0.00000"
    Selection.NumberFormat = "0.0000"
    Selection.NumberFormat = "0.000"
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
End Function

 

[Joe4]

Welcome to the Board!

When you insert a new sheet, it becomes the ActiveSheet.
So you should simply be able to replace this:

    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "19375B"

without something like this:

    ActiveSheet.Name = "19375B"

(though you cannot name every sheet the same thing, so I assuming this was just a test, and you have a method for naming them different names).

 

[sparko92]

Welcome to the Board!

When you insert a new sheet, it becomes the ActiveSheet.
So you should simply be able to replace this:

    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "19375B"

without something like this:

    ActiveSheet.Name = "19375B"

(though you cannot name every sheet the same thing, so I assuming this was just a test, and you have a method for naming them different names).

Thank you for your help. The name comes from the selection copy above that, so I am copying cell A" and then pasting that as the name of the first new sheet. My intention is that on the next loop B2 becomes the name of the next new sheet. Would your suggested solution still work?