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
Microsoft ExcelVBAMicrosoft Office
Last Comment
Tracy
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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