asked on

Copy Multiple Columns from one sheet to another with different headers

Hi,

I have some VBA that copies one sheet from one workbook to another. Now that I have the data in the same workbook. I have searched, but have not been able to find a solution specific to what I need. I would like to copy specific columns from Store Schedule tab to the RC Schedule tab however the columns are not the same and the column names on Store Schedule tab can be in row 2 or 3 or both. Example of some of the columns that I need to copy (maintaining the column header in the RC Schedule tab).

From Store Schedule to RC Schedule Column

1. Column A to A
2. Column C to B
3. Column D to D
4. Column E to F
5. Column HG to J

Attaching a sample of the file as well
International-Store-Schedule-Tem.xls

aikimark

are you sure you want C to B? There is already a StoreNumber column on RC.

jmac001

ASKER

Yes, the store number in the RC tab is different than the store number in the Store Schedule tab.

aikimark

See if this works for you.

Option Explicit

Public Sub Q_28409476()
    Dim wksFrom As Worksheet
    Dim wksTo As Worksheet
    Dim rngFrom As Range
'    Dim rngTo As Range
    Set wksFrom = Worksheets("Store Schedule")
    Set wksTo = Worksheets("RC Schedule")
    Set rngFrom = wksFrom.Range(wksFrom.Range("A5"), wksFrom.Range("A5").End(xlDown))
    rngFrom.Copy wksTo.Range("A2")
    Set rngFrom = wksFrom.Range(wksFrom.Range("C5"), wksFrom.Range("C5").End(xlDown))
    rngFrom.Copy wksTo.Range("B2")
    Set rngFrom = wksFrom.Range(wksFrom.Range("D5"), wksFrom.Range("D5").End(xlDown))
    rngFrom.Copy wksTo.Range("D2")
    Set rngFrom = wksFrom.Range(wksFrom.Range("E5"), wksFrom.Range("E5").End(xlDown))
    rngFrom.Copy wksTo.Range("F2")
    Set rngFrom = wksFrom.Range(wksFrom.Range("HG5"), wksFrom.Range("HG5").End(xlDown))
    rngFrom.Copy wksTo.Range("J2")
End Sub

Open in new window

jmac001

ASKER

Question, I have fields that will not have data, how do I account for that in the VBA. I placed the code in my working file and where ever there was a break in the data is where the data stopped for that column.

aikimark

That's generally done by using the lastcell special reference or invoking a Find method to get the last cell in a column (or row). Do you know how to do that?

jmac001

ASKER

No, I don't

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmac001

ASKER

Get errror 1004 (application - defined or or object-defined error) on:

Line 21 - Set rngFrom = wksFrom.Range(wksFrom.Cells(5, 215), wksFrom.Cells(215 lngLastRow))

aikimark

That should be:

Set rngFrom = wksFrom.Range(wksFrom.Cells(5, 215), wksFrom.Cells(lngLastRow, 215))

Open in new window