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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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 ?
 
Upvote 0
The sheet containing the list of sheet names has to be active (selected) when you run the macro.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top