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

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

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:

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
```

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

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:

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.

```
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
```

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.

ASKER

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?

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

Are you still getting an error on this?

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

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