Link to home
Start Free TrialLog in
Avatar of James Kyritsis
James Kyritsis

asked on

How do i import and manipulate data from one excel worksheet to another?

Hi Everyone,

I hope you can help me.

I have a example worksheet containing the following data:

2017 Barcode      2018 Barcode      Publisher Name                   Carton Qty      Weight      
9781782085225      9781782087168      AVONSIDE                           50                    330

I need to import and manipulate the data into the following format in another worksheet:

CAL Code        Barcode              Publisher Code    Weight   Carton Qty      Pallet Qty      
CAL12345      9781782087168      ASIDE                     .330        50                     2,500

In order to do this:
- I have a table containing 2017 Barcodes and their corresponding CAL Codes,
- I have a table containg Publisher Name and their corresponding Publisher Codes,
- Carton Qty is simply a trainght copy,
- Pallet Qty is equal to Carton Qty multiplied by 50, and
- Weight is equal to Weight devided by 1000
Avatar of Mike in IT
Mike in IT
Flag of United States of America image

Where are the two tables that you mention? Can you provide a couple of sample workbooks?
Here is an example of what you can do.

This assumes that the data to be copied starts in A1 and that the place that it is being copied to starts in A1 also.

And because I don't know where the tables for the Publisher Codes and CAL Codes are, I placed them both on the same sheet as the data to be copied.
Obviously, this can be updated to where they actually are. Here is the example:
Sub CopyFormat()
    Dim wbMain As Workbook, wbData As Workbook
    Dim wsData As Worksheet, wsMain As Worksheet
    Dim CalCode, Barcode, PublisherCode, Weight, Carton, Pallet, LastRow
    
    Set wbData = Workbooks("TestBook.xlsm")
    Set wbMain = Workbooks("WorkingData.xlsm")
    
    Set wsData = wbData.Sheets("Sheet1")
    Set wsMain = wbMain.Sheets("Self Appraisal")
    
    For Each c In wsData.Range(wsData.Range("A2"), wsData.Range("A" & Rows.count).End(xlUp))
        Barcode = wsData.Range("B" & c.row).Value
        Weight = wsData.Range("E" & c.row).Value / 1000
        'Weight = Weight
        Pallet = wsData.Range("D" & c.row).Value * 50
        PublisherCode = Publisher(wsData.Range("A" & c.row).Value, wsData)
        CalCode = CAL(wsData.Range("C" & c.row).Value, wsData)
        Carton = wsData.Range("D" & c.row).Value
    Next c
    
    With wsMain
        LastRow = .Cells(.Rows.count, "A").End(xlUp).row + 1
    End With
    
    wsMain.Range("A" & LastRow).Value = CalCode
    wsMain.Range("B" & LastRow).NumberFormat = "0"
    wsMain.Range("B" & LastRow).Value = Barcode
    wsMain.Range("C" & LastRow).Value = PublisherCode
    wsMain.Range("D" & LastRow).NumberFormat = "#.000"
    wsMain.Range("D" & LastRow).Value = Weight
    wsMain.Range("E" & LastRow).Value = Carton
    wsMain.Range("F" & LastRow).Value = Pallet
    
End Sub

Function Publisher(X, wsData)
    For Each c In wsData.Range(wsData.Range("I1"), wsData.Range("I" & Rows.count).End(xlUp))
        If X = wsData.Range("J" & c.row).Value Then
            Publisher = wsData.Range("I" & c.row).Value
            Exit For
        End If
    Next c
End Function
Function CAL(X, wsData)
    For Each c In wsData.Range(wsData.Range("L1"), wsData.Range("L" & Rows.count).End(xlUp))
        If X = wsData.Range("L" & c.row).Value Then
            CAL = wsData.Range("M" & c.row).Value
            Exit For
        End If
    Next c
End Function

Open in new window

Avatar of James Kyritsis
James Kyritsis

ASKER

Hi Mike,

The two tables are located in another worksheet within the same excel file.

Worksheet CAL_NUMBERS:
Cal_Number         Plu
CAL12345             123456789

Worksheet PUBLISHER_CODES:
Eg.
Publisher_Name                      Publisher_Code
Avonside Publishing Ltd         AVON

The name of the source data worksheet is "Main Data"
The name of the destination worksheet is "Final Data"

I have now attached a copy of the excel file for you to understand clearer.

THANK YOU Mike!
TEST-PID-Consolidated-Master-2018.xlsm
Okay, I've made few updates to the code I provided before:

Sub CopyFormat()
    Dim wbMain As Workbook, wbData As Workbook
    Dim wsData As Worksheet, wsMain As Worksheet, wsCAL As Worksheet, wsPub As Worksheet
    Dim CalCode, Barcode, PublisherCode, Weight, Carton, Pallet, LastRow
    
    Set wbData = Workbooks("Test-PID-Consolidated-Master-2018.xlsm")
    Set wbMain = Workbooks("WorkingData.xlsm")
    
    Set wsData = wbData.Sheets("Main Sheet")
    Set wsMain = wbData.Sheets("Final Sheet")
    Set wsCAL = wbData.Sheets("CAL_NUMBERS")
    Set wsPub = wbData.Sheets("PUBLISHER_CODES")
    
    For Each c In wsData.Range(wsData.Range("A6"), wsData.Range("A" & Rows.Count).End(xlUp))
        Barcode = wsData.Range("B" & c.Row).Value           '2018 barcode
        If Application.IsNumber(wsData.Range("AK" & c.Row).Value) Then
            Weight = wsData.Range("AK" & c.Row).Value / 1000 'weight divided by 1000
        Else
            Weight = "The value: " & wsData.Range("AK" & c.Row).Value & " is not a number"
        End If
        If Application.IsNumber(wsData.Range("AO" & c.Row).Value) Then
            Pallet = wsData.Range("AO" & c.Row).Value * 50      'Carton qty * 50 = Pallet
        ElseIf Application.IsText(wsData.Range("AO" & c.Row).Value) Then
            Pallet = "The value: " & wsData.Range("AO" & c.Row).Value & " is not a number."
        Else
            Pallet = "The value provided is not a number or text."
        End If
        PublisherCode = Publisher(wsData.Range("G" & c.Row).Value, wsPub)   'Publisher Codes looked up in Publisher Codes sheet
        CalCode = CAL(wsData.Range("A" & c.Row).Value, wsCAL)   '2017 barcode lookup in CAL Numbers sheet
        Carton = wsData.Range("AO" & c.Row).Value
    
        With wsMain
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With
        
        If CalCode <> "" Then
            wsMain.Range("A" & LastRow).Value = CalCode
        Else
            wsMain.Range("A" & LastRow).Value = "2017 Barcode: " & Barcode & " not found"
        End If
        wsMain.Range("B" & LastRow).NumberFormat = "0"
        wsMain.Range("B" & LastRow).Value = Barcode
        wsMain.Range("C" & LastRow).Value = PublisherCode
        wsMain.Range("D" & LastRow).NumberFormat = "#.000"
        wsMain.Range("D" & LastRow).Value = Weight
        wsMain.Range("E" & LastRow).Value = Carton
        wsMain.Range("F" & LastRow).Value = Pallet
    Next c
    
End Sub

Function Publisher(X, wsPub)
    For Each c In wsPub.Range(wsPub.Range("A1"), wsPub.Range("A" & Rows.Count).End(xlUp))
        If UCase(X) = UCase(Left(wsPub.Range("A" & c.Row).Value, Len(X))) Then
            Publisher = wsPub.Range("B" & c.Row).Value
            Exit For
        End If
    Next c
End Function
Function CAL(X, wsCAL)
    For Each c In wsCAL.Range(wsCAL.Range("L1"), wsCAL.Range("L" & Rows.Count).End(xlUp))
        If X = wsCAL.Range("L" & c.Row).Value Then
            CAL = wsCAL.Range("A" & c.Row).Value
            Exit For
        End If
    Next c
End Function

Open in new window


There were some problems with the formatting of the data in you Main Sheet. The Weight sometimes had a "g" after it and sometimes did not. Also, there were a few that had a formula for them that ended up having errors since I don't have the files that they were referring to. I added some logic to ignore the values that were not numbers or text.

Then there were the 2017 barcodes. Most of them were not found in the CAL_NUMBERS sheet. I was able to find only 2776 out of the 5785 2017 barcodes.

Hope this helps. Let me know if there are any problems that you discover.
Thank you Mike.

I have inserted the macro into the "Final Sheet" worksheet and ran the macro.
An error appears "Subscript Out Of Range".

It appears to be working for you. Are you able to attach your file Mike?
ASKER CERTIFIED SOLUTION
Avatar of Mike in IT
Mike in IT
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Mike,

I cut and pasted your code into the Final Sheet worksheet via developer and ran the macro.
I'm not sure I understand what you mean by regular user module?

Regards,
James
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Are you still getting an error on this?
Avatar of Tracy
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Mike in IT (https:#a42088057)
-- Mike in IT (https:#a42090491)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer