Excel Forum

Code: Select all

  With formBook
    i = Worksheets("Enterthedata").Range("C225")
    End With
    With Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AC" & 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
    End If

Code: Select all

With formBook
    i = Worksheets("Enterthedata").Range("C225")
    End With
    With Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AC" & i + 1))
    End With
    Set rd = formBook.Sheets("Database").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

Code: Select all

rs.Copy: rt.PasteSpecial xlPasteValues
rs.Copy: rd.PasteSpecial xlPasteValues

Code: Select all

Sub BeenArL()                               
    Dim wbShare As Workbook
    Dim wdShare 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")
    Set wdShare = Workbooks("PoBookShare.xlsx")
    With formBook.Sheets("Form")
        Set rSource = .Range("B3:B50")
    End With
    With formBook.Sheets("Database")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With
    With formBook.Sheets("Form")
        i = (.Range("L9") + .Range("M9") + .Range("M12"))
        If i <> .Range("J12") Then
            MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        End If
    End With
    Application.Calculation = xlCalculationManual
    For Each rs In rSource
        For Each rt In rTarget
            If rt = rs Then rt wdShare.Sheets("Sheet1").Offset(0, 25) = "Y"
        Next rt
    Next rs
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    With formBook.Sheets("TemBilling")
    .Range("a2:p2").Resize(.Range("q1")).Copy
    End With
    wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    End With
    formBook.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    formBook.Sheets("Form").Range("G4:G8,H1,J2,I4:N8,I6").ClearContents
    With formBook.Sheets("Form")
        .Range("J10") = .Range("J10") + 1
    End With
    Application.ScreenUpdating = True
      Windows("AR.Form By Su.xlsm").Activate       
    ActiveWorkbook.Save
    Windows("ArBookShare.xlsx").Activate
    ActiveWorkbook.Save
End Sub

Code: Select all

For Each rs In rSource
        For Each rt In rTarget
            If rt = rs Then rt wdShare.Sheets("Sheet1").Offset(0, 25) = "Y"
        Next rt
    Next rs

Code: Select all

rt wdShare.Sheets("Sheet1").Offset(0, 25) = "Y"

Code: Select all

 With formBook.Sheets("Database")

Code: Select all

With wdShare.Sheets("Sheet1")

Code: Select all

Sub BeenArL()
    Dim wbShare As Workbook
    Dim wdShare As Workbook
    Dim formBook As Workbook
    Dim rSource As Range
    Dim rTarget As Range
    Dim wdOpen As Boolean                         'wbOpen
    Dim rs As Range
    Dim rt As Range
    Dim i As Double
    Set formBook = ThisWorkbook
    Set wbShare = Workbooks("ArBookShare.xlsx")
    Set wdShare = Workbooks("PoBookShare.xlsx")
    
    For Each wdShare In Workbooks                  'wbOpen
        If wdShare.Name = "PoBookShare.xlsx" Then  'wbOpen
            wdOpen = True                          'wbOpen
    Next wd                                        'wbOpen
    If Not wdOpen Then                             'wbOpen
    End If                                         'wbOpen
    
    With formBook.Sheets("Form")
        Set rSource = .Range("B3:B50")
    End With
    With wdShare.Sheets("Sheet1")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With
    With formBook.Sheets("Form")
        i = (.Range("L9") + .Range("M9") + .Range("M12"))
        If i <> .Range("J12") Then
            MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        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
    Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    With formBook.Sheets("TemBilling")
    .Range("a2:p2").Resize(.Range("q1")).Copy
    End With
    wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    End With
    formBook.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    formBook.Sheets("Form").Range("G4:G8,H1,I4:N8,M12").ClearContents
    With formBook.Sheets("Form")
        .Range("J10") = .Range("J10") + 1
    End With
    Application.ScreenUpdating = True
    Windows("AR.Form.xlsm").Activate
        ActiveWorkbook.Save
    Windows("ArBookShare.xlsx").Activate
        ActiveWorkbook.Save
    Windows("PoBookShare.xlsx").Activate
        ActiveWorkbook.Save
   End Sub

Code: Select all

 Set wdShare = Workbooks("PoBookShare.xlsx")

Code: Select all

Dim wbShare As Workbook
    Dim wdShare As Workbook
    Dim formBook As Workbook
    Dim rSource As Range
    Dim rTarget As Range
    Dim wdOpen As Boolean                         'wbOpen
    Dim rs As Range
    Dim rt As Range
    Dim i As Double
    Dim wb As Workbook                              'wbOpen
    Set formBook = ThisWorkbook
    Set wbShare = Workbooks("ArBookShare.xlsx")
    Set wdShare = Workbooks("PoBookShare.xlsx")
    
    For Each wd In Workbooks                       'wbOpen
    Set wd = PoBookShare.Sheets("sheets1").Open    'wbOpen
        If wd.Name = "PoBookShare.xlsx" Then      'wbOpen
            wdOpen = True                          'wbOpen
    Next wd                                         'wbOpen
    If Not wdOpen Then                             'wbOpen
    Workbooks.Open Filename:="\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\PO\PoBookShare.xlsx"
    End If                                         'wbOpen
    

Code: Select all

For Each wd In Workbooks
    If wd.Name = "PoBookShare.xlsx" Then
        wdOpen = True
    End If
Next wd

Code: Select all

If Not wdOpen Then
    Workbooks.Open Filename:="\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\PO\" & wdShare
End If

Code: Select all

Dim wdOpen As Boolean
Dim wd As Workbook

Code: Select all

Dim wd As Workbook

Code: Select all

Dim wdOpen As Boolean

Code: Select all

[code]Sub BeenArL()                                
    Dim wbShare As Workbook
    Dim wdShare As Workbook
    Dim formBook As Workbook
    Dim wdOpen As Boolean
    Dim wd 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")
    Set wdShare = Workbooks("PoBookShare.xlsx")
    For Each wd In Workbooks
    If wd.Name = "PoBookShare.xlsx" Then
        wdOpen = True
    End If
    Next wd
    If Not wdOpen Then
    Workbooks.Open Filename:="\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\PO.ใบส่งสินค้า\PoBookShare.xlsx"
    End If
    

Code: Select all

Sub BeenArL()                                
    Dim wbShare As Workbook
    Dim wdShare As Workbook
    Dim formBook As Workbook
    Dim wdShareOpen As Boolean
    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")
    Set wdShare = Workbooks("PoBookShare.xlsx")
    For Each wdShare In Workbooks
    If wdShare.Name = "PoBookShare.xlsx" Then
        wdShareOpen = True
    End If
    Next wdShare
    If Not wdShareOpen Then
    Workbooks.Open Filename:="\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\PO.ใบส่งสินค้า\PoBookShare.xlsx"
    End If

    With formBook.Sheets("Form")
        Set rSource = .Range("B3:B50")
    End With
    With wdShare.Sheets("Sheet1")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With
    With formBook.Sheets("Form")
        i = (.Range("L9") + .Range("M9") + .Range("M12"))
        If i <> .Range("J12") Then
            MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        End If
    End With