VBA Macro to change name of worksheet

[dinkss]

Hi all,

Is there any way of creating a macro that will create new worksheet inside of workbook with weeks numbering?

I have worksheet named WEEK-21, and want to have a macro that when used (I will assign macro button) will create a new worksheet with name WEEK-22 and so on. So after pressing assigned button I want to be able to create new worksheets with weeks numbering following the name of the first workbook, so WEEK-22, WEEK-23, WEEK-24 etc etc.

Any help will be greatly appreciated.

 

[offthelip]

try this code:

Sub Addsheet()
nshts = Worksheets.Count
maxwk = 0
For i = 1 To nshts
 wn = Worksheets(i).Name
  If Left(wn, 4) = "WEEK" Then
    dsh = InStr(wn, "-")
    nm = Mid(wn, dsh + 1)
    If nm > maxwk Then
     maxwk = nm
    End If
  End If
Next i
If maxwk > 0 Then
 Worksheets.Add(After:=Sheets("WEEK-" & maxwk)).Name = "WEEK-" & maxwk + 1
End If

End Sub

 

[dinkss]

try this code:

Sub Addsheet()
nshts = Worksheets.Count
maxwk = 0
For i = 1 To nshts
 wn = Worksheets(i).Name
  If Left(wn, 4) = "WEEK" Then
    dsh = InStr(wn, "-")
    nm = Mid(wn, dsh + 1)
    If nm > maxwk Then
     maxwk = nm
    End If
  End If
Next i
If maxwk > 0 Then
 Worksheets.Add(After:=Sheets("WEEK-" & maxwk)).Name = "WEEK-" & maxwk + 1
End If

End Sub

I will try it and will let you know! Thank you!

 

[dinkss]

I will try it and will let you know! Thank you!

try this code:

Sub Addsheet()
nshts = Worksheets.Count
maxwk = 0
For i = 1 To nshts
 wn = Worksheets(i).Name
  If Left(wn, 4) = "WEEK" Then
    dsh = InStr(wn, "-")
    nm = Mid(wn, dsh + 1)
    If nm > maxwk Then
     maxwk = nm
    End If
  End If
Next i
If maxwk > 0 Then
 Worksheets.Add(After:=Sheets("WEEK-" & maxwk)).Name = "WEEK-" & maxwk + 1
End If

End Sub

Hi Buddy,

Your macro works brilliant but can you modify it to copy current sheet instead of creating blank one?
It changes the name perfectly but I would likle to copy current sheet and change name automatically.

Is it possible?

I was using this macro for long time - it is copying previous sheet and give you a message box with question how many copies do you want but it doesn't change name like your one.

Dim I As Long
Dim xNumber As Integer
Dim xName As String
Dim xActiveSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Set xActiveSheet = ActiveSheet
xNumber = InputBox("Enter number of times to copy the current sheet")
For I = 1 To xNumber
xName = ActiveSheet.Name
xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
ActiveSheet.Name = "WEEK-" & I
Next
xActiveSheet.Activate
Application.ScreenUpdating = True
End Sub


Can you help me please?

Thank so much for your help.

 

[offthelip]

try this:

Sub Addsheet()
nshts = Worksheets.Count
maxwk = 0
For i = 1 To nshts
 wn = Worksheets(i).Name
  If Left(wn, 4) = "WEEK" Then
    dsh = InStr(wn, "-")
    nm = Mid(wn, dsh + 1)
    If nm > maxwk Then
     maxwk = nm
    End If
  End If
Next i
If maxwk > 0 Then
 ActiveSheet.Copy After:=Sheets("WEEK-" & maxwk)
 ActiveSheet.Name = "WEEK-" & maxwk + 1
End If

 

[dinkss]

try this:

Sub Addsheet()
nshts = Worksheets.Count
maxwk = 0
For i = 1 To nshts
 wn = Worksheets(i).Name
  If Left(wn, 4) = "WEEK" Then
    dsh = InStr(wn, "-")
    nm = Mid(wn, dsh + 1)
    If nm > maxwk Then
     maxwk = nm
    End If
  End If
Next i
If maxwk > 0 Then
 ActiveSheet.Copy After:=Sheets("WEEK-" & maxwk)
 ActiveSheet.Name = "WEEK-" & maxwk + 1
End If

This works perfect but it doesn't give me a popup box with question of how many copies do I want to create. Can you add something like that?
InputBox("Enter number of times to copy the current sheet")

thank you

 

[offthelip]

You didn't ask for that in the original question, this is what is known as "Requirements Creep" which in the real world of software developement can cost you a lot!!!
The easy way to do this is to add the subroutine :

Sub loopaddsht()
xNumber = InputBox("Enter number of times to copy the current sheet")
For I = 1 To xNumber
Call Addsheet
Next I
End Sub

And run this one from your button

 

[dinkss]

You didn't ask for that in the original question, this is what is known as "Requirements Creep" which in the real world of software developement can cost you a lot!!!
The easy way to do this is to add the subroutine :

Sub loopaddsht()
xNumber = InputBox("Enter number of times to copy the current sheet")
For I = 1 To xNumber
Call Addsheet
Next I
End Sub

And run this one from your button

Oh, sorry! I'm only a newbie. Have no clue about it. Very sorry. I will try this tomorrow and will let you know. Thanks

 

[dinkss]

Oh, sorry! I'm only a newbie. Have no clue about it. Very sorry. I will try this tomorrow and will let you know. Thanks

Hi buddy,

you are genius! All is working perfectly!!! WOW! I'm really greatfull for your help!

 

[dinkss]

Could you be so kind and help me with another task?

I'm looking to copy data - export data from multiple workbooks in different catalogues, but only from the range of A286:F311. Is it possible to do it?
I have 6 folders with 1 workbook in each folder. I want to have a folder SUMMARY with workbook SUMMARY that would copy all data from the above range from each workbook.

Is it possible to do that? Let say if I open Summary workbook I want to have data shown side by side to see full output from production. Hopefuly I have explained this right.

Thanks