Thread: Adding New Sheets

I got a workbook with a main sheet and then 52 sheets behind it.

they are named
Week 1A
Week 1B
Week 2A
Week 2B
Week 3A
Week 3B

and so on all the way to Week 26A and B

Now I need each sheet to be identical except for one cell in each sheet.

Cell A3 ='MLB OVERALL'!M10

I need M10 to change by 1 for each sheet.


Is there a quick way to do this

can I create a macro??? I just know doing it by hand is a pain.

Matt

Good evening rbpd5015

Something like this should add the sheets for you, name them and pop the formula into A3 of each sheet :

Sub test()
Dim counter As Long
counter = 10
For n = 1 To 26
Sheets.Add.Name = "Week " & n & "A"
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
Range("A3").Formula = "='MLB OVERALL'!M" & counter
counter = counter + 1
Sheets.Add.Name = "Week " & n & "B"
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
Range("A3").Formula = "='MLB OVERALL'!M" & counter
counter = counter + 1
Next n
End Sub

HTH

DominicB

Dominic,

Sorry man I am a very fast learner, could you please guide me a bit more of how to get that sub routine into the cell. I have never done that before so I have no clue, but I am sure once you show me once I will NEVER forget.

Matt

Hi rbpd5015

Originally Posted by rbpd5015

Sorry man I am a very fast learner, could you please guide me a bit more of how to get that sub routine into the cell.

From Excel press Alt + F11 - this will open the VBE.
Make sure the file you are working on is the one selected in the VBA Project window on the left.
Go to Insert > Module and paste the code into the empty pane that opens.
Go to File > Close and return to Microsoft Excel

Once back in Excel, go to Tools > Macro > Macros, click on "test" and "Run".

HTH

DominicB

Dom,
I did the exact steps and get an error #400.

I will do some research now and see what that means. I uploaded the book in case you wanted to look at it.

Matt

Hi rbpd5015

I've had a look at your file.
The second sheet (Sheet1A) already has a formula in cell A3. Do you want this formula overwriting.
If the formula that you specified above, ='MLB OVERALL'!M10 goes into this cell (A3), it causes a circular reference.

Can you clarify exactly what your requirements are?

DominicB

ok the formula is needed.

what I wanted was sheets automatically added and the M10 to increase with each sheet.

I was just saying that each sheet is identical except for the name and that formula. SO I thought there would be an easy way to duplicate them.

Matt

Hi rbpd5015

Originally Posted by rbpd5015

ok the formula is needed.

What formula? The one that causes a circular reference? Is this correct?

Originally Posted by rbpd5015

what I wanted was sheets automatically added and the M10 to increase with each sheet.

Are all sheets to be identical (apart from A3)? And they go up to 26B?
Does Sheet1A start at M10?

DominicB

Ok the Formula in Sheet 'WEEK 1A is needed it is what calls the value needed.

so the first sheet WEEK 1A the formula is
='MLB OVERALL'!M10 the next sheet WEEK 1B is
='MLB OVERALL'!M11 then
='MLB OVERALL'!M12 for Sheet WEEK 2A and so on

Matt

Hi rbpd5015

OK. Have a look at your workbook attached.
It now has 52 sheets following your prescribed naming convention,plus the front sheet.
Cell A3 in each of your backing sheets has the formula you requested, incrementing by 1 each time. In the end I just copied your sheets, used my add-in (see below) for the bulk rename and thenusedthe macro below to loop through all worksheets and populate A3 with the formula :

Sub AllSheets()
counter = 9
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A3").Formula = "='MLB OVERALL'!M" & counter
counter = counter + 1
Next
End Sub

I removed the above macro from the sheet after use as it seems a one use only job.

HTH

DominicB

Thanks,
However I am a bit confused why did you name the SHEETS SHEETS, They were named WEEK for a reason???????

Now when I got and try to rename them I window that wants me to look for a file to update the values?????
Matt

Hi rbpc5015

Originally Posted by rbpd5015

However I am a bit confused why did you name the SHEETS SHEETS?

Errrr, not sure really. I think I got distracted somewhere along the way.

I can't fix it now, as I don't have access to XL2007 on this machine, but I'll upload a corrected workbook when I get home.

DominicB

Dom,
Thanks so much.. ONE last favor please

on my main Sheet MLB OVERALL. I have a bunch of rows that call to the different WEEK sheets.

If you are building a macro to rename the sheets can these be added with them.

for example row 19 is

column A is WEEK 5B (Which is correct, but doesn't work because the hyperlink is looking for Sheet WEEK 5B. When you build a macro can it make a hyperlink for each one of these below WEEK 5B.

column B is =8-('SHEET 16B'!$C$11) (Should be WEEK 5B

column D,E,F,I,J all also have SHEET 16B and should be WEEK 5B

these values should always be calling that rows sheet.


Thanks a ton,

Matt

Hi rbpd5015

OK, all the sheets have ben renamed to weeks and all the hyperlinks inserted on the fron sheet.

However, your original sheet didn't have a hyperlink for 15B (the sheet was there,just not a line on MLB Overall). Therefore, the hyperlinks that I have generated via code have over-run your preformatted rows by one. You may have to jiggle (technical word) your formulae around a bit.

HTH

DominicB