Snasui Excel Forum

Code: Select all

With wbShare
       Set shRange = .Sheets("Sheet1").Range("A1", .Range("Z" & Rows.Count).End(xlUp))
       shRange.Borders.LineStyle = xlContinuous
       shRange.Sort Key1:=.Sheets("Sheet1").Range("D2"), Order1:=xlAscending, Header:=xlGuess
End With

Code: Select all

Sub PasteData()
        Dim wbShare As Workbook
        Dim formBook As Workbook
        Dim rTarget As Range
        Dim i As Integer
        Dim E As Long
        Dim rs As Range
        Dim rt As Range
        Dim rd As Range
        Dim shRange As Range

        Set formBook = ThisWorkbook
        Set wbShare = Workbooks("DB.xlsx")
        On Error Resume Next
        wbShare.Sheets("Sheet1").ShowAllData
        With wbShare
                E = .Sheets("Sheet1").Range("f" & Rows.Count).End(xlUp).Value + 1
                Select Case formBook.Sheets("Form").Range("o2").Value
                        Case "ใบกำกับภาษี"
                            E = formBook.Sheets("Sheet1").Range("c2")
                        Case "ใบส่งสินค้าชั่วคราว"
                            E = formBook.Sheets("Sheet1").Range("c3")
                End Select
                formBook.Worksheets("Form").Range("m2").Value = E
        End With
       
         wbShare.Save
       
        With formBook
                i = .Worksheets("Form").Range("C225").Value
        End With
        With formBook.Worksheets("Template")
                Set rs = .Range(.Range("A2"), .Range("Z" & i + 1))
        End With
                Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                        If formBook.Worksheets("Form").Range("C225") = True Then
                        End If
            With wbShare
                    Set shRange = .Sheets("Sheet1").Range("A1", .Range("Z" & Rows.Count).End(xlUp))
                    shRange.Borders.LineStyle = xlContinuous
                    shRange.Sort Key1:=.Sheets("Sheet1").Range("D2"), Order1:=xlAscending, Header:=xlGuess
           End With
      
        If formBook.Worksheets("Form").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
            formBook.Save
            formBook.Activate
            formBook.Sheets("Form").Range("D2,B204:B220,D204:D220,D222, E204:F220,O204,N204:N220").ClearContents
End Sub

Code: Select all

With wbShare
       Set shRange = .Sheets("Sheet1").Range("A1", .Range("Z" & Rows.Count).End(xlUp))
       shRange.Borders.LineStyle = xlContinuous
       .Soft.SortFields.Clear
'เกณฑ์ที่สำคัญอันดับ 1
       .Sort.SortFields.Add Key:=.Range("a1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'เกณฑ์ที่สำคัญอันดับ 2
       .Sort.SortFields.Add Key:=.Range("d1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       With .Sort
           .SetRange shRange
           .Header = xlYes
           .SortMethod = xlPinYin
           .MatchCase = False
           .Apply
       End With
End With

Code: Select all

   With wbShare.Sheets(1)
        Set shRange = .Range("a1", .Range("z" & .Rows.Count).End(xlUp))
        shRange.Borders.LineStyle = xlContinuous
       .Sort.SortFields.Clear
'เกณฑ์ที่สำคัญอันดับ 1
       .Sort.SortFields.Add Key:=.Range("a1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'เกณฑ์ที่สำคัญอันดับ 2
       .Sort.SortFields.Add Key:=.Range("d1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       With .Sort
           .SetRange shRange
           .Header = xlYes
           .SortMethod = xlPinYin
           .MatchCase = False
           .Apply
       End With
    End With

Code: Select all

With wbShare.Sheets(1)
                Set shRange = .Range("b1", .Range("ad" & .Rows.Count).End(xlUp))
                shRange.Borders.LineStyle = xlContinuous
               .Sort.SortFields.Clear
        'เกณฑ์ที่สำคัญอันดับ 1
               .Sort.SortFields.Add Key:=.Range("b1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'เกณฑ์ที่สำคัญอันดับ 2
               .Sort.SortFields.Add Key:=.Range("f1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               With .Sort
                   .SetRange shRange
                   .Header = xlYes
                   .SortMethod = xlPinYin
                   .MatchCase = False
                   .Apply
               End With
            End With

Code: Select all

'...
dim lastRow as long
with sheets(1)
    lastRow = .range("b" & .rows.count).end(xlup).row
    Set shRange = .Range("b1:ad" & lastRow)
end with
'...

Code: Select all

With wbShare.Sheets(1)
                lastRow = .Range("b" & .Rows.Count).End(xlUp).Row
                Set shRange = .Range("b1:ad" & lastRow)
                shRange.Borders.LineStyle = xlContinuous
               .Sort.SortFields.Clear
        'เกณฑ์ที่สำคัญอันดับ 1
               .Sort.SortFields.Add Key:=.Range("b1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'เกณฑ์ที่สำคัญอันดับ 2
               .Sort.SortFields.Add Key:=.Range("f1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               With .Sort
                   .SetRange shRange
                   .Header = xlYes
                   .SortMethod = xlPinYin
                   .MatchCase = False
                   .Apply
               End With
            End With

Code: Select all

shRange.Borders.LineStyle = xlContinuous 

Code: Select all

shRange.Borders.LineStyle = xlNone