Snasui Excel Forum

Code: Select all

Worksheets("Payment").Range("J4") _
    = Format(Application.Max(Sheets("Database").Range("B:B")) + 1, "21-000")

Code: Select all

'Other code
Dim i As Integer, lstNo As String

With Worksheets("Database")
    lstNo = .Range("b" & .Rows.Count).End(xlUp).Value
End With

With Worksheets("Payment")
    If lstNo = "No." Then
        .Range("j4").Value = "21-001"
    Else
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End If
End With
'Other code

Code: Select all

Sub record()
Dim rs As Range, rt As Range
Dim rs1 As Range, rt1 As Range
Dim i As Integer, lstNo As String

With Worksheets("Database")
    lstNo = .Range("b" & .Rows.Count).End(xlUp).Value
End With

With Worksheets("Payment")
    If lstNo = "No." Then
        .Range("j4").Value = "21-001"
    Else
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End If
End With

With Worksheets("Template")
    i = Application.CountIf( _
        .Range("K3:K4"), ">0")
    Set rs = .Range("A2:K" & 2 + i)
    Set rs1 = .Range("A8:F8")
End With
    Set rt = Worksheets("Database") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Set rt1 = Worksheets("Report") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    rs.Copy
    rt.PasteSpecial xlPasteValues
    rs1.Copy
    rt1.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
MsgBox "Finish"

End Sub

Code: Select all

Sub record()
    Dim rs As Range, rt As Range
    Dim rs1 As Range, rt1 As Range
    Dim i As Integer, lstNo As String
    
    With Worksheets("Payment")
        If .Range("j4").Value = "" Then
            .Range("j4").Value = "21-001"
        End If
    End With
    With Worksheets("Template")
        i = Application.CountIf( _
            .Range("K3:K4"), ">0")
        Set rs = .Range("A2:K" & 2 + i)
        Set rs1 = .Range("A8:F8")
    End With
    Set rt = Worksheets("Database") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Set rt1 = Worksheets("Report") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    rs.Copy
    rt.PasteSpecial xlPasteValues
    rs1.Copy
    rt1.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    With Worksheets("Database")
        lstNo = .Range("b" & .Rows.Count).End(xlUp).Value
    End With
    
    With Worksheets("Payment")
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End With
    MsgBox "Finish"
End Sub

Code: Select all

Sub record()
    Dim rs As Range, rt As Range
    Dim rs1 As Range, rt1 As Range
    Dim i As Integer, lstNo As String
    
    With Worksheets("Payment")
        If .Range("j4").Value = "" Then
            .Range("j4").Value = "21-001"
        End If
        End With
            With Worksheets("Payment2")
        If .Range("j4").Value = "" Then
            .Range("j4").Value = "21-001"
        End If
        End With
            With Worksheets("Payment3")
        If .Range("j4").Value = "" Then
            .Range("j4").Value = "21-001"
        End If
    End With
    With Worksheets("Template")
        i = Application.CountIf( _
            .Range("j3:j7"), ">0")
        Set rs = .Range("A2:Q" & 2 + i)
        Set rs1 = .Range("A13:F13")
    End With
    Set rt = Worksheets("Database") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Set rt1 = Worksheets("Report") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    rs.Copy
    rt.PasteSpecial xlPasteValues
    rs1.Copy
    rt1.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    With Worksheets("Database")
        lstNo = .Range("b" & .Rows.Count).End(xlUp).Value
    End With
    
    With Worksheets("Payment")
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End With
    
    With Worksheets("Payment")
        lstNo = .Range("j4").Value
    End With
    
    With Worksheets("Payment2")
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End With
       With Worksheets("Payment2")
        lstNo = .Range("j4").Value
    End With
    
    With Worksheets("Payment3")
        .Range("j4").Value = "21-" & Format(VBA.Right(lstNo, 3) + 1, "000")
    End With


    MsgBox "Finish"
End Sub