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

 

[Leith Ross]

Hello ucsutah1,

In a case like your's, it would be helpful if you could provide a workbook with a sheet for the Amazon headers, a second sheet for the Distributor's headers, and a third sheet showing the desired result.

Since this forum does not allow posting of workbooks, you would need to either upload the workbook to a public file sharing service or email it to me.

 

[ucsutah1]

Wow, that was quick thanks for the quick reply.

Here is a link to Amazon.xlsx, datafeed.xlsx, and what it should look like datafeedchanged.xlsx. On datafeed.xlsx I highlighted the column headers in red that are not needed and columns can be deleted with the script. As you will see in datafeedchanged.xlsx (just an example of what datafeed.xlsx should look like with the column headers), there are only 7 headers needed and the top column headers are the changed Amazon headers. I also put underneath those columns of what the column used to be from my distributor datafeed.xlsx. https://mega.nz/#F!65kBFJKJ!dZFDAy1C821k7lWlfyc1nQ

I hope this forum allows links?

If you need any other information please let me know. Thanks for your help again really appreciate it.

 

[Leith Ross]

Hello ucsutah1,

Perfect, I downloaded the zip folder and all the files are there. Thanks/

 

[jolivanes]

In the 1st Array, replace the "A", "B" etc with actual column headers.
In the 2nd Array (b) replace with actual column headers from wb2.
Both should have the same amount of column headers.
They should be in order from left to right as in the worksheets.
Change Workbook names and sheet names as required.
Both workbooks need to be open.

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

Set wb1 = Workbooks("Book1.xlsm")
Set wb2 = Workbooks("Book2.xlsm")

a = Array("Col A", "Col B", "Col C", "Col D", "Col E", "Col F", "Col G", "Col H")
b = Array("Col HA", "Col GB", "Col FC", "Col ED", "Col DE", "Col CF", "Col BG", "Col AH")

Application.ScreenUpdating = False

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

 

[ucsutah1]

Thanks for the help, Obviously I am doing something wrong. I am getting a runtime error 9 subscript out of range? Also, is there a way to delete certain columns by letter in the script? thanks again

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


Set wb1 = Workbooks("Amazon.xlsx")
Set wb2 = Workbooks("datafeed.xlsx")


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


Application.ScreenUpdating = False


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

 

[jolivanes]

In which workbook do you have the code?
The extension can not be .xlsx (as you have in your code).

 

[ucsutah1]

The workbook I ran it from was amazon.xlsx, but I changed the code to Amazon.xlsx to Amazon.xlsm, I also changed datafeed to datafeed.xlsx to datafeed.xlsm.

[COLOR=#333333]Set wb1 = Workbooks("Amazon.xlsm")
[/COLOR][COLOR=#333333]Set wb2 = Workbooks("datafeed.xlsm")[/COLOR]

I also saved the two workbooks as amazon.xlsx to xlsm and datafeed.xlsx to datafeed.xlsm. I still get the same error when I run the macro. I don't know what i am doing wrong?

 

[jolivanes]

Did you save the workbooks before you tried the code again?
Which line is showing the error?

 

[ucsutah1]

Ok, I exited both workbooks (Amazon.xlsm), and datafeed.xlsm and I reran the macro code and got another error

Run-time error '91' Object variable or With block variable not set

I debugged that and this was in yellow

d = wb1.Sheets("Sheet1").Rows(1).Find(a(i)).Column

Should Both workbooks be xlsm and not xlsx correct? But the code should only have to be on the one your running it from (Amazon.xlsm) or does it have to be on both?