Issues with Excel 2013

H

Hawk3116

New Member
Joined
Jun 16, 2014
Messages
15
My company just "upgraded" to office 2013 and now one of my macros that ran perfectly in 2010 now seems to have a handful of issues.
1.It runs at a fraction of the speed ,2010- ~15 seconds per account vs 2013 ~ 1 minute per account.I have already tried disabling Animate controls and elements inside Windows and it helps but it was minimal.
2. After the macro runs excel sometimes will start to run super slow or it won't allow you to select a cell even in other workbooks.
3.I had to take out screen updating as it really seems to mess with the macro .
4. last it will randomly just completely crash.
Like I said this only became an issue when we moved to 2013 does anyone see any issues or know any common problems people are having when they make the jump ?

PHP: 
Sub MultipleTransaction(rowcount, SapGuiAuto, Connection, session, rown)

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Balance forward").Visible = True
Worksheets("Debit Adjustment").Visible = True
Worksheets("Invoice").Visible = True
Worksheets("Invoice2").Visible = True
Worksheets("TXU").Visible = True


    Sheets("Balance forward").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Clear
    Sheets("Debit Adjustment").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Clear
    Range("A2").Select
    Sheets("Invoice").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Clear
    Range("A2").Select
    Sheets("Invoice2").Select
    Cells.Select
        Selection.Clear
  

Sheets("Client").Select

 Range("D1").FormulaR1C1 = "a"
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Clear
   
Sheets("ScriptSheet").Select
If rowcount = 2 Then

 Range("C2").FormulaR1C1 = "=RC[-1]*1"
    If rown > 2 Then
    Range("C2").AutoFill Destination:=Range("C2:C" & rown)
    End If
    Range("C2:C" & rown).Copy
    Range("B2").PasteSpecial Paste:=xlPasteValues
Range("c2:c" & rown).Select
    Selection.Clear
Else
End If

Range(Cells(rowcount, 2), Cells(rowcount, 2)).Select
ca = ActiveCell.Value
Length = Len(ActiveCell)
Range(Cells(rowcount, 5), Cells(rowcount, 5)).Select
Contract = ActiveCell.Value
Range("d2").Select
docs = ActiveCell.Value
Range("g2").Select
Formats = ActiveCell.Value



If Not IsEmpty(ca) Then


Dim ws As Worksheet
    Dim ret As Boolean
    ret = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = ca Then
           Range(Cells(rowcount, 6), Cells(rowcount, 6)).Value = "Already Done"
           Exit Sub
        End If
    Next


Dim newsheet
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
newsheet.Name = ca
Dim strSheetName As String
strSheetName = ActiveSheet.Name


session.FindById("wnd[0]").maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nfpl9"
session.FindById("wnd[0]").SendVKey 0
If Length = 10 Then
session.FindById("wnd[0]/usr/ctxtFKKL1-VTREF").Text = ca
session.FindById("wnd[0]/usr/ctxtFKKL1-VKONT").Text = ""
session.FindById("wnd[0]/usr/ctxtFKKL1-GPART").Text = ""
ElseIf Length < 11 Then
session.FindById("wnd[0]/usr/ctxtFKKL1-VTREF").Text = ""
session.FindById("wnd[0]/usr/ctxtFKKL1-VKONT").Text = ""
session.FindById("wnd[0]/usr/ctxtFKKL1-GPART").Text = ca
Else
session.FindById("wnd[0]/usr/ctxtFKKL1-VTREF").Text = ""
session.FindById("wnd[0]/usr/ctxtFKKL1-VKONT").Text = ca
session.FindById("wnd[0]/usr/ctxtFKKL1-GPART").Text = ""
End If
SectionContract:
session.FindById("wnd[0]").SendVKey 0
If session.Children.Count > 1 Then
 session.FindById("wnd[1]/tbar[0]/btn[0]").Press
 End If
 
Bp = session.FindById("wnd[0]/usr/ctxtFKKL1-GPART").Text
Joker = session.FindById("wnd[0]/sbar").Text
If Joker Like "No items*" Then
Sheets(strSheetName).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("ScriptSheet").Select
Range(Cells(rowcount, 6), Cells(rowcount, 6)).FormulaR1C1 = "No Records"
Worksheets("Balance forward").Visible = False
Worksheets("Debit Adjustment").Visible = False
Worksheets("Invoice").Visible = False
Worksheets("Invoice2").Visible = False
Worksheets("TXU").Visible = False
Exit Sub
    End If

session.FindById("wnd[0]/usr/tabsTABSTRIP01/tabpTAB05").Select
session.FindById("wnd[0]/usr/tabsTABSTRIP01/tabpTAB05/ssubSUBAREA1:SAPLFKL9:0315/cntlGRID_0315/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
session.FindById("wnd[0]/usr/tabsTABSTRIP01/tabpTAB05/ssubSUBAREA1:SAPLFKL9:0315/cntlGRID_0315/shellcont/shell").SelectContextMenuItem "&PC"
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.FindById("wnd[1]/tbar[0]/btn[0]").Press




Range("A1").Select
ActiveCell.PasteSpecial
Range("A1").Select
If ActiveCell.Value = "" Then
  Sheets(strSheetName).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("ScriptSheet").Select
Range(Cells(rowcount, 3), Cells(rowcount, 3)).Select
    ActiveCell.FormulaR1C1 = "No Records"
 GoTo Complete
Else
End If


  Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:= _
        True
   Rows("1:2").Delete Shift:=xlUp
   Rows("2:2").Delete Shift:=xlUp
    Columns("A:A").Delete Shift:=xlToLeft

  Range("A1").End(xlDown).Select
endofrow = ActiveCell.Row
  Range("A1").End(xlDown).Select
endofrow2 = ActiveCell.Row

    Columns("N:N").Delete Shift:=xlToLeft
    Columns("M:M").Delete Shift:=xlToLeft
    Columns("L:L").Delete Shift:=xlToLeft
    Columns("K:K").Delete Shift:=xlToLeft
    Columns("J:J").Delete Shift:=xlToLeft
    Columns("G:G").Delete Shift:=xlToLeft
    Columns("F:F").Delete Shift:=xlToLeft
    Columns("B:B").Delete Shift:=xlToLeft
     
     
     Range("A2:A" & endofrow).NumberFormat = "0"
 
    Range("d2").Select
    Do
If ActiveCell.Value Like "Transfer Open Item*" Then
ActiveCell.Offset(0, -3).Copy
Sheets("Balance forward").Select
    If Range("a2").Value = "" Then
Range("A2").Select
ActiveCell.Value = ca
Range("b2").Select
ActiveCell.PasteSpecial
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
    
    ElseIf Range("a2").Value > "0" Then
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = ca
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
End If
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = vbNullString
Sheets("Balance forward").Select


                        If Range("a2").Value <> "" Then
                                  
Sheets("Balance forward").Select

Range("A1").Select
Selection.End(xlDown).Select
rown1 = ActiveCell.Row
rowcount1 = 2
While rowcount1 <= rown1
Range(Cells(rowcount1, 1), Cells(rowcount1, 1)).Select
Contractnumber = ActiveCell.Value
Range(Cells(rowcount1, 2), Cells(rowcount1, 2)).Select
Doc = ActiveCell.Value
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nfpe3"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/ctxtRFPE1-OPBEL").Text = Doc
session.FindById("wnd[0]/tbar[0]/btn[0]").Press
   
   
 If Not session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_AG/ctxtFPE3_AG-VKONT[4,0]", False) Is Nothing Then
 Range(Cells(rowcount1, 3), Cells(rowcount1, 3)).Value = session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_AG/ctxtFPE3_AG-VKONT[4,0]").Text
 End If
  If Not session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_OP/ctxtFPE3_OP-VKONT[4,0]", False) Is Nothing Then
Range(Cells(rowcount1, 4), Cells(rowcount1, 4)).Value = session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_OP/ctxtFPE3_OP-VKONT[4,0]").Text
  End If
  
  If Not session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_OP/txtFPE3_OP-ANZBT[10,0]", False) Is Nothing Then
Range(Cells(rowcount1, 6), Cells(rowcount1, 6)).Value = session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_OP/txtFPE3_OP-ANZBT[10,0]").Text
 End If
  
  
  If Not session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_AG/txtFPE3_AG-ANZBT[10,0]", False) Is Nothing Then
Range(Cells(rowcount1, 7), Cells(rowcount1, 7)).Value = session.FindById("wnd[0]/usr/tblSAPLFKPSCTRL_0700_AG/txtFPE3_AG-ANZBT[10,0]").Text
 End If
 
 

rowcount1 = rowcount1 + 1
Wend
Range("A1").Select
Selection.End(xlDown).Select
endofrow = ActiveCell.Row
Range("C2:d" & endofrow).NumberFormat = "0"
Range("H2").FormulaR1C1 = "=IF(RC[-5]=RC[-7],RC[-4],RC[-5])"
Range("H2").Select
        
        If endofrow > 2 Then
        Selection.AutoFill Destination:=Range("H2:H" & endofrow)
        End If
        
Range("H2:H" & endofrow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "0"
Range("H2:H" & endofrow).Copy
Range("C2").Select
ActiveSheet.Paste
Range("D2").FormulaR1C1 = _
"=IF(RC[1]>0,CONCATENATE(""Transfer from account "",RC[-1]),CONCATENATE(""Transfer to account "",RC[-1]))"
    
    If endofrow > 2 Then
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & endofrow)
    Else
    End If

Range("a1:g" & endofrow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Sheets(strSheetName).Select
Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
Selection.End(xlDown).Select
endofrow2 = ActiveCell.Row
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'Balance forward'!C[-4]:C[-2],3,FALSE)"
        
If endofrow2 > 2 Then
Selection.AutoFill Destination:=Range("F2:F" & endofrow2)
Else
End If

Range("F2:F" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(RC[2]),RC[1],IF(AND(ISNUMBER(SEARCH(""to"",RC[2])),RC[3]>0),SUBSTITUTE(RC[2],""to"",""from""),RC[2]))"
    
    If endofrow2 > 2 Then
    Selection.AutoFill Destination:=Range("D2:D" & endofrow2)
    Else
    End If

Range("A1:H" & endofrow2).Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("E:F").Delete Shift:=xlToLeft
 
                        Else
                        Sheets(strSheetName).Select
                        End If

'------------------------------------------ DEBIT Adjustment Add Start

Cells.Replace What:="Manual Posting*", Replacement:="Adjustment", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("D2").Select
  
Do

    If ActiveCell.Value Like "Adjustment*" Then
ActiveCell.Offset(0, -3).Copy
Sheets("Debit Adjustment").Select

        If Range("a2").Value = "" Then
Range("A2").Value = ca
Range("B2").PasteSpecial
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
 
        ElseIf Range("a2").Value > "0" Then
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = ca
ActiveCell.Offset(0, 1).PasteSpecial
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
        End If
    
    Else
ActiveCell.Offset(1, 0).Select
    End If
    
    
Loop Until ActiveCell = vbNullString

Sheets("Debit Adjustment").Select

                If Range("a2").Value <> "" Then

Range("A1").Select
Selection.End(xlDown).Select
Rown2 = ActiveCell.Row
session.FindById("wnd[0]").maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nzse16n"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/ctxtGD-TAB").Text = "dfkkop"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/mbar/menu[2]/menu[0]/menu[0]").Select
session.FindById("wnd[1]/usr/ctxtGS_SE16N_LT-NAME").Text = "ASG"
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").Text = "XRN7"
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").SetFocus
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").CaretPosition = 4
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").SetFocus
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").Press
Range("b1:B" & Rown2).Select
Selection.Copy
Application.Wait Now + TimeValue("00:00:01")
session.FindById("wnd[1]/tbar[0]/btn[24]").Press
session.FindById("wnd[1]/tbar[0]/btn[8]").Press
session.FindById("wnd[0]/tbar[1]/btn[8]").Press
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").SelectContextMenuItem "&PC"
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
Sheets("Invoice2").Select
Range("E1").FormulaR1C1 = "a"
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.Clear
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, _
1)), TrailingMinusNumbers:=True
Rows("1:3").Delete Shift:=xlUp
Rows("2:2").Delete Shift:=xlUp
Columns("A:A").Delete Shift:=xlToLeft
Range("A1").Select
Selection.End(xlDown).Select
rown3 = ActiveCell.Row
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TRIM(CONCATENATE(RC[-2],RC[-1]))"

    If rown3 > 2 Then
    Selection.AutoFill Destination:=Range("D2:D" & rown3)
    End If
    
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Transactions!C[-4]:C[-3],2,FALSE)"
        
    If rown3 > 2 Then
    Selection.AutoFill Destination:=Range("E2:E" & rown3)
    End If
        
Sheets(strSheetName).Select
Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'Invoice2'!C[-5]:C[-1],5,FALSE)"
        
    If endofrow2 > 2 Then
    Selection.AutoFill Destination:=Range("F2:F" & endofrow2)
    End If
    
Range("F2:F" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(RC[2]),RC[1],RC[2])"

If endofrow2 > 2 Then
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & endofrow2)
End If

Range("d2:d" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("E:F").Delete Shift:=xlToLeft
Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select

                Else
                Sheets(strSheetName).Select
                End If
'''''''''''''''''''' Getting Return Reasons.
Range("D2").Select
Do Until ActiveCell = vbNullString

    If ActiveCell.Value Like "Returns*" Then
ActiveCell.Offset(0, -3).Copy
Sheets("Invoice2").Select
Range("A1").Select
        
        If ActiveCell = vbNullString Then
ActiveSheet.Paste
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
        Else
Range("a20000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets(strSheetName).Select
ActiveCell.Offset(1, 0).Select
        End If
        
    Else
    ActiveCell.Offset(1, 0).Select
    End If

Loop

Sheets("Invoice2").Select

    If Not IsEmpty(Range("a1").Value) Then
   
Range("A1").Select
session.FindById("wnd[0]").maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nzse16n"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/ctxtGD-TAB").Text = "dfkkrp"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").Text = "9999"
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").SetFocus
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").CaretPosition = 4
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]").SendVKey 71
session.FindById("wnd[1]/usr/sub:SAPLSPO4:0300/txtSVALD-VALUE[0,21]").Text = "rlbel"
session.FindById("wnd[1]/usr/sub:SAPLSPO4:0300/txtSVALD-VALUE[0,21]").CaretPosition = 5
session.FindById("wnd[1]").SendVKey 0
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,0]").SetFocus
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,0]").Press

        If Range("A2").Value = vbNullString Then
        Range("A1").Copy
        Else
        Range("a1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        End If
        
session.FindById("wnd[1]/tbar[0]/btn[24]").Press
session.FindById("wnd[1]/tbar[0]/btn[8]").Press
session.FindById("wnd[0]/tbar[1]/btn[8]").Press
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").SelectContextMenuItem "&PC"
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Range("A1").Value = "A"
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("A:A").Clear
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
Columns("A:I").Delete Shift:=xlToLeft
Rows("1:1").Delete Shift:=xlUp
Rows("2:2").Delete Shift:=xlUp
Range("A1").End(xlDown).Select
endofrow = ActiveCell.Row
Range("C1").Select
Sheets(strSheetName).Select
Columns("E:f").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Invoice2!C[-4]:C[-3],2,FALSE)"
   
    If endofrow2 > 2 Then
    Selection.AutoFill Destination:=Range("E2:E" & endofrow2)
    End If
    
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(RC[-1]),RC[-2],TRIM(CONCATENATE(RC[-2],""- "",RC[-1])))"

    If endofrow2 > 2 Then
    Selection.AutoFill Destination:=Range("F2:F" & endofrow2)
    End If

Range("F2:F" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("D:E").Delete Shift:=xlToLeft
Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select

    Else
    End If

    If Length > 11 Then

Sheets("ScriptSheet").Select
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/NzSE16N"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/ctxtGD-TAB").Text = "ZESIID"
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").Text = "9999"
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").SetFocus
session.FindById("wnd[0]/usr/txtGD-MAX_LINES").CaretPosition = 4
session.FindById("wnd[0]").SendVKey 0

If Length < 11 Then
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,1]").Text = ca
Else
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,2]").Text = ca
End If

If session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnOPTION[1,10]", False) Is Nothing Then
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC").VerticalScrollbar.Position = 1
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC").VerticalScrollbar.Position = 2

Else
End If





session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnOPTION[1,10]").SetFocus
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnOPTION[1,10]").Press
session.FindById("wnd[1]/usr/cntlGRID/shellcont/shell").CurrentCellRow = 11
session.FindById("wnd[1]/usr/cntlGRID/shellcont/shell").SelectedRows = "11"
session.FindById("wnd[1]/usr/cntlGRID/shellcont/shell").DoubleClickCurrentCell
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC").Columns.ElementAt(0).Width = 20
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,10]").Text = "*VPOD*"
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,10]").SetFocus
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,10]").CaretPosition = 6
session.FindById("wnd[0]/tbar[1]/btn[8]").Press
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").SelectContextMenuItem "&PC"
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
Sheets("Invoice2").Select
Range("D1").FormulaR1C1 = "a"
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.Clear
Range("a1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 2), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
Selection.Delete Shift:=xlToLeft
Rows("1:3").Delete Shift:=xlUp
Columns("J:J").Select
ActiveSheet.Range("$J$1:$J$12200").RemoveDuplicates Columns:=1, Header:=xlNo
Sheets("Invoice2").Select
Range("j2").Select

        If ActiveCell.Value = "" Then
        Sheets("ScriptSheet").Select
        Range(Cells(rowcount, 3), Cells(rowcount, 3)).Value = "One to One"
        Else
        Sheets("ScriptSheet").Select
        Range(Cells(rowcount, 3), Cells(rowcount, 3)).Value = "Multi"
        End If
        
    Else

Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select
Range("d2").Select
Cells.Replace What:="IS-U Invoicing*", Replacement:="Invoice", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
GoTo SectionInvoice
End If
  
Sheets("ScriptSheet").Select
bills = Range(Cells(rowcount, 3), Cells(rowcount, 3)).Value

Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select
Range("d2").Select
Cells.Replace What:="IS-U Invoicing*", Replacement:="Invoice", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

SectionInvoice:
        
        Do
    If ActiveCell.Value Like "Invoice*" Then
ActiveCell.Offset(0, -3).Copy
Sheets("Invoice").Select

            If Range("a2").Value = "" Then
            Range("A2").Select
            ActiveSheet.Paste
            Sheets(strSheetName).Select
            ActiveCell.Offset(1, 0).Select
            Else
            Range("A1").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
            Sheets(strSheetName).Select
            ActiveCell.Offset(1, 0).Select
            End If

    Else
    ActiveCell.Offset(1, 0).Select
    End If

Loop Until ActiveCell = vbNullString

Sheets("Invoice").Select

        If Range("a2").Value <> "" Then
   
Sheets("Invoice").Select
Range("A1").End(xlDown).Select
endofrow5 = ActiveCell.Row
session.FindById("wnd[0]").maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nzse16n"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/ctxtGD-TAB").Text = "Dfkkop"
session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/mbar/menu[2]/menu[0]/menu[0]").Select
session.FindById("wnd[1]/usr/ctxtGS_SE16N_LT-NAME").Text = "ASG"
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").Text = "XRN7"
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").SetFocus
session.FindById("wnd[1]/usr/txtGS_SE16N_LT-UNAME").CaretPosition = 4
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").SetFocus
session.FindById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").Press
Range("A2:a" & endofrow5).Select
Selection.Copy
Application.Wait Now + TimeValue("00:00:01")
session.FindById("wnd[1]/tbar[0]/btn[24]").Press
session.FindById("wnd[1]/tbar[0]/btn[8]").Press
session.FindById("wnd[0]/tbar[1]/btn[8]").Press
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").PressToolbarContextButton "&MB_EXPORT"
session.FindById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").SelectContextMenuItem "&PC"
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
session.FindById("wnd[1]/tbar[0]/btn[0]").Press

Range("D1").FormulaR1C1 = "a"
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Cells.Select
Selection.Clear
Range("a1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").Delete Shift:=xlToLeft
Columns("B:c").Delete Shift:=xlToLeft
Rows("1:3").Delete Shift:=xlUp
Rows("2:2").Delete Shift:=xlUp
Range("A1").End(xlDown).Select
endofrow5 = ActiveCell.Row
Range("C2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""Invoice # "",RC[-1])"
    
    If endofrow5 > 2 Then
    Selection.AutoFill Destination:=Range("C2:C" & endofrow5)
    Else
    End If
    
Sheets(strSheetName).Select
Columns("E:f").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Invoice!C[-4]:C[-2],3,FALSE)"
        
        If endofrow2 > 2 Then
        Selection.AutoFill Destination:=Range("E2:E" & endofrow2)
        End If
        
Range("E2:E" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(RC[-1]),RC[-2],RC[-1])"
        
        If endofrow2 > 2 Then
        Selection.AutoFill Destination:=Range("F2:F" & endofrow2)
        End If
    
Range("F2:F" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("D:E").Delete Shift:=xlToLeft

        Else
        Sheets(strSheetName).Select
        End If
        
 

 Application.ScreenUpdating = True
 
 Worksheets("Batman").Visible = True
 Sheets("Batman").Select
  Application.Wait Now + TimeSerial(0, 0, 1)
   Worksheets("Batman").Visible = False
 Sheets("ScriptSheet").Select
 Application.ScreenUpdating = False
 Debug.Print
        

Sheets(strSheetName).Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "I am Batman"

With Selection.Font
.Size = 15
End With
With Selection
.HorizontalAlignment = xlCenter
End With

Columns("D:D").Select
Cells.EntireColumn.AutoFit

 
Sheets(strSheetName).Select


If Not IsEmpty(docs) Then
Columns("A:A").Copy
Range("G1").Select
ActiveSheet.Paste
Range("f1").Value = "Doc. No."
Else
End If

Columns("A:A").Delete Shift:=xlToLeft
Range("a1").Value = "Post.Date"
Range("B1").Value = "Due Date"
Range("C1").Value = "Type"
Range("d1").Value = "Amount"
Range("E1").Value = "Current Balance"


Cells.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("C2").Select
    
Do

If ActiveCell.Value Like "Int. Doc Int. Run*" Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]>0,""Late Fee"",""Deposit Interest "")"
ElseIf ActiveCell.Value Like "Interest Document Invoicing*" Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]>0,""Late Fee"",""Deposit Interest "")"
End If

ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = vbNullString

Cells.Replace What:="IS-U Invoicing*", Replacement:="Invoice", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="IS-U inv. reversal*", Replacement:="Invoice reversal", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Check Lot*", Replacement:="Payment", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Payment Run*", Replacement:="Payment", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Interest Document Invoicing*", Replacement:="Late Fee", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="IS-U Migr. of Paymts*", Replacement:="Balance Forward", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Dunning Run Document*", Replacement:="Late Fee", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("D2:E" & endofrow2).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("A:A").Select
ActiveWorkbook.Worksheets(strSheetName).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(strSheetName).Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets(strSheetName).Sort
.SetRange Range("A2:f" & endofrow2)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("E2").Value = "=RC[-1]"

If endofrow2 > 2 Then
Range("E3").Value = "=R[-1]C+RC[-1]"
Else
End If

If endofrow2 > 3 Then
Range("E3").AutoFill Destination:=Range("E3:E" & endofrow2)
Else
End If

Range("E2:E" & endofrow2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
         
If endofrow2 > 2 Then
Range("A2:F" & endofrow2).Select
Else
Range("A2:F2").Select
End If

With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
    
Cells.Replace What:="Payment Lot", Replacement:="Payment", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("A1").Select
Sheets("Debit Adjustment").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets("Balance forward").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets("Invoice").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select
Range("C3").Select

    If ActiveCell.Value Like "Balance Forward*" Then

While ActiveCell.Value Like "Balance Forward*"
ActiveCell.Offset(1, 0).Select
Wend
Dim BF1 As Long
BF1 = ActiveCell.Row
Rows(BF1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim extra As Range
Range("A2:f2").Select
Range(Selection, Selection.End(xlDown)).Select
Set extra = Selection
Range("d2").Select
Selection.End(xlDown).Offset(1, 0).Select
myrng3 = Range("D2").End(xlDown).Offset(0, 0).Address
ActiveCell.Formula = "=SUM(D2:" & myrng3 & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
ActiveCell.Offset(0, -2).Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 5).Select
ActiveCell.FormulaR1C1 = "Multiple Balance Forwards"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
extra.Select
Selection.Delete Shift:=xlUp
 
    End If

   'HERE !!!!!
Range("A1:f1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Selection.Font
    .Name = "Arial"
    .Size = 10
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Bold = True
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With

Range("A100000").End(xlUp).Select
endofrow2 = ActiveCell.Row
Range("A1:f" & endofrow2).Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("A1:f" & endofrow2).Borders(xlEdgeTop).LineStyle = xlContinuous
Range("A1:f" & endofrow2).Borders(xlEdgeRight).LineStyle = xlContinuous
Range("A1:f" & endofrow2).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("A1:f" & endofrow2).Borders(xlInsideVertical).LineStyle = xlContinuous
Range("A1:f" & endofrow2).Borders(xlInsideHorizontal).LineStyle = xlContinuous
Rows("1:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("TXU").Select
ActiveSheet.DrawingObjects.Select
Selection.Copy

Sheets(strSheetName).Select
Range("C1").Select
ActiveSheet.Paste

Sheets("TXU").Select
Range("A6").Select
Selection.Copy

Sheets(strSheetName).Select
Range("C6").Select
ActiveSheet.Paste
    
If bills = "One to One" Then
GoTo SectionEndOne
Else: bills = "Multi"
GoTo SectionEndMulti
End If


SectionEndMulti:

    If Length < 11 Then
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "Account Name"
    Else
    Range("A8").FormulaR1C1 = "Account Name"
    Range("A9").FormulaR1C1 = "Account Number"
    Range("A8:A9").Select
    End If
  
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
    
    
    
    If Length > 11 Then
    
Range("B9").Activate
ActiveCell.FormulaR1C1 = ca
Selection.NumberFormat = "0"
With ActiveCell.Characters(Start:=1, Length:=2).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
End With
    
    End If
    
Range("B8").Select
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/ncaa2"
session.FindById("wnd[0]").SendVKey 0

    If Length = 10 Then
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-GPART").Text = Bp
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-VKONT").Text = ""
session.FindById("wnd[0]").SendVKey 0

If session.Children.Count > 1 Then
session.FindById("wnd[1]/usr/lbl[1,3]").CaretPosition = 4
session.FindById("wnd[1]").SendVKey 2
End If

    ElseIf Length < 10 Then
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-GPART").Text = ca
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-VKONT").Text = ""
session.FindById("wnd[0]").SendVKey 0

If session.Children.Count > 1 Then
session.FindById("wnd[1]/usr/lbl[1,3]").CaretPosition = 4
session.FindById("wnd[1]").SendVKey 2
End If

    Else
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-GPART").Text = ""
session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-VKONT").Text = ca
session.FindById("wnd[0]").SendVKey 0
    End If

Range("B8").Value = session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/txtFKKVKD-GPARTT").Text



'Everything Is Finished I think

Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select
Range("a12").Select
Selection.End(xlDown).Select
endofrow2 = ActiveCell.Row
Range("A12:C" & endofrow2).Select

With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With

Columns("A:A").EntireColumn.AutoFit
Range("D13:E13").Select
    
    If endofrow2 > 13 Then
    Range("D13:E" & endofrow2).Select
    Else
    End If
    
With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
End With

Range("c12:E12").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With
        
Range("C6").Select
        
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With

ActiveSheet.Shapes.Range(Array("Picture 1")).Select
ActiveSheet.DrawingObjects.Select
Dim myR As Range
Dim myP As Shape
For Each myP In ActiveSheet.Shapes
Set myR = myP.TopLeftCell
myP.Left = myR.Left + (myR.Width - myP.Width) / 2
myP.Top = myR.Top 'Optional
Next myP
Range("E11").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
    
With Selection.Font
    .Name = "Arial"
    .Size = 12
End With

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("B8:B9").Select
Selection.Font.Bold = True
    
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With
           
batman = Range("e10000").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = ("A1:F" & batman)
With ActiveSheet.PageSetup
       
        .RightFooter = "&P"
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintQuality = 600
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .Zoom = 100
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
     
If Length < 11 Then
Rows("9:10").Select
Selection.Delete Shift:=xlUp
Else
End If
End With

GoTo Complete
'=========================================================================== NEW
  
SectionEndOne:
Range("A8").FormulaR1C1 = "Account Name"
Range("A9").FormulaR1C1 = "Service Address"
Range("A10").FormulaR1C1 = "Account Number"
Range("A8:A10").Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
    
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
    
Range("B10").Activate
ActiveCell.FormulaR1C1 = ca
Selection.NumberFormat = "0"
    
    '''''''''''''''''''''''''''''''''''''
With ActiveCell.Characters(Start:=1, Length:=2).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
End With
    '''''''''''''''''''''''''''''''''''
    
Range("B8").Select
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/ncaa2"
session.FindById("wnd[0]").SendVKey 0

    If Length < 11 Then
    session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-GPART").Text = ca
    session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-VKONT").Text = ""
    Else
    session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-GPART").Text = ""
    session.FindById("wnd[0]/usr/subA01P01:SAPLFKKC:0100/ctxtFKKVKP-VKONT").Text = ca
    End If

session.FindById("wnd[0]").SendVKey 0
session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/txtFKKVKD-GPARTT").SetFocus
session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/txtFKKVKD-GPARTT").CaretPosition = 0
Range("B8").Value = session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/txtFKKVKD-GPARTT").Text

        If Length > 11 Then
If Not session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/ctxtGPART_ADR-ADTXT", False) Is Nothing Then

Sheets("Invoice2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "a"
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.Clear
Range("A1").Value = session.FindById("wnd[0]/usr/subGENSUB_HD:SAPLBUSS:1003/subA01P02:SAPLFKKC:0240/ctxtGPART_ADR-ADTXT").Text
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Ab1").End(xlToLeft).Select
Batgirl = ActiveCell.Address

    Do Until Batgirl Like "*A*1*"
        If ActiveCell Like "*/*" Then
        ActiveCell.Delete Shift:=xlToLeft
        Else
        ActiveCell.Offset(0, -1).Select
        Batgirl = ActiveCell.Address
        End If
    Loop
 
 
Range("A1").FormulaR1C1 = _
"=Trim(CONCATENATE(RC[1],"" "",RC[2],"" "",RC[3],"" "",RC[4],"" "",RC[5],"" "",RC[6],"" "",RC[7],"" "",RC[8],"" "",RC[9],"" "",RC[10],"" "",RC[11],"" "",RC[12],"" "",RC[13]))"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sheets(strSheetName).Select
Range("B9").Select
ActiveSheet.Paste
Else
Range("B9").FormulaR1C1 = "Not In System"
End If
    
        Else
        End If
        
Sheets("Invoice2").Select
Cells.Select
Selection.Clear
Sheets(strSheetName).Select
Application.DisplayAlerts = False
Range("A12:C12").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With
    
Columns("A:A").EntireColumn.AutoFit
Range("D13:E13").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
End With

Range("c12:E12").Select

'''''''''''''''''''''''''''''''''''''''''''''''''
    
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
End With

With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
End With


'''''''''''''''''''''''''''''''''''''''''''''''''


With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With
Range("c6").Select

With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With

ActiveSheet.Shapes.Range(Array("Picture 1")).Select
ActiveSheet.DrawingObjects.Select
Dim myR1 As Range
Dim myP1 As Shape
For Each myP1 In ActiveSheet.Shapes
Set myR1 = myP1.TopLeftCell
myP1.Left = myR1.Left + (myR1.Width - myP1.Width) / 2
myP1.Top = myR1.Top 'Optional
Next myP1
Range("E11").FormulaR1C1 = "=TODAY()"
Range("E11").Select

With Selection.Font
    .Name = "Arial"
    .Size = 12
End With

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("B8:B10").Select
Selection.Font.Bold = True

With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
End With

batman = Range("e10000").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = ("A1:F" & batman)
    
With ActiveSheet.PageSetup
        .RightFooter = "&P"
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintQuality = 600
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .Zoom = 100
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True

End With

Complete:
Range("E11").End(xlDown).Select
Total = ActiveCell.Value
Range("A1").Select
ActiveCell.Value = ca
ActiveCell.NumberFormat = "0"
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With

Worksheets("Balance forward").Visible = False
Worksheets("Debit Adjustment").Visible = False
Worksheets("Invoice").Visible = False
Worksheets("Invoice2").Visible = False
Worksheets("TXU").Visible = False
Application.ScreenUpdating = True
Sheets("ScriptSheet").Select
Range(Cells(rowcount, 6), Cells(rowcount, 6)).Value = Total
Range("D2").Select
If IsEmpty(ActiveCell.Value) Then
Sheets(strSheetName).Select
Columns("F:F").Delete Shift:=xlToLeft
Range("A1").Select

Sheets("ScriptSheet").Select
Else
End If
End If
 
Exit Sub

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA - how to add a copy and paste values on current module
Replies
2
Views
93
Minx_Minxy
M
T
  • Question Question
VBA On error isn't working
Replies
2
Views
179
Tashkkaa
T
M
  • Solved
Macro stops after message box appear
Replies
3
Views
424
bobsan42
bobsan42
J
  • Question Question
SAP coding
Replies
2
Views
374
Jordan2
J
T
  • Question Question
Workbook not visible by VBA
Replies
5
Views
704
Artik
Artik

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top