VBA duplicate Workbook with sheet names from list

[MacroNobie]

Hi everybody, I am working on a dashboard, I have a finished one for a specific region and know I want to replicate that workbook but have the sheet name with the region changed so for example this workbook has: Directory, Region 1, Data. I would like to copy it and have: Directory, Region 2, Data.

Is there a way? Thanks for the help!!

 

[ranman256]

Public Sub SaveAsNewRegion()
Dim vNum, vName
Dim sht As Worksheet


vNum = InputBox("Enter Region#", "Save As New Region Workbook")
If vNum = "" Then Exit Sub


For Each sht In Worksheets
  If InStr(sht.Name, "Region") > 0 Then
     vName = "Region " & vNum
     sht.Name = vName
     ActiveWorkbook.SaveAs "Workbook_" & vName & ".xlsx"
     Exit Sub
  End If
Next
End Sub

 

[Fluff]

Another option

Sub NewWbk()

   Dim Ws As Worksheet
   Dim Fname As String
   For Each Ws In Worksheets
      If Ws.Name Like "Region*" Then
         Ws.Name = "Region " & Right(Ws.Name, 1) + 1
         Fname = Ws.Name
         Exit For
      End If
   Next Ws
   ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Fname, 52
End Sub

 

[MacroNobie]

Thanks guys for your help. If I do this, it changes the background color, is there a way to prevent that?

 

[Fluff]

Neither code change any colours.
What do you mean by background colour? & how does it change?

 

[MacroNobie]

It is normally a light blue but changes to light green kind of. The blue that I used it not a standard blue.

 

[Fluff]

If you are using non standard colours, then you will probably have to reset those colours in the new book. But that is something I know nothing about.