Copy & Replace Header Columns to Another Workbook Header Columns?

[ucsutah1]

I don't have a lot of experience with VB Scripts or Macros so I hope you can help me.

I have two separate workbooks that have different header columns on them. One is an Amazon workbook that has specific header columns in order to import products. The other is my distributor that I have to match Amazon Column Headers for the products to import. So what I need to do is a way to copy column headers from the Amazon workbook and replace my distributor's workbook column headers with Amazon's? However, my distributor's column headers are not in the same Columns as Amazons and different titles that basically mean the same thing.

For Example: SKU, Product Name Title, Manufacture, UPC Code - Amazon (Starting Column B, Row 2), And Distributor may be UPC Code, Manufacture No, Product Name, SKU etc. (Starting Column A, Row 2).

Also, One last thing would be to delete certain columns that don't match Amazons. For Example Column, F wouldn't be needed and deleted.

I have tried a few different things like lookup tables, and a ton of research but haven't had any luck.

thanks in advance

 

[jolivanes]

The first item of the b Array will replaced by the first item of the a Array, the second by the second etc etc. Change the items as required.
I see that the fourth item in the a Array is "Manufacturer". Change that to whatever it needs to be.

This should delete the columns also.

Sub Maybe()
Dim a, b, d, i As Long, j As Long
Dim sh2 As Worksheet

Set sh2 = Workbooks("datafeed.xlsm").Sheets("Sheet1")

a = Array("SKU", "Product ID", "Manufacturer Part Number", "Manufacturer", "Product Name/Title", "Standard Price", "Quantity")
b = Array("Item No", "UPC Code", "Manufacturer No", "Manufacturer", "Product Name", "Price (USD)", "Inventory")
d = Array("I:U", "F", "E")

Application.ScreenUpdating = False

For i = LBound(b) To UBound(b)
    sh2.Rows(1).Find(b(i)).Value = a(i)
Next i

For j = LBound(d) To UBound(d)
    sh2.Columns(d(j)).Delete
Next j

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[ucsutah1]

Awsome, wow you did a great job! You got it! Five Stars

One small little issue though is the Manufacturer Part Number only shows Manufacture still, and I have to have the headers to match exactly for the Amazon import too work? Could it be that because there is a duplicate Manufacturer and it only grabs the first part of the name when it searches and not Part Number?

thanks

 

[jolivanes]

Show us the arrays as you currently have them (or the whole code for that matter)

 

[ucsutah1]

Sub Maybe()Dim a, b, d, i As Long, j As Long
Dim sh2 As Worksheet


Set sh2 = Workbooks("datafeed1.xlsm").Sheets("Sheet1")


a = Array("SKU", "Product ID", "Manufacturer Part Number", "Manufacturer", "Product Name/Title", "Standard Price", "Quantity")
b = Array("Item No", "UPC Code", "Manufacturer No", "Manufacturer", "Product Name", "Price (USD)", "Inventory")
d = Array("I:U", "F", "E")


Application.ScreenUpdating = False


For i = LBound(b) To UBound(b)
    sh2.Rows(1).Find(b(i)).Value = a(i)
Next i


For j = LBound(d) To UBound(d)
    sh2.Columns(d(j)).Delete
Next j


Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Everything works perfectly fine on the code except just "Manufacture Part Number", which only shows Manufacturer after replacing Manufacturer No on the header?

 

[jolivanes]

You didn't change the arrays as I mentioned.

a = Array("SKU", "Product ID", "Manufacturer Part Number", "Manufacturer", "Product Name/Title", "Standard Price", "Quantity")
b = Array("Item No", "UPC Code", "Manufacturer No", "Manufacturer", "Product Name", "Price (USD)", "Inventory")

As it stand now
Item No will be replaced by SKU
UPC Code will be replaced by Product ID
Manufacturer No will be replaced by Manufacturer Part Number
Manufacturer will be replaced by Manufacturer. Change the Manufacturer in the a Array to whatever it needs to be
Or if you don't know how to do that, let us know what it should be.

 

[ucsutah1]

Manufacturer No will be replaced by Manufacturer Part Number
Manufacturer will be replaced by Manufacturer. Change the Manufacturer in the a Array to whatever it needs to be

I hope I can explain this better. When you run the code Manufacture No should be replaced by Manufacturer Part Number? however, it's not being replaced with Manufacturer Part Number it's being replaced by Manufacturer only and not Part Number. Array A & B are correct but its just not outputting Manufacture Part Number but only Manufacturer. If you run the code you will see what I mean.

This is an example after the code has run what it shows SKU, Product ID, Manufacturer (Should be Manufacturer Part Number), Manufacturer, Product Name/Title, Standard Price, and Quantity.

 

[jolivanes]

Re: it shows SKU, Product ID, Manufacturer (Should be Manufacturer Part Number), Manufacturer, Product Name/Title, Standard Price, and Quantity
That is exactly what I get when I run the code. The third Column (C) has "Manufacturer Part Number".
How is Cell C1 formatted? How wide is your Column C?

 

[ucsutah1]

So, i got rid of the second manufacturer in the array a and after that, it worked!

Even after the second manufacturer removed I now have an output of SKU, Product ID, Manufacturer Part Number, Manufacturer, Product Name/Title, Standard Price, and Quantity.

I don't know why it's working after I removed the second Manufacturer. But it's finally all working perfectly!!!!!! Thank You for all your help and support!

 

[jolivanes]

Glad you have it under control now.
Good luck