Copy entire worksheet and rename it

[PrashanthGM12345]

Hi gurus,

i have a workbook with 3 worksheets.
Intro, Context, Data.

i need a VBA code to copy the "Data" worksheet and paste it in the same workbook with the name "CSI_1".
Next time when i run the macro it should copy the entire "Data" worksheet and paste it in the same workbook after "CSI_1" worksheet as "CSI_2".
This should keep on happening as and when i run the macro.

Also i need to hide the "Data" worksheet.

Can anyone please help me with this?

I have attached the excel workbook for easy reference.

 

[vletm]

PrashanthGM12345
One possible as You wrote...
Use those buttons.

 

[Hui]

Try the following code:

Sub Copy_Save_as_WS()
   

Dim WS_Count As Integer
Dim I As Integer, Highno As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
'Copy Data
Sheets("Data").Copy After:=Sheets(Worksheets.Count)

'Find Highest No CSI_ Worksheet
For I = 1 To WS_Count
  If InStr(1, ActiveWorkbook.Worksheets(I).Name, "CSI_") > 0 Then
  tmp = CInt(Right(ActiveWorkbook.Worksheets(I).Name, Len(ActiveWorkbook.Worksheets(I).Name) - 4))
  If tmp > Highno Then Highno = tmp
  End If
Next I
   
'Renanme new worksheet
ActiveWorkbook.Worksheets(Worksheets.Count).Name = "CSI_" & CStr(Highno + 1)
   
'Cleanup
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Delete

End Sub

 

[PrashanthGM12345]

Try the following code:

Sub Copy_Save_as_WS()
  

Dim WS_Count As Integer
Dim I As Integer, Highno As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
'Copy Data
Sheets("Data").Copy After:=Sheets(Worksheets.Count)

'Find Highest No CSI_ Worksheet
For I = 1 To WS_Count
  If InStr(1, ActiveWorkbook.Worksheets(I).Name, "CSI_") > 0 Then
  tmp = CInt(Right(ActiveWorkbook.Worksheets(I).Name, Len(ActiveWorkbook.Worksheets(I).Name) - 4))
  If tmp > Highno Then Highno = tmp
  End If
Next I
  
'Renanme new worksheet
ActiveWorkbook.Worksheets(Worksheets.Count).Name = "CSI_" & CStr(Highno + 1)
  
'Cleanup
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Delete

End Sub

Thank you so much. works perfectly.

 

[PrashanthGM12345]

PrashanthGM12345
One possible as You wrote...
Use those buttons.

Thank you so much. It works