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 code needs to be in the workbook where you are copying from, not where you're copying to.
What are the sheet names where you're copying from and to?
Is the spelling of the column headers right? No leading or trailing spaces in the headers?
I made a couple workbooks to try the code on and it works perfect here.

 

[ucsutah1]

I am sorry I tried everything and I still haven't been able to get it to work. I keep on getting that same error. However, as you were saying the sheet name was incorrect for amazon it was called Simple Data. I just created a new workbook and the sheet name is sheet 1 now. So copying from Sheet 1 on amazon1.xlsm, to Sheet 1 on datafeed.xlsm, and I still get the error message. I also just tried 1 column header and that still didn't work. I checked the column Headers and the spelling is correct and no spaces. I really appreciate you being patient and helping me through this. I attached workbook amazon1.xlsm and datafeed.xlsm so if you could see what I am doing wrong. The amazon1.xlsm has the code. https://mega.nz/#F!65kBFJKJ!dZFDAy1C821k7lWlfyc1nQ

thanks again for your help.

 

[jolivanes]

Your headers are in Row2, not Row1.
In the arrays you call the headers "Col SKU", there is no "Col" in the headers.

 

[jolivanes]

There were several instances where the data in the arrays was spelled different than in the workbook sheets.
This code worked for me on your attachments.
You'll have to check and change the data that is not the same, like the letter r missing in Manufacture.
If they're not the same you will continue to have errors.

Sub Maybe()
Dim wb1 As Workbook, wb2 As Workbook, a, b, i As Long, d As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set wb1 = Workbooks("Amazon.xlsm")    '<----- or Amazone1.xlsm?
Set wb2 = Workbooks("datafeed.xlsm")
Set sh1 = wb1.Sheets("Sheet1")
Set sh2 = wb2.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")


Application.ScreenUpdating = False


For i = LBound(a) To UBound(a)
    d = sh1.Rows(2).Find(a(i)).Column
        sh1.Range(Cells(3, d), Cells(Cells(Rows.Count, d).End(xlUp).Row, d)).Copy
            sh2.Rows(1).Find(b(i)).Offset(1).PasteSpecial xlPasteValues
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[ucsutah1]

thank you jolivanes that was it, and i noticed the Manufacturer wasn't spelled correctly. I finally was able to get it to run on the correct sheet, however, there is an issue with it not just replacing the headers in row 1 on datafeed.xlsm. It copies the amazon headers below the headers and puts all the other data below that. I just need the headers to be replaced and no other data below it? At least what i see unless i did something wrong. lol

thanks

 

[jolivanes]

Did I misunderstand your Post #1 . If so, my apologies.
Do you only want to replace the column headers and not transfer data?
So "Item No" would be replaced by "SKU", "UPC Code" by "Product ID" etc etc. Is that right.

 

[ucsutah1]

Yes, that is correct

So "Item No" would be replaced by "SKU", "UPC Code" by "Product ID" etc etc. Is that right.

Also, on post #1 I was hoping to remove columns that weren't needed.

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.

If I could just choose the columns in the code to delete? I hope that would be the easiest? I apologize if I wasn't clear on what I was hoping for.

thanks

 

[jolivanes]

Sub Maybe_A()
Dim a, b, i 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")


Application.ScreenUpdating = False

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

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

Which Columns do you want to delete?

 

[ucsutah1]

In datafeed.xlsm basically all the columns that the headers weren't changed on. In other words on the workbook the headers highlighted in red are the ones needed to be deleted except for column a header that i put it in red by accident. So Column E, F, I, J, K, L, M, N, O, P, Q, R, S, T, U need to be deleted. And hopefully they can be shifted over so there all in order meaning SKU, Product ID, Manufacturer Part Number etc.

thanks,

 

[ucsutah1]

I just tested your code and it works great by replacing the other headers. However, there is one header that only puts a portion of the header. Its right next to Product ID and it is Manufacturer Part Number and only replaces it with Manufacturer?