creating new sheet based on a range of rows

niloyparvaz

New Member
Joined
Dec 16, 2009
Messages
14
Hi

So basically I am trying to create new worksheets based on a range of rows. For instance if i have a range of A1:F1 and I want to check this range and see if there are any values which corresponds to the name of a worksheet already, if not then create a new worksheet naming it according to the value of the row. I know its a really easy bit of coding but I am an amateur in VBA and I would really appreciate it your help. Thanks
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub NewSht()
Dim c As Range
With ActiveSheet
    For Each c In .Range("A1:F1")
        With c
            If Not WorksheetExists(.Value) Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Value
            End If
        End With
    Next c
End With
End Sub


Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
 

niloyparvaz

New Member
Joined
Dec 16, 2009
Messages
14
Hi
Thanks for ur response

The code works to a certain extent.

For instance I already had 10 worksheets according to the G10 currency names (USD,, EUD, GBP and so on). USD was my first sheet so I removed it manually from excel and tried running the code. Isnt the code supposed to put the USD sheet back in the workbook then provided the name is defined in one of the cells within the range ?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
The sheet containing the list of sheet names has to be active (selected) when you run the macro.
 

niloyparvaz

New Member
Joined
Dec 16, 2009
Messages
14

ADVERTISEMENT

Hi

Thanks again

Is there a way to stop bringing up new worksheet, for instance after the last currency name, it keeps on making new sheet with just the default sheet names and then gives an error in vba
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub NewSht()
Dim c As Range
With ActiveSheet
    For Each c In .Range("A1:F1")
        With c
            If .Value <> "" Then
                If Not WorksheetExists(.Value) Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Value
                End If
            End If
        End With
    Next c
End With
End Sub


Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
 

niloyparvaz

New Member
Joined
Dec 16, 2009
Messages
14

ADVERTISEMENT

Hi thank you very much it seems to be working fine now

I have some other problems, and I was hoping you can help me out

I am sending you a screenshot which wil make it easier to understand

Basically if you check the 4th row I have the names of all the currencies there and below I have the currency table which I update every 30 mins

for instance the USD currency table I just had to put this formula =MDVLiborCurve(B3,B4) and then make an array of the formula like its shown in the sheet. Then for the same row, the EUR is 4 columns away

What I would like to do is, everytime I put a new currency in the Currencies row (4th row), it should check the last currency in the 6th row, and then move 4 columns to the right and make a table for that currency using the formula. In simpler words I am looking forward to automate this process

Can you help me out? Let me know if my description is not clear. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top