Simple macros to re-arrange column into upload format

Excel macros to re-arrange column from text file into rows format. Please watch the video below.


1.  Create 2 Macros name in you excel.

2.  All Macros code have to paste in  Module1.

3.  Copy Macros's code below.

Sub Remove()
'   Delete title line
    Rows("1:5").Select
    Selection.Delete Shift:=xlUp
    
'
    For i = 1 To 9000
        Range("A" & i).Select
        If Left(ActiveCell.Value, 1) = "-" Then
            Rows(i & ":" & i).Select
            Selection.Delete Shift:=xlUp
        End If
    Next
    
'   Delete column B and D
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft

'   Paste title in Sheet2
    Sheets("Sheet1").Select
    Range("A1:A9").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
    Sheets("Sheet1").Select
    Range("C1:C9").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    Sheets("Sheet1").Select
    Range("E1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("S1").Select
    ActiveSheet.Paste

End Sub


Sub addnew()
Dim lines As Long
Dim records(18) As String

lines = 1
For i = 1 To 8000
    Sheets("Sheet1").Select
    Range("A" & i).Select
    If ActiveCell.Value = "Supplier" Then

        For a = 0 To 8 ' add into record.
            irow = i + a
            Range("B" & irow).Select
            records(a) = ActiveCell.Value
            Debug.Print records(a) & " Array (" & a & ")"
            Range("D" & irow).Select
            records(a + 9) = ActiveCell.Value
            Debug.Print records(a + 9) & " Array (" & a + 9 & ")"
            If a = 0 Then
                Range("F" & irow).Select
                records(18) = ActiveCell.Value
                Debug.Print records(18) & " Array (18) "
           End If
        Next
        Debug.Print "---add Records----"
        Debug.Print ""
        Sheets("sheet2").Select
        lines = lines + 1
        For w = 0 To 18
            Range(Chr(65 + w) & lines).Select
            ActiveCell.Value = records(w)
        Next
        For r = 0 To 18
            records(r) = ""
        Next
    End If
Next
End Sub






Comments

Post a Comment

Popular posts from this blog

Excel VBA connect SAPRFC

Image
This is simple and you don't have to install those development tools to run the code. Is just required Microsoft excel you can connect into your SAP R/3. No other tools required, no need the extra software installed and mainly is using Excel VBA run the code and you able to retrieve/import data on your SAP R/3 data. Required item: This is the items required to start your tutorial: Microsoft excel. SAP Login id and password. SAP program with remote function call. I will show your my customized RFC coding in my SAP ABAP. The RFC program name is "ZMM_PRPORS" SAP Gui installed One more setting you have to do before start the coding. You have to add SAP reference below: 1. Go to Developer tab -> Click the Visual Basic: 2. Go to Tools -> References 3. Click those SAP references show as below and click OK. Excel VBA Coding Double click on the sheet1 and pasted code below. Please fill in your SAP ip addres...
Read more

How to Create Sap Gui Scripting

Image
SAP GUI Scripting syntax similar like VBScript and you can used setup your testing data or data migration from legacy system to SAP. Is easy and less time to create sap gui scripting compared to LSMW. Sap Gui Scripting it can migrated master data and Transaction data as well. I will showed you how to create it. Here the step by step to create your sap gui script 1. Click on the Script Recording and Playback 2. Click on  More button, you can changed the filename and path. 3. Click on Record button and type "SU01" to lock user id. 4. Once you have completed then click Stop button. 5. Here the script you recorded. If Not IsObject(application) Then    Set SapGuiAuto  = GetObject("SAPGUI")    Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then    Set connection = application.Children(0) End If If Not IsObject(session) Then    Set session    = con...
Read more