Excel Forum

Code: Select all

'Other code
With wbShare
        'e = .Sheets("Sheet1").Range("e" & Rows.Count).End(xlUp).Value + 1
        Select Case formBook.Sheets("EnterThedata").Range("o2").Value
            Case "ใบกำกับภาษี"
                e = formBook.Sheets("Document").Range("c2")
            Case "ใบส่งสินค้าชั่วคราว"
                e = formBook.Sheets("Document").Range("c3")
            Case "ใบลดหนี้"
                e = formBook.Sheets("Document").Range("c4")
        End Select
        formBook.Worksheets("Enterthedata").Range("m2").Value = e
'                With wbShare
'                    o = .Sheets("Sheet1").Range("d" & Rows.Count).End(xlUp) = formBook.Worksheets("Enterthedata").Range("n2")
'                End With
End With
'Other code

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
        Set formBook = ThisWorkbook
        Set wbShare = Workbooks("PoWbShare.xlsx")
         Application.ScreenUpdating = False
        With wbShare
                e = .Sheets("Sheet1").Range("f" & Rows.Count).End(xlUp).Value + 1
                Select Case formBook.Sheets("EnterThedata").Range("o2").Value
                        Case "ใบกำกับภาษี"
                            e = formBook.Sheets("Document").Range("c2")
                        Case "ใบส่งสินค้าชั่วคราว"
                            e = formBook.Sheets("Document").Range("c3")
                        Case "ใบลดหนี้"
                            e = formBook.Sheets("Document").Range("c4")
                End Select
                formBook.Worksheets("Enterthedata").Range("m2").Value = e
        End With
        
        With formBook
                i = .Worksheets("Enterthedata").Range("C225").Value
        End With
         wbShare.Save
        With formBook
                i = .Worksheets("Enterthedata").Range("C225").Value
        End With
        With formBook.Worksheets("Template")
                Set rs = .Range(.Range("A2"), .Range("AD" & i + 1))
        End With
                Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                        If formBook.Worksheets("Enterthedata").Range("C225") = True Then
                        End If
        With formBook
                i = formBook.Sheets("Enterthedata").Range("C225").Value
        End With
        
        If formBook.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
            formBook.Save
            formBook.Activate
            Range("D2").Select
            Application.CutCopyMode = False
            formBook.Sheets("Enterthedata").Range("D2,B204:B220,D204:D220,D222, E204:F220,O204,N204:N220").ClearContents
            Application.ScreenUpdating = True
End Sub

Code: Select all

Sub MainCode()
Call AutoFilter
Call PasteData
End Sub

Code: Select all

Sub AutoFilter()
                Windows("PoWbShare.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 MainCode()
Application.ScreenUpdating = False
Call AutoFilter
Call PasteData
Application.ScreenUpdating = True
End Sub