Excel Forum

Code: Select all

Sub Paste()
    Dim irRange As Range
    Worksheets("sheet1").Range("c3:c7").Copy
    Worksheets("sheet2").Select
    Set Item = Worksheets("sheet2").Range("B3:B1000").Cells.Find(What:=Range("b2").Value)
    If Item Is Nothing Then
        Worksheets("sheet2").Cells(65536, "B").End(xlUp).Offset(1, 0).Select
    Else
        Item.Activate
    End If
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    With Worksheets("sheet2")
        Set irRange = .Range("b3", .Range("f" & Rows.Count).End(xlUp))
        irRange.Borders.LineStyle = xlContinuous
        irRange.Sort Key1:=.Range("b3"), Order1:=xlAscending, Header:=xlGuess
    End With
    With Worksheets("sheet1")
    .Range("c3:c7").ClearContents
    .Range("C4:C7").Formula = "= vlookup($C$3,range(names)" & Range("b1000").End(xlUp).Row & ",match(c3,names,0),0)"
        Range("B4:c4").Copy
        Range("B4").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        
    End With
    Worksheets("sheet1").Select
    Range("C4").Select
End Sub   

Code: Select all

Sub Paste()
    Dim irRange As Range
    Worksheets("sheet1").Range("c3:c7").Copy
    Worksheets("sheet2").Select
    Set Item = Worksheets("sheet2").Range("B3:B1000").Cells.Find(What:=Range("b2").Value)
    If Item Is Nothing Then
        Worksheets("sheet2").Cells(65536, "B").End(xlUp).Offset(1, 0).Select
    Else
        Item.Activate
    End If
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    With Worksheets("sheet2")
        Set irRange = .Range("b3", .Range("f" & Rows.Count).End(xlUp))
        irRange.Borders.LineStyle = xlContinuous
        irRange.Sort Key1:=.Range("b3"), Order1:=xlAscending, Header:=xlGuess
    End With
    
    With Worksheets("sheet1")

    .Range("c3:c7").ClearContents
    
    .Range("c3:c7").ClearContents
    .Range("C4").Formula = "= VLOOKUP(C3,sheet2!B3:F100,2,0)"
    .Range("C5").Formula = "= VLOOKUP(C3,sheet2!B3:F100,3,0)"
    .Range("C6").Formula = "= VLOOKUP(C3,sheet2!B3:F100,4,0)"
    .Range("C7").Formula = "= VLOOKUP(C3,sheet2!B3:F100,5,0)"
        
        
    End With
    Worksheets("sheet1").Select
    Range("C4").Select
End Sub

Code: Select all

Sub Paste()
   Dim wsPaste As Worksheet
   Dim temp As Worksheet
   
   Set wsPaste = Sheets("Sheet2")
   Set temp = Sheets("Sheet1")
   
   If temp.Range("E3") = 0 Then
        temp.Range("F3:J3").Copy
        wsPaste.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        MsgBox "บันทึกข้อมูลเรียบร้อย", vbInformation
    ElseIf IsNull(temp.Range("C3")) Then
        MsgBox "๕ุณยังไม่กรอกรหัส", vbInformation
    Else
        With Application
            Dim i As Integer
            i = .Match(temp.Range("C3"), wsPaste.Range("B3:B100")) + 1
            temp.Range("G3:J3").Copy
            wsPaste.Cells(i, 3).Offset(1, 0).PasteSpecial xlPasteValues
            MsgBox "แก้ไขข้อมูลเรียบร้อยแล้ว", vbInformation
            Application.CutCopyMode = False
        End With
        
    End If
   
End Sub