Snasui Excel Forum

Code: Select all

With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    End With
    wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents
    With formBook.Sheets("Form")
        .Range("N2") = .Range("N2") + 1
    End With

Code: Select all

Sub Macro1()
  Range("A16:H16").Select
  Application.CutCopyMode = False
  Selection.Copy
  Sheets("Sheet2").Select
  Range("A35").Select
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
   xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  ActiveWindow.SelectedSheets.PrintPreview
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 "โปรดตรวจจำนวนเงินและบันทึกใหม่"
            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
    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
    wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    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.xlsm").Activate
    ActiveWorkbook.Save
    End Sub

Code: Select all

With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Select
  Application.CutCopyMode = False
  Selection.Copy
   wbShare.Sheets("Report").Select
  Range("A35").Select
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
   xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  ActiveWindow.SelectedSheets.PrintPreview
    End With

Code: Select all

Application.Goto Workboooks(1).Worksheets(1).Range("G44")

Code: Select all

With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    wbShare.Sheets("Report").Range("A35").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintPreview
End With

Code: Select all

 With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    wbShare.Sheets("Report").Range("A35").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintPreview
    End With
    wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    formBook.Sheets("Form").Range("G4:G10,H1,I4:N10,M12").ClearContents
    With formBook.Sheets("Form")
        .Range("N2") = .Range("N2") + 1
    End With

Code: Select all

 With formBook.Sheets("TemBilling")
    .Range("P10:W10").Resize(.Range("Y9")).Copy
    wbShare.Sheets("Report").Range("A35").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SelectedSheets.PrintPreview
    End With

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
    wbShare.Sheets("Report").Range("A35").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
   xlNone, SkipBlanks:=False, Transpose:=False
   wbShare.Sheets("Report").Range("a1:h35").PrintPreview
    End With

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
         If wbShare.Sheets("Report").Range("A35") <> "" Then
    wbShare.Sheets("Report").Range("a1:h35").PrintPreview
    End If
   End With

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
         If wbShare.Sheets("Report").Range("A35") <> "" Then
    Windows("ArBookShare.xlsx").Activate
    End If
   End With

Code: Select all

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.FormBySu").Activate
    ActiveWorkbook.Save
    End Sub