none
Move data from one Excel Workbook to another RRS feed

  • Question

  • Hello,

    I have two lists of information with several rows and columns. I need to take column "systems" from list 1 and copy it to list 2. The rows are not in the same order in both lists.

    LIST 1                                                                                                                                    LIST 2

    PMO ... ... ... Systems ... ... ...                                                                PMO ... ... ... Systems ... ... ...

    9891 ... ... ... 384-239 ... ... ...                                                               9570

    9570 ... ... ... 186-033 ... ... ...                                                               9891

    9047 ... ... ... 584-273 ... ... ...                                                               9047

    ...

    I did the following script and it works pretty well, the only inconvenient is that it ends up copying the last system of list 1 to the first row of list 2:

                                      

    Sub SystemsToWIT()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    'Se definen variables
    fila_org = 3 'fila de origen
    col_PM_org = 1 'columna de origen PMOs
    col_system_org = 15 'columna de origen CSE

    fila_des = 4 'fila de destino
    col_PM_des = 1 'columna de destino PM
    col_system_des = 19 'columna de destino zona

    Dim PM, system As String
    'Se han definido variables

    'Se da valor inicial a las variables de origen
    PM_org = Cells(fila_org, col_PM_org)
    system_org = Cells(fila_org, col_system_org)

    'Mientra PM_org sea diferente a nada, se traspasan datos
    While PM_org <> ""
        PM_org = Cells(fila_org, col_PM_org)
        system_org = Cells(fila_org, col_system_org)

    'Se activa libro de destino
    Workbooks("WIT OctenoZ7.xlsb").Activate
    'Se activa hoja de destino
    Worksheets("TA Inventory").Select

    'Busca la celda de la PM para iniciar volcado de Sistemas.
        While PM_des <> PM_org
            fila_des = fila_des + 1
            PM_des = Cells(fila_des, col_PM_des)
        Wend

    'Se copian valores de origen en variables de destino
        system_des = system_org
        Cells(fila_des, col_system_des) = system_des

        fila_org = fila_org + 1
        fila_des = 4

    'Se activa el libro de origen
        Workbooks("LibroDestino7.xlsm").Activate
    'Se activa hoja de origen
        Worksheets("Hoja_Destino_7").Select

        PM_org = Cells(fila_org, col_PM_org)
    Wend

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = False

    MsgBox ("Corregir campo de la columna S en la fila de la primera PMO de la WIT")

    End Sub

    If someone can find the error, I will fill really grateful :).

    Best regards,

    Monday, September 30, 2019 9:00 AM