Articles

Solved Dim _____ As String in VBA twice

September 24, 2013 at 10:00:22
Specs: Windows 7

    Dim ws As Worksheet
    Dim newSheetName As String
    newSheetName = Sheets("GMPAIRS").Range("B3")
    For Each ws In Worksheets
    If ws.Name = newSheetName Or newSheetName = "" Then
    MsgBox "Sheet already exists or name is invalid", vbInformation
    Exit Sub
    End If
    Next
    Sheets.Add Type:="Worksheet"
    With ActiveSheet
    .Move After:=Worksheets(Worksheets.Count)
    .Name = newSheetName
    End With

I have tried to use the above in a Single VBA twice but it comes up as an error
I have changed 'newSheetName' to 'NwShtName' in the second one

Obviously this does not work as I have tried, so what am I missing?, or is it just not possible.

The idea behind this is to combine two separate VBA's, of which both generate a new worksheet that is named from whatever Sheet and Range I need to use.

Any ideas?


See More: Dim _____ As String in VBA twice

Report •


#1
September 24, 2013 at 10:26:27
✔ Best Answer

Do you use indents when you write your VBA code? Code is easier to read and follow when sections are indented. Comments help too.

Dim ws As Worksheet
Dim newSheetName As String
'Set Sheet Name variable to value from cell
  newSheetName = Sheets("GMPAIRS").Range("B3")
'Verify that sheet doesn't already exist, quit if it does 
    For Each ws In Worksheets
     If ws.Name = newSheetName Or newSheetName = "" Then
       MsgBox "Sheet already exists or name is invalid", vbInformation
       Exit Sub
     End If
    Next
'If sheet doesn't exist, create it and name it
     Sheets.Add Type:="Worksheet"
       With ActiveSheet
         .Move After:=Worksheets(Worksheets.Count)
         .Name = newSheetName
       End With

re: "I have tried to use the above in a Single VBA twice "

I don't know what that means. I've never heard of a Single VBA. What is that?

The snippet of code that you posted above seems to work fine. If you have other code that is not working, perhaps you should post that. We can't fix what we don't see.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 26, 2013 at 07:38:48

Dim ws As Worksheet
Dim newSheetName As String
'Set Sheet Name variable to value from cell
  newSheetName = Sheets("GMPAIRS").Range("B3")
'Verify that sheet doesn't already exist, quit if it does 
    For Each ws In Worksheets
     If ws.Name = newSheetName Or newSheetName = "" Then
       MsgBox "Sheet already exists or name is invalid", vbInformation
       Exit Sub
     End If
    Next
'If sheet doesn't exist, create it and name it
     Sheets.Add Type:="Worksheet"
       With ActiveSheet
         .Move After:=Worksheets(Worksheets.Count)
         .Name = newSheetName
       End With
Dim ws1 As Worksheet
Dim newSheetName1 As String
'Set Sheet Name variable to value from cell
  newSheetName1 = Sheets("MASTERS").Range("M7")
'Verify that sheet doesn't already exist, quit if it does 
    For Each ws1 In Worksheets
     If ws.Name = newSheetName1 Or newSheetName1 = "" Then
       MsgBox "Sheet already exists or name is invalid", vbInformation
       Exit Sub
     End If
    Next
'If sheet doesn't exist, create it and name it
     Sheets.Add Type:="Worksheet"
       With ActiveSheet
         .Move After:=Worksheets(Worksheets.Count)
         .Name = newSheetName1
       End With

I have come up with the above which creates two new worksheets with each being titled differently, as I required. Quite easy really, even though I took a guess at what I needed to do.

re: "I have tried to use the above in a Single VBA twice " Sorry about that, just the incorrect way of typing what I was trying to say.

Thanks


Report •

#3
September 26, 2013 at 07:43:25


Report •

Related Solutions

#4
September 26, 2013 at 08:21:32

Yes, problem solved,
and can I take this opportunity to thank you again for your help.
I am finding it invaluable and am learning all the time.
Many many thanks

Report •


Ask Question