VBA - Add New Worksheets & Name (from a list of cells)

[Dreamteam]

Hi all,

First post and pretty excited...

I'm new to VBA and I'm really just trying to learn it correctly from the start (whatever that actually means). I have a pretty straightforward piece of code that I have written - which works - and that's great. However, I am wondering if this is almost perfect. By this I mean I guess that there are plenty of ways to complete this task but I am keen to know if this way is as good as it can get.

Oops, I better mention what I am trying to do here. I have a list of currency pairs in col A of a worksheet (AUD USD, USD CAD, GBP AUD etc...) and I am creating new worksheets based on these values.

It has taken me quite a while to write as I had a few issues with it - i.e. if I ran it when I had another workbook open then the worksheets would be added to the wrong workbook etc.

I think what I am also keen to find out is the view of using object references as opposed to statements like .select / .activesheet etc

Any advice/comments would be much appreciated because as alluded to above I want to begin the correct way.

Many thanks

Dt

Sub CreateWorksheetsAddNames()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim MyCell As Range
    Dim MyRange As Range
   
    Set wb = Workbooks("What is Trending xlsm")
    Set ws = wb.Worksheets("Data")
    Set MyRange = ws.Range("a2")
    Set MyRange = ws.Range(MyRange, MyRange.End(xlDown))
    
    For Each MyCell In MyRange
        wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
        wb.Worksheets(wb.Worksheets.Count).Name = MyCell.Value
    Next MyCell
    
    
End Sub

 

[cerfani]

the filename does not include a dot before the extension, should be '.xlsm,' no?

Also if you are writing a macro you shouldn't reference objects with select and activate because it will be harder to follow. When you set objects to named variables it is easier to read and the code will be less prone to error. Like if you add a worksheet but then want to do something with the previously active worksheet you would have to reactivate it again. It just creates more steps and the code is more vague.

 

[Dreamteam]

Thanks for this Cerfani.

No, the file name is fine - I actually named this What is Trending xlsm - although I am not sure why I did that :~)

 

[hiker95]

I'm new to VBA and I'm really just trying to learn it correctly from the start

Dreamteam,

Welcome to the MrExcel forum.


See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 2/3/2018

https://www.mrexcel.com/forum/excel...ere-entries-another-column-2.html#post5001046

 

[Fluff]

@Dreamteam
There's nothing wrong with your code.
However I would recommend always adding the file extension onto the end of a workbook name.
If somebody with different system settings tries to run that macro it will fail on the Set wb= line.
Also, this is more personal preference than anything, I'd write the Set MyRange like

Set MyRange = Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))

 

[Dreamteam]

Dreamteam,

Welcome to the MrExcel forum.


See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 2/3/2018

https://www.mrexcel.com/forum/excel...ere-entries-another-column-2.html#post5001046

Many many thanks for this - crumbs what a resource :~)

 

[Dreamteam]

@Dreamteam
There's nothing wrong with your code.
However I would recommend always adding the file extension onto the end of a workbook name.
If somebody with different system settings tries to run that macro it will fail on the Set wb= line.
Also, this is more personal preference than anything, I'd write the Set MyRange like

Set MyRange = Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))

Many thanks for these comments

 

[Fluff]

Many thanks for these comments

You're welcome

 

[hiker95]

Many many thanks for this - crumbs what a resource :~)

Dreamteam,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.