Insert Copied sheet

**sikke** · 10-12-2012, 04:44 PM

Hi there,
I'm a newbe in excel programming and hope to get some help in this forum.
I'm trying to make a workbook, where each sheet are testequipment records.
Did find some codes but they need som minor rewriting.

I would like the "insert serial" macro to copy the "new sheet" instead of inserting a empty sheet after the "master"
(and still rename the copied sheet with value from inputbox")

Regarding the "search serial" macro, could this return a message if serial not found. like serial not found, would you insert a new one. (yes-No)

**sikke** · 10-12-2012, 04:47 PM

Sorry, forgot to upload the workbook....here it is

**stnkynts** · 10-12-2012, 05:49 PM

For your first question replace the similar code (module 9) with this new code:

Public Sub AddSheet()
Dim shName As String, newsht As String
Dim shExists As Boolean

Do

shName = InputBox("Please Enter the Item Serial number")
If shName <> "" Then

shExists = SheetExists(shName)
If Not shExists Then

newsht = Sheets("new sheet").Copy(after:=Worksheets(Worksheets.Count))
Sheets(Worksheets.Count).Name = shName

Else

MsgBox "Worksheet " & shName & " already exists", vbOKOnly + vbInformation, "Add Sheet"
End If
End If
Loop Until Not shExists Or shName = ""
End Sub

For your second question replace the similar code (module 2) with:

Sub GotoSheet()
  
  ' Jumping between Worksheets
    Dim sSheet As String
    sSheet = InputBox( _
    Prompt:="Serial number?", _
      Title:="Serial Search Box")
    On Error Resume Next
    If sSheet = vbCancel Then Exit Sub
    If Val(sSheet) > 9999999999999# Then
        Worksheets(Val(sSheet)).Activate
    Else
        msg = MsgBox("Serial not found, would you like to enter a new one?", vbYesNo)
            If msg = vbYes Then
                Call GotoSheet
                Exit Sub
            End If
        Worksheets(sSheet).Activate
    End If
End Sub

**sikke** · 10-13-2012, 03:16 AM

Hi stnkynts,
First of all ... THANKS...

the first one (module 9) .... excellent, works nice. Question, could i hide the "new sheet" and have the new copied one, visible?

In module 2, the message box pops up even if the serialnumber i'm searching for is already inserted, If i answer "No" the message box disappears and cursor then jumps to the correct sheet, if the sheet is named with letters then nothing happends !!!

regards
sikke

**stnkynts** · 10-16-2012, 05:03 PM

Sorry about the confusion. Try these in module 9 and 2 respectively:

Public Sub AddSheet()
Dim shName As String, newsht As String
Dim shExists As Boolean

Application.ScreenUpdating = False
Sheets("new sheet").Visible = True
Do

shName = InputBox("Please Enter the Item Serial number")
If shName <> "" Then

shExists = SheetExists(shName)
If Not shExists Then

newsht = Sheets("new sheet").Copy(after:=Worksheets(Worksheets.Count))
Sheets(Worksheets.Count).Name = shName
Sheets("new sheet").Visible = False
Sheets(shName).Activate

Else

MsgBox "Worksheet " & shName & " already exists", vbOKOnly + vbInformation, "Add Sheet"
End If
End If
Loop Until Not shExists Or shName = ""
Application.ScreenUpdating = True
End Sub

Sub GotoSheet()
  
  ' Jumping between Worksheets
    Dim sSheet As String
    Dim wksht As Worksheet
    Dim msg
    sSheet = InputBox( _
    Prompt:="Serial number?", _
      Title:="Serial Search Box")
    On Error Resume Next
    If sSheet = vbCancel Then Exit Sub
    For Each wksht In Application.Worksheets
        If wksht.Name = sSheet Then
            wksht.Activate
            Exit Sub
        End If
    Next wksht
        msg = MsgBox("Serial not found, would you like to enter a new one?", vbYesNo)
            If msg = vbYes Then
                Call GotoSheet
                Exit Sub
            End If
End Sub