Create new sheet, name it, and add a formula with for loop?

[pythonruby]

Hi, I keep getting errors with this vba code. I'm trying to make 10 new worksheets with the name of each worksheet in the Newname array. I'm trying to put the same formula (with the difference that each formula would take from a different "Newname" file. I'm trying to put firstformula from A1 to A 560 and secondformula from B1 to B 560.

Sub Namesheetformula()
For x = 1 to 10
Newname = Choose(x,"A","B","c","d","e","f","g","h","i","j")
Firstformula = if(isna(match('Master!'A1,'Newname'!A1:A560,0),"",'Master'!A1)
Secondformula = if(isna(match('Master!'A1,'New'!A1:A560,0),"",index('Newname'!A1:A1000,match('Master!'A1,'Newname'!A1:A560,0)))
Sheets(Sheets.count).Name = Newname
ws.Range("A1:A560").Formula = Firstformula
ws.Range("B1:B560").Formula = Secondformula
End Sub

<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>
<!--StartFragment-->
<!--EndFragment-->
</tbody>

Thank you!

 

[Michael M]

Ok, try this....but note your formula gives a circular reference, so check it out.

Sub Namesheetformula()
Dim newname As String, ws As Worksheet
    For x = 1 To 10
        newname = Choose(x, "A", "B", "c", "d", "e", "f", "g", "h", "i", "j")
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = newname
        ActiveSheet.Range("A1:A560").Formula = "=if(isna(match(Master!A1,A1:A560,0)),"""",Master!A1)"
        ActiveSheet.Range("B1:B560").Formula = "=if(isna(match(Master!A1,A1:A560,0)),"""",index(A1:A1000,match(Master!A1,A1:A560,0)))"
    Next x
End Sub

 

[pythonruby]

Hi Michael:

Thank you! Sorry, I just realized my circular reference lol. It was supposed to be Newname_sheet instead of Newname in the formulas inputed in columns A and B. Each sheet would be different, newname_a, newname_b, etc. I tried inputting that into the vba editor and changing your code a bit but it just keeps giving me an error.
For my Match function it is supposed to look like this:
match

"=if(isna(match​

('Master'!A1,'Newname_Sheet'A1:A560,0),"""",'Master'!A1)



Sub Namesheetformula()Dim newname As String, ws As Worksheet For x = 1 To 10 newname = Choose(x, "A", "B", "c", "d", "e", "f", "g", "h", "i", "j") Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = newname ActiveSheet.Range("A1:A560").Formula = "=if(isna(match(Master!A1,'"Newname" & "_sheet"'A1:A560,0)),"""",Master!A1)" ActiveSheet.Range("B1:B560").Formula = "=if(isna(match(Master!A1,A1:A560,0)),"""",index('"Newname" & "_sheet"'A1:A1000,match(Master!A1,'"Newname" & "_sheet"'A1:A560,0)))"
Next xEnd Sub

 

[Michael M]

You don't need the newname sheet in the formula, because the formula will be inserted into the new sheet as it is created, hence removing the need for the name

Sub Namesheetformula2()
Dim newname As String, ws As Worksheet
For x = 1 To 10
newname = Choose(x, "A", "B", "c", "d", "e", "f", "g", "h", "i", "j")
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = newname
ActiveSheet.Range("A1:A560").Formula = "=if(isna(match(Master!A1,A1:A560,0)),"""",Master!A1)"
ActiveSheet.Range("B1:B560").Formula = "=if(isna(match(Master!A1,A1:A560,0)),"""",index(A1:A1000,match(Master!A1,A1:A560,0)))"
Next x
End Sub

 

[pythonruby]

Hi Michael, I do need the newname in the formula. I need my formula to say

=if(isna(match('Master'!A1, 'newname_sheet'!A1:A560, 0)),"",'Master'!A1)

I need the formula to reference two different worksheets. My formula references my worksheet: Master, and my worksheets: a_sheet, b_sheet, c_sheet. I already have an existing worksheet called master and sheets called newname_sheet(a_sheet, b_sheet, c_sheet, etc.)

Right now the formula doesn't reference 'newname_sheet"A1:A560, but simply references 'a'!A1:A560. Does that make sense? Thank you!

 

[Michael M]

Ok, I'm a little confused.......but that's not hard...
Try

Sub Namesheetformula2()
Dim newname As String, ws As Worksheet
For x = 1 To 10
newname = Choose(x, "A", "B", "c", "d", "e", "f", "g", "h", "i", "j")
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = newname & "_Sheet"
ActiveSheet.Range("A1:A560").Formula = "=if(isna(match(Master!A1," & newname & "_Sheet!A1:A560,0)),"""",Master!A1)"
ActiveSheet.Range("B1:B560").Formula = "=if(isna(match(Master!A1," & newname & "_Sheet!A1:A560,0)),"""",index(A1:A1000,match(Master!A1," & newname & "_Sheet!A1:A560,0)))"
Next x
End Sub

 

[pythonruby]

Worked perfectly! Thank you!!! On mrexcel, do they give points for answers, like they do on yahooanswers? (Sorry for the confusion, my VBA status is still a newb)I am trying to learn though, so I was wondering why it wasn't simply (Master!A1, newname & "_Sheet"!A1:A560,0)). Is this syntax? When in a formula it must be named "& Variable &" and in a non-formula we can just type variable & string (newname & "_sheet")?

ActiveSheet.Name = newname & "_Sheet"ActiveSheet.Range("A1:A560").Formula = "=if(isna(match(Master!A1," & newname & "_Sheet!A1:A560,0)),"""",Master!A1)"</pre>

 

[pythonruby]

Oh wait I just understood it, it was a string in quotes!

However, why do we need to do sheets.count?

 

[Michael M]

That simply means the code will add a sheet after the last sheet ( Sheets.count)

Sheets.Add after:=Sheets(Sheets.Count)

Sheets.Add ADD A NEW SHEET
after:= 'OBVIOUS
Sheets(Sheets.Count) THE LAST SHEET

 

[pythonruby]

I see! Again, thanks so much