Copy Cells From One Worksheet To Another Worksheet Using A Macro

[TheTazster30]

First off I will say that I'm a total noob when it comes to Macros in Excel. I understand what it does and how it works but just not knowing the coding to make it happen and would love to get come help if possible please!

Problem:
I'm looking to be able to create a macro that will look on "Worksheet A" and then copy the cells or contents of that column and input that unto "Worksheet B" in another column. Now one of the important things is that in "Worksheet A" I have cells named "supplier_id", "drop_ship_fee", "supplier_name", "product_id"....etc.... But in "Worksheet B those cells are not the same name and are named "id", "mfgid", "name", "manufacturer", etc....


Ex:
supplier_id = id
drop_ship_fee = mfgid
supplier_name = name
product_id = manufacturer


Now I have to make sure that the information that's on "Worksheet A" listed under "Column A (supplier_id)" is copied over to "Worksheet B" under "Column A (id)". Yes I know that I can just copy and past from one Worksheet to the other but I will have to be doing this with thousands of products every week and that's to time consuming. So I'm looking for a simple way to accomplish this.

I have attached the two worksheets so that you can see better at what I'm trying to accomplish. The "Worksheet C" is what the finished result would need to look like. Thank you in advance for any help on this issue, have been trying to figure this out all day and I'm sure it's very simple to accomplish.

[stnkynts]

Here you go. You are going to have to add to it because i was not sure which columns in worksheet A correspond with which column in worksheet B. I did the first few columns for you. You should be able to look at the code and add/edit to get what you need. Let me know if you need further help.

Option Explicit

Sub CopyData()
Dim lastrow As Long

lastrow = Sheets("Worksheet A").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Worksheet A").Range("A2", "E" & lastrow).Copy _
    Destination:=Sheets("Worksheet B").Range("A2")
Sheets("Worksheet A").Range("AB2", "AB" & lastrow).Copy _
    Destination:=Sheets("Worksheet B").Range("F2")
End Sub

[TheTazster30]

First off let me thank you for taking time out to help and resolve this problem I really appreciate the help. Works like a charm by the way! I knew that it had to be something simple but for a noob like me just wasn't working...LOL!

Now if possible so that I can disect the code and understand it, the first part makes sense to me and that I see what needs to be added or edited for it to match my worksheet.

Option Explicit

Sub CopyData()
Dim lastrow As Long

lastrow = Sheets("Worksheet A").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Worksheet A").Range("A2", "E" & lastrow).Copy _
    Destination:=Sheets("Worksheet B").Range("A2")

But this second part I'm not following what the "AB2" & "AB" & "F2" are or where that code is a part of on the Worksheets. I'm not following that and if you could explain that part would be appreciated.

Sheets("Worksheet A").Range("AB2", "AB" & lastrow).Copy _
    Destination:=Sheets("Worksheet B").Range("F2")
End Sub

Now with this code I see that all that I basically have to do is assign each Column on "Worksheet A" to the correct Column on "Worksheet B" and as long as the Columns don't change on my Worksheets that it will always copy the correct info from "Worksheet A" to "Worksheet B". Now I'm sure for now that the Columns won't change on my Exported Excel List but lets say down the road the Columns do change I would have to go back in and reassign the Columns correctly for the correct information to be under that Column.

So my question would be is there a way to be able write the code so that it will lets say scan "Worksheet A" "Row 1" look for cell named "supplier_name" and take everything under that Column on "Worksheet A" and then scan "Worksheet B" "Row 1" for "manufacturer" and then place everything under that Column on "Worksheet B"?

So that it's more specific instead of "A1" to "A1", "B2" to "B2" that it would go "supplier_name" to "manufacturer", "product_id" to "mfgid"....etc....Hope that makes sense!.....LOL

[stnkynts]

The reason I set the second copy destination to the "F" column was because thats where i thought the "AB" column from worksheet A needed to go. I could have been entirely wrong. In answer to your question about searching for string names, while it sounds easy on paper, coding a macro to do it is something else. Hered what it would look like

Option Explicit

Sub CopyData2()
Dim lastcol As Long, lastrow As Long
Dim icol As Variant, bcol As Variant

'counts how many columns there are
lastcol = Sheets("Worksheet A").Cells(1, Columns.Count).End(xlToLeft).Column
'counts how many rows there are
lastrow = Sheets("Worksheet A").Range("A" & Rows.Count).End(xlUp).Row

'loop
For Each icol In Sheets("Worksheet A").Range("1:1")
    'the word in ""'s is what you are comparing against
    If InStr(1, icol, "supplier", vbTextCompare) Then
        icol.EntireColumn.Copy
    End If
        
        For Each bcol In Sheets("Worksheet B").Range("1:1")
            If InStr(1, icol, "id", vbTextCompare) Then
                bcol.PasteSpecial xlPasteAll
                GoTo Last
            End If
        Next bcol
        
        'For Each bcol In Sheets("Worksheet B").Range("1:1")
           ' If bcol.Value = "id" Then
              '  bcol.PasteSpecial xlPasteAll
              '  GoTo Last
           ' End If
       ' Next bcol
Next icol

Last:
End Sub

I tested it out on the sheet and it work. I would like to point out that where you see an apostraphy it means that the code will not read that part. So essentially I can write messages that only someone looking at the code will see. The macro will go right over it without looking at it. I have included, even though it probably isnt neccessary or even best, a string search function. Basically you can have it search row 1 for whatever "like" word you want. So in this situation it will find anything that has "supplier" in it, like for example "supplier_id". If you know exactly what the cell value is going to be it would be best just to say icol.value = "whatever". I put a snippet of code in to show you what that would look like.

Note: This is not the most efficient way to do this. For some reason i can do things great going down rows but going left to right on columns seems to screw me up. This is my own failing and I am working hard to fix it. Nonetheless it works. Maybe a more advanced member can update to a more efficient code. Let me know.