Excel Forum

Code: Select all

Sub MainCode_ex()
    Dim formBook As Workbook
    Dim i As Integer
    Dim e As Long
    Dim rs As Range
    Dim rt As Range
    Dim rk As Range
    Set formBook = ThisWorkbook
   Set wbShare = Workbooks("Material_BSh.xlsx")
    With wbShare
           e = .Sheets("Stock_MR").Range("B" & Rows.Count).End(xlUp).Value + 1    'หาเลขสุดท้ายแลัว + 1
          formBook.Worksheets("FormRM_ex").Range("E5").Value = e
    End With
    wbShare.Save
    i = Worksheets("FormRM_ex").Range("B6").Value
    With formBook.Worksheets("Template")
            Set rs = .Range(.Range("a2"), .Range("p" & i + 1))
    End With
    
    Set rt = wbShare.Sheets("Stock_MR").Range("A1048576").End(xlUp).Offset(1, 0)
   If Worksheets("FormRM_ex").Range("l5") = True Then
        MsgBox "Please check your data. This transaction already recorded."
        Exit Sub
    End If
    If Worksheets("FormRM_ex").Range("l5") = "" Then
        MsgBox "Your data is empty. Fill your data and click record button again."
        Exit Sub
    End If
    rs.Copy: rt.PasteSpecial xlPasteValues
    Sheets("FormRM_ex").Range("k8:l12,n8:n12").ClearContents
       With formBook.Sheets("FormRM_ex")
            .Range("E5") = .Range("E5") + 1
        End With
    Application.CutCopyMode = False
End Sub

Code: Select all

With formBook.Worksheets("Template")
    Set rs = .Range(.Range("A12"), .Range("L12" & i + 1))
End With

Code: Select all

Sub Main_ex()
    Dim formBook As Workbook
    Dim i As Integer
    Dim e As Long
    Dim rs As Range
    Dim rt As Range
    Dim rk As Range
    Dim r As Range
    Set formBook = ThisWorkbook
   Set wbShare = Workbooks("Material_BSh.xlsx")
    With wbShare
           e = .Sheets("Stock_MR").Range("B" & Rows.Count).End(xlUp).Value + 1
          formBook.Worksheets("FormRM_ex").Range("E5").Value = e
    End With
    wbShare.Save
    i = Worksheets("FormRM_ex").Range("B6").Value
    With formBook.Worksheets("Template")
            Set rs = .Range(.Range("A12"), .Range("L12" & i + 1))
    End With

    For Each r In rs.Columns(4).Cells
        If r.Value <> "" Then
            Set rt = wbShare.Sheets("Stock_MR").Range("A1048576").End(xlUp).Offset(1, 0)
            rs.Rows(r.Rows).Copy: rt.PasteSpecial xlPasteValues
        End If
    Next r

    If Worksheets("FormRM_ex").Range("L5") = "" Then
        MsgBox "Your data is empty. Fill your data and click record button again."
        Exit Sub
    End If
   
    Sheets("FormRM_ex").Range("k8:l12,n8:n12").ClearContents
       With formBook.Sheets("FormRM_ex")
            .Range("E5") = .Range("E5") + 1
        End With
    Application.CutCopyMode = False
End Sub

Code: Select all

i = Worksheets("Form").Range("I2").Value

Code: Select all

 i = Worksheets("Form").Range("I2").Value
    With formBook.Worksheets("Template")
            Set rs = .Range("A12:L12").Resize(i + 1)
    End With
    For Each r In rs.Columns(4).Cells
        If r.Value <> "" Then
            Set rt = wbShare.Sheets("Employees").Range("A1048576").End(xlUp).Offset(1, 0)
              rs.Rows(r.Row).Copy: rt.PasteSpecial xlPasteValues
        End If
    Next r

Code: Select all

  i = Worksheets("Form").Range("I2").Value
  With formBook.Worksheets("Template")
      Set rs = .Range("A12:L12").Resize(i + 1)
  End With

Code: Select all

For Each r In rs.Columns(4).Cells
        If r.Value <> "" Then
            Set rt = wbShare.Sheets("Emp").Range("A1048576").End(xlUp).Offset(1, 0)
              rs.Rows(r.Rows).Copy: rt.PasteSpecial xlPasteValues
        End If
    Next r

Code: Select all

rs.Rows(r.Row).Copy: rt.PasteSpecial xlPasteValues

Code: Select all

Sub InEmp()
Application.ScreenUpdating = False
    Dim formBook As Workbook
    Dim i As Integer
    Dim e As Long
    Dim rs As Range
    Dim rt As Range
    Dim rk As Range
    Dim r As Range
    Set formBook = ThisWorkbook
   Set wbShare = Workbooks("Material_BookShare.xlsx")
    With wbShare
           e = .Sheets("Emp").Range("B" & Rows.Count).End(xlUp).Value + 1
          formBook.Worksheets("Form").Range("J5").Value = e
    End With
    wbShare.Save
    i = Worksheets("Form").Range("I2").Value
    With formBook.Worksheets("Template")
            Set rs = .Range("A12:L12").Resize(i + 1)
    End With

    For Each r In rs.Columns(4).Cells
        If r.Value <> "" Then
            Set rt = wbShare.Sheets("Emp").Range("A1048576").End(xlUp).Offset(1, 0)
              rs.Rows(r.Row).Copy: rt.PasteSpecial xlPasteValues
        End If
    Next r

        With formBook.Sheets("Form")
            .Range("J5") = .Range("J5") + 1
        End With
    Application.CutCopyMode = False
End Sub

Code: Select all

With wbShare.Sheets("Emp")
       e = .Range("B" & .Rows.Count).End(xlUp).Row + 1
      formBook.Worksheets("Form").Range("J5").Value = e
End With

wbShare.Save

i = formBook.Worksheets("Form").Range("I2").Value

With formBook.Worksheets("Template")
        Set rs = .Range("A12:L12").Resize(i + 1)
End With

For Each r In rs.Columns(4).Cells
    If r.Value <> "" Then
        Set rt = wbShare.Sheets("Emp").Range("A1048576").End(xlUp).Offset(1, 0)
        rs.Rows(r).Copy: rt.PasteSpecial xlPasteValues
    End If
Next r