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!

 

[Peter_SSs]

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)

As I understand it, you are asking for a new sheet called "A_Sheet" to be added and then formulas like this added to it

=IF(ISNA(MATCH(Master!A1,A_Sheet!A1:A560,0)),"",Master!A1)

It seems to me that Michael is saying that you don't need the red part of the formula but you are stating you do.
If that is correct, then I have to agree with Michael. Not only do you not need the name of the sheet that the formula is on, it is bad practice to do so.

If you want to see why follow through the steps below.

Start a new workbook and make Sheet1 like this

Excel Workbook
	B	C
1	Name	Sales
2	John	20
3	Mary	12
4	Peter	14
5	John	18
6	Mary	18
7	Peter	20
Sheet1

Now on Sheet2, put the headers and names as shown
Put this formula in B2 and copy it down to B4:
=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A2,Sheet1!$C$2:$C$7)

Excel Workbook
	A	B
1	Name	Sales
2	John	38
3	Mary	30
4	Peter	34
Sheet2

Now on Sheet2 sort the data on the Sales column.
Here is the result. Can you see something drastically wrong?

Excel Workbook
	A	B
1	Name	Sales
2	Mary	34
3	Peter	38
4	John	30
Sheet2

Now set up sheet 2 again with the names in the original order but put the formula in B2 without the red part & copy down.
Sort again and you should see that everything works correctly.

Sorting is not the only thing that can get messed up if you use the sheet name of the formula sheet in the formulas on that sheet.

 

[Michael M]

Thanks Peter
You have put it perfectly....which I was struggling to do....obviously...

 

[pythonruby]

Oh, I see. So if the location of the cell is in the sheet itself I don't need to put the name of the sheet. My apologies, but thank you for taking the time to explain this!

 

[Peter_SSs]

Oh, I see. So if the location of the cell is in the sheet itself I don't need to put the name of the sheet.

Exactly.

My apologies, but thank you for taking the time to explain this!

No problem. That's what the forum is for.