Excel Forum

Code: Select all

Sub save()
    Sheets("ป้อนข้อมูล").Range("D3").Copy
    With Sheets("รายงาน")
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
      Sheets("ป้อนข้อมูล").Range("H2").Copy
    With Sheets("รายงาน")
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("D11").Copy
    With Sheets("รายงาน")
        .Range("C" & .Range("C" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("D6").Copy
    With Sheets("รายงาน")
        .Range("D" & .Range("D" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("I3").Copy
    With Sheets("รายงาน")
        .Range("E" & .Range("E" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("D4").Copy
    With Sheets("รายงาน")
        .Range("F" & .Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("D5").Copy
    With Sheets("รายงาน")
        .Range("G" & .Range("G" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Sheets("ป้อนข้อมูล").Range("I6").Copy
    With Sheets("รายงาน")
        .Range("H" & .Range("H" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
     Sheets("ป้อนข้อมูล").Range("D8").Copy
    With Sheets("รายงาน")
        .Range("I" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    Range("D3").Select
ActiveWorkbook.save
End Sub

Code: Select all

Sub test()
    dim rngSource As Variant
    With Sheets("ป้อนข้อมูล")
        rngSource = Array(.Range("d3"), .Range("h2"), .Range("d11"), .Range("d6"), .Range("i3"), _
            .Range("d4"), .Range("d5"), .Range("i6"), .Range("d8"))
    End With
    With Sheets("รายงาน")
        .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 9).Value = rngSource
    End With
    Range("D3").Select
    ActiveWorkbook.save
End Sub

Code: Select all

Sub save()
    Dim rngSource As Variant
    Dim rngBase As Range
    Dim i As Integer
    With Sheets("รายงาน")
        Set rngBase = .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
    End With
    With Sheets("ป้อนข้อมูล")
        i = Application.CountIf(rngBase, .Range("d3"))
        If i > 0 Then
            MsgBox "ทะเบียนซ้ำ ยกเลิกการบันทึก"
            Exit Sub
        End If
        rngSource = Array(.Range("d3"), .Range("h2"), .Range("d11"), .Range("d6"), .Range("i3"), _
            .Range("d4"), .Range("d5"), .Range("i6"), .Range("d8"))
    End With
    With Sheets("รายงาน")
        .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 9).Value = rngSource
    End With
    Range("D3").Select
    ActiveWorkbook.save
End Sub