Snasui Excel Forum

Code: Select all

With abcdef
   ... <== This place are called inside With...End With
End With

Code: Select all

wbShare.Save 
Application.ScreenUpdating = False
With formBook
        i = .Worksheets("Enterthedata").Range("C224")
End With
With .Worksheets("Template")
        Set rs = .Range(.Range("A2"), .Range("AF" & i + 1))
End With

Code: Select all

Sub MainCode()
Call AutoFilter
Call PasteData
End Sub

Code: Select all

Sub AutoFilter()
       Windows("Ph_BookShare.xlsx").Activate
       ActiveWindow.WindowState = xlNormal
       ActiveWindow.WindowState = xlNormal
       On Error Resume Next
       ActiveSheet.ShowAllData
       On Error GoTo 0
       ActiveWorkbook.Save
End Sub

Code: Select all

Sub PasteData()
        Dim wbShare As Workbook
        Dim formBook As Workbook
        Dim i As Integer
        Dim e As Long
        Dim rs As Range
        Dim rt As Range
        Set formBook = ThisWorkbook
        Set wbShare = Workbooks("Ph_BookShare.xlsx")
        With wbShare
                 e = wbShare.Sheets("Sheet1").Range("e" & Rows.Count).End(xlUp).Value   '
         End With
        wbShare.Save '<== Add this line for refresh shareworkbook
        Application.ScreenUpdating = False
            With formBook
                     i = Worksheets("Enterthedata").Range("C224")
            End With
            With Worksheets("Template")
                    Set rs = .Range(.Range("A2"), .Range("AF" & i + 1))
            End With

        Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        If Worksheets("Enterthedata").Range("C224") = 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
        wbShare.Save '<==Save after record data
        formBook.Save

            Application.CutCopyMode = False
            Sheets("Enterthedata").Range("D2,K2,B204:B219,D204:D219,L204:L219,D221,E221,E204:F219,L204:M219,O204:O219").ClearContents
        With Worksheets("Enterthedata")
                .Range("N1") = .Range("N1") + 1
        End With
End Sub

Code: Select all

Sub Macro6()
    Windows("PoWbShare.xlsx").Activate
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    ActiveSheet.Range("$A$1:$AD$22798").AutoFilter Field:=2
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=3
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=4
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=5
    ActiveSheet.ShowAllData
End Sub

Code: Select all

Sub Macro6()
    Windows("PoWbShare.xlsx").Activate
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    ActiveSheet.Range("$A$1:$AD$22798").AutoFilter Field:=2
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=3
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=4
    ActiveSheet.Range("$A$1:$AD$21798").AutoFilter Field:=5
End Sub

Code: Select all

Sub BeenArL()
    Dim wbShare As Workbook
    Dim wb As Workbook ' declare wb 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")
    For Each wb In Workbooks ' loop wb not loop wdShare
        If wb.Name = "PoWbShare.xlsx" Then
            wdShareOpen = True
        End If
    Next wb
    If Not wdShareOpen Then
        ChDir "\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\AR.ระบบลูกหนี้"
    Workbooks.Open Filename:="\\Server\DATA (E)\My P S  Project.xls\PS.BookShare\AR.ระบบลูกหนี้\PoWbShare.xlsx"
    End If
    Set wdShare = Workbooks("PoWbShare.xlsx") 'set wdShare after open not before open
    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("L11") + .Range("M11") + .Range("M12"))
        If i <> .Range("J12") Then
            MsgBox "Please check your data. This transaction already recorded."
            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
    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
    wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    End With
   With formBook.Sheets("TemBilling")
         .Range("P10:W10").Resize(.Range("Y9")).Copy
        formBook.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    If formBook.Sheets("Report").Range("A34") <> "" Then
                MsgBox "คุณต้องการพิมพ์รายงานหรือไม่"
            Exit Sub
        End If
    End With
       
formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents
With formBook.Sheets("Form")
.Range("N2") = .Range("N2") + 1
End With
Application.ScreenUpdating = True
With Windows("PoWbShare.xlsx").Activate
    ActiveWorkbook.Save
End With
With Windows("ArBookShare.xlsx").Activate
    ActiveWorkbook.Save
End With
With Windows("AR.Form").Activate
    ActiveWorkbook.Save
End With
End Sub

Code: Select all

With formBook.Sheets("TemBilling")
         .Range("P10:W10").Resize(.Range("Y9")).Copy
        formBook.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
        If formBook.Sheets("Report").Range("A34") <> "" Then
                MsgBox "คุณต้องการพิมพ์รายงานหรือไม่"
            Exit Sub
        End If
    End With

Code: Select all

formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents

Code: Select all

With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
     .Offset(1, 0).PasteSpecial xlPasteValues
     End With
If wbShare.Sheets("Report").Range("A35") <> "" Then
         MsgBox "คุณต้องการพิมพ์รายงานหรือไม่"
End If
formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents
With formBook.Sheets("Form")
.Range("N2") = .Range("N2") + 1
End With
Application.ScreenUpdating = True
With Windows("PoWbShare.xlsx").Activate
    ActiveWorkbook.Save
End With
With Windows("ArBookShare.xlsx").Activate
    ActiveWorkbook.Save
End With
With Windows("AR.Form").Activate
    ActiveWorkbook.Save
End With
End Sub

Code: Select all

Sub MainCode()
   Call Code1
   Call Code2
End Sub

Sub Code1()
    MsgBox "Hello."
End Sub

Sub Code2()
    MsgBox "How are you?"
End Sub

Code: Select all

With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
     .Offset(1, 0).PasteSpecial xlPasteValues
     End With
If wbShare.Sheets("Report").Range("A35") <> "" Then
         MsgBox "คุณต้องการพิมพ์รายงานหรือไม่"
End If
formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents
With formBook.Sheets("Form")
.Range("N2") = .Range("N2") + 1
End With
Application.ScreenUpdating = True
Windows("PoWbShare.xlsx").Activate
ActiveWorkbook.Save
Windows("ArBookShare.xlsx").Activate
ActiveWorkbook.Save
Windows("AR.Form").Activate
ActiveWorkbook.Save
End Sub