My code is not entering the field in required sheet

Question

My code is working fine in creating a new sheet, but the next part of the code is not working properly I have given certain fields which are to be entered in sheet 123 but it is entering only the value "Remarks" in cell A1 i.e only the last field . what is the problem?

I'm not able get it.

Sub CreateSheet()



Dim xName As String
Dim xSht As Object
On Error Resume Next
xName = InputBox("Please enter a name for this new sheet ")
If xName = "" Then Exit Sub
    Set xSht = Sheets(xName)
    If Not xSht Is Nothing Then
        MsgBox "Sheet cannot be created as there is already a worksheet with the same name in this workbook"
        Exit Sub
        End If
        Sheets.Add(, Sheets(Sheets.Count)).Name = xName


        Sheets("New Ledger Creator").Activate


         Dim lastrow As Long
    lastrow = Range("b" & Rows.Count).End(xlUp).Row
    Range("b" & lastrow + 1).Select

    Selection = xName



Sheets("123").Select


Range("A1").Select
Selection.Value = "Paid"
Range("A2").Select

Selection.Value = "Date"
Range("B2").Select
Selection.Value = "For"
Range("C2").Select
Selection.Value = "Through"
Range("D2").Select
Selection.Value = "Amount"
Range("E2").Select
Selection.Value = "Remarks"
Range("F2").Select
Selection.Value = "Date"
Range("G2").Select
Selection.Value = "For"
Range("H2").Select
Selection.Value = "Through"
Range("I2").Select
Selection.Value = "Amount"
Range("J2").Select
Selection.Value = "Remarks"
Range("A1:E1").Select

End Sub

See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Oct 29 '18 at 16:43
`On Error Resume Next` hides a multitude of sins. Have the wrong workbook active when the code runs? `Resume Next` doesn't care, entered sheet name doesn't exist? Just gloss over that error... Anyway... generally don't use `On Error Resume Next` - trap the error and handle it before letting the code continue. — Darren Bartrup-Cook, Oct 29 '18 at 17:06
Just tested and `Resume Next` did as I expected - _New Ledger Creator_ didn't exist so it worked out the `lastrow` on the new (blank) sheet I just created and then selected cell `B2`. It couldn't select sheet `123` as my workbook hasn't got one so it put headings in `A1:J2` (overwriting the sheet name it had just placed there) of the sheet it had just created.... — Darren Bartrup-Cook, Oct 29 '18 at 17:10

score 1 · Answer 1 · answered Oct 29 '18 at 17:32

Had a play around and came up with the code below. Shows not using Select and getting rid of On Error Resume Next.

Option Explicit 'VERY IMPORTANT!!!!!!!!
                'Place at top of every new module by selecting Tools ~ Options and ticking
                'Require Variable Declaration.

Sub CreateSheet()

    Dim xName As String
    Dim xSht As Object
    Dim IllegalCharacters As Variant
    Dim iChr As Variant
    Dim shtNew As Worksheet
    Dim shtLCreator As Worksheet

    On Error GoTo Err_Handle

    IllegalCharacters = Array("/", "\", "[", "]", "*", "?", ":")
    xName = InputBox("Please enter a name for this new sheet.")

    'Remove any illegal characters from sheet name.
    For Each iChr In IllegalCharacters
        xName = Replace(xName, iChr, "")
    Next iChr

    If Len(xName) > 0 Then
        If WorkSheetExists(xName) Then
            MsgBox "Cannot create sheet '" & xName & "' as it already exists.", vbOKOnly + vbCritical
        Else
            Set shtNew = ThisWorkbook.Worksheets.Add
            shtNew.Name = xName

            Set shtLCreator = ThisWorkbook.Worksheets("New Ledger Creator")
            shtLCreator.Cells(Rows.Count, 2).End(xlUp).Offset(1) = xName

            With ThisWorkbook.Worksheets("123")
                .Range("A1") = "Paid"
                .Range("A2:J2") = Array("Paid", "Date", "For", "Through", "Amount", _
                                        "Remarks", "Date", "For", "Through", "Amount", "Remarks")
            End With
        End If
    End If

FAST_EXIT:

Exit Sub

Err_Handle:
    Select Case Err.Number
        Case 11 'Division by 0

            MsgBox "Somehow a division by 0 happened." & vbCr & _
                   "Well done, you did the impossible - there's no calculations in this code.", vbOKOnly

            'Resume Next 'Error was dealt with, so continue on line following error.
            'Resume 'Error was dealt with so continue on same line that caused error.
            Resume FAST_EXIT 'Error was dealt with, resume at Fast_Exit label.

        Case Else
            MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
    End Select

End Sub

'Checks if a worksheet exists - returns TRUE/FALSE
Public Function WorkSheetExists(SheetName As String, Optional WrkBk As Workbook) As Boolean
    Dim wrkSht As Worksheet

    If WrkBk Is Nothing Then
        Set WrkBk = ThisWorkbook
    End If

    On Error Resume Next
        Set wrkSht = WrkBk.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0
End Function

My code is not entering the field in required sheet

1 Answers1