Excel Forum

Code: Select all

Sub PasteData()
Dim i As Integer
Dim rs As Range
Dim rt As Range
    Application.ScreenUpdating = False
    i = Worksheets("Enterthedata").Range("C225")
    With Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AC" & i + 1))
    End With
    Set rt = Worksheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
    If Worksheets("Enterthedata").Range("C225") = True Then
        MsgBox "Please check your data. This transaction already recorded."
        Exit Sub
    End If
    If Worksheets("Enterthedata").Range("B204") = "" Then
        MsgBox "Your data is empty. Fill your data and click record button again."
        Exit Sub
    End If
    rs.Copy: rt.PasteSpecial xlPasteValues

    Application.CutCopyMode = False
    Sheets("Enterthedata").Range("D2,B204:B220,D204:D220,L204:L220,D222, E204:F220").ClearContents
    With Worksheets("Enterthedata")
    If Len(.Range("M2")) = 6 Then
        .Range("M2") = Left(.Range("M2"), 2) & Right(.Range("M2"), 4) + 1
    ElseIf Len(.Range("M2")) = 7 Then
        .Range("M2") = Left(.Range("M2"), 1) & Right(.Range("M2"), 6) + 1
    ElseIf Len(.Range("M2")) = 8 Then
        .Range("M2") = Left(.Range("M2"), 1) & Right(.Range("M2"), 7) + 1
    Else
    .Range("M2") = .Range("M2") + 1
    End If
    End With
    Application.ScreenUpdating = True
End Sub

Code: Select all

Sub PasteData()
Dim wbShare As Workbook
Dim formBook As Workbook
Dim i As Integer
Dim rs As Range
Dim rt As Range
Set formBook = ThisWorkbook
Set wbShare = Workbooks("BookShare.xlsx")
    Application.ScreenUpdating = False
    i = Worksheets("Enterthedata").Range("C225")
    With Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AB" & i + 1))
    End With
   wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    If Worksheets("Enterthedata").Range("C225") = True Then
        MsgBox "Please check your data. This transaction already recorded."
        Exit Sub
    End If
    If Worksheets("Enterthedata").Range("B204") = "" Then
        MsgBox "Your data is empty. Fill your data and click record button again."
        Exit Sub
    End If
    rs.Copy: rt.PasteSpecial xlPasteValues

    Application.CutCopyMode = False
    Sheets("Enterthedata").Range("D2,B204:B220,D204:D220,L204:L220,D222, E204:F220").ClearContents
    With Worksheets("Enterthedata")
    If Len(.Range("M2")) = 6 Then
        .Range("M2") = Left(.Range("M2"), 2) & Right(.Range("M2"), 4) + 1
    ElseIf Len(.Range("M2")) = 7 Then
        .Range("M2") = Left(.Range("M2"), 1) & Right(.Range("M2"), 6) + 1
    Else
    End If
    End With
    Application.ScreenUpdating = True
End Sub

Code: Select all

i = Worksheets("Enterthedata").Range("C225")

Code: Select all

With Formbook
    i = .Worksheets("Enterthedata").Range("C225").Value
End With

Code: Select all

i = Formbook.Worksheets("Enterthedata").Range("C225").Value

Code: Select all

Sub PasteData()
Dim wbShare As Workbook
Dim formBook As Workbook
Dim i As Integer
Dim rs As Range
Dim rt As Range
Set formBook = ThisWorkbook
Set wbShare = Workbooks("BookShare.xlsx")
    Application.ScreenUpdating = False
    With formBook
    i = Worksheets("Enterthedata").Range("C225")
    End With
    With Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AB" & i + 1))
    End With
    Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    If Worksheets("Enterthedata").Range("C225") = True Then
        MsgBox "Please check your data. This transaction already recorded."
        Exit Sub
    End If
    If Worksheets("Enterthedata").Range("B204") = "" Then
        MsgBox "Your data is empty. Fill your data and click record button again."
        Exit Sub
    End If
    rs.Copy: rt.PasteSpecial xlPasteValues

    Application.CutCopyMode = False
    Sheets("Enterthedata").Range("D2,B204:B220,D204:D220,L204:L220,D222, E204:F220").ClearContents
    With Worksheets("Enterthedata")
    If Len(.Range("M2")) = 6 Then
        .Range("M2") = Left(.Range("M2"), 2) & Right(.Range("M2"), 4) + 1
    ElseIf Len(.Range("M2")) = 7 Then
        .Range("M2") = Left(.Range("M2"), 1) & Right(.Range("M2"), 6) + 1
    Else
    End If
    End With
    Application.ScreenUpdating = True
End Sub

Code: Select all

Sub BeenArL()                           ' ปุ่มบันทึกรับชำระ ชีท Form
Dim wbShare As Workbook
Dim formBook As Workbook
    Dim rSource As Range
    Dim rTarget As Range
    Dim rs As Range
    Dim rt As Range
    Dim i As Double
Set formBook = ThisWorkbook
Set wbShare = Workbooks("ArBookShare.xlsx")
    With formBook("Form")
        Set rSource = .Range("B3:B47")
    End With
    With formBook("Database")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With
    With ActiveSheet
     i = (.Range("L4") + .Range("L6"))
    If i <> .Range("J8") Then
        MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        Exit Sub
    End If
    End With
    With formBook("Form")                  
        If .Range("K6") = "" Then
            MsgBox "โปรดใส่รหัสจ่ายแล้วบันทึกใหม่"
             Exit Sub
        End If
        End With
    Application.Calculation = xlCalculationManual
    For Each rs In rSource
        For Each rt In rTarget
            If rt = rs Then rt.Offset(0, 25) = "Y"
        Next rt
    Next rs
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Sheets("TemBilling").Range("A12:O12").Copy
     Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("TemBilling").Range("P12:W12").Copy
    Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Form").Range("H1,J2,I4:L4,L6,K6,G4").ClearContents
   With formBook("Form")
            .Range("J6") = .Range("J6") + 1
        End With
        Application.ScreenUpdating = True
End Sub

Code: Select all

Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0)
Sheets("TemBilling").Range("A12:O12").Copy
rt.PasteSpecial xlPasteValues

Code: Select all

Sub BeenArL()                                '  ปุ่มบันทึกรับชำระ ชีท Formรับชำระ
Dim wbShare As Workbook
Dim formBook As Workbook
    Dim rSource As Range
    Dim rTarget As Range
    Dim rs As Range
    Dim rt As Range
    Dim i As Double
Set formBook = ThisWorkbook
Set wbShare = Workbooks("ArBookShare.xlsx")
    With Sheets("Form")
        Set rSource = .Range("B3:B50")
    End With
    With Sheets("Database")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With
    With ActiveSheet
     i = (.Range("L9") + .Range("M9") + .Range("M12"))
    If i <> .Range("J12") Then
        MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        Exit Sub
    End If
    End With
    Application.Calculation = xlCalculationManual
    For Each rs In rSource
        For Each rt In rTarget
            If rt = rs Then rt.Offset(0, 25) = "Y"
        Next rt
    Next rs
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    With Workbooks
  Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End With
    With Worksheets("TemBilling")
    Set rSource = .Range("A2:P2").Resize(.Range("Q1"))
    End With
    Sheets("TemBilling").Range("A2:P6").Copy
    wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
With Worksheets("TemBilling")
    Set rSource = .Range("P10:W10").Resize(.Range("Y9"))
End With
    Sheets("TemBilling").Range("P10:W14").Copy
    Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Form").Range("G4:G8,H1,J2,I4:N8,I6").ClearContents
    With Sheets("Form")
            .Range("J10") = .Range("J10") + 1
        End With
        Application.ScreenUpdating = True
End Sub

Code: Select all

With Worksheets("TemBilling")
    Set rSource = .Range("A2:P2").Resize(.Range("Q1"))
    End With
    Sheets("TemBilling").Range("A2:P6").Copy
    wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    With Worksheets("TemBilling")
    Set rSource = .Range("P10:W10").Resize(.Range("Y9"))
    End With
    Sheets("TemBilling").Range("P10:W14").Copy
    Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues