Formatting Data from One to To Format on Another

[psulions83]

Hi,

I have a worksheet with columns in order I need data. On a second tab I have data that I want to convert to the same order as the first tab. I had an hlookup funtion that worked but when trying to do this for thousands of rows it would not finish. Is there a more efficient way to do this that anyone can suggest?

 

[Joe4]

Assuming that both sheets have column headers which can be used to match and identify which columns need to be moved, I would probably opt for VBA, in which you can move whole columns at a time.

If there is consistency, in that the columns will ALWAYS be in a certain order every time (so you are always having to move the same columns the same way every time), you can get the VBA code you need very easily by turning on the Macro Recorder and record yourself moving them manually. That will give you the code that you need.

If that is not the case, and you need help coming up with VBA code, please post more detailed description, including:
- what the name of your two sheets are
- which sheet's column structure are you trying to match
- which rows/columns the headers appear in
- if both sheets do not have the same number of columns, what do you want to happen to the extra columns

 

[psulions83]

Thanks Joe. I will try this but I am not sure it will be the best option as the columns could potentially change position. Might be a short term solution.

 

[Joe4]

Thanks Joe. I will try this but I am not sure it will be the best option as the columns could potentially change position.

I was just saying that you could use the Macro Recorder if they do not change position. That would be easy for you because it does not involve any code writing, only code recording.
However, VBA can handle it, even if columns can change position. We would just need to write that code (instead of recording it).
If you can answer the questions I asked in my previous post, I can probably help you with that.

 

[psulions83]

Thanks Joe!

- what the name of your two sheets are - CURRENT FORMAT and PRIOR FORMAT
- which sheet's column structure are you trying to match - I need data from PRIOR to come into CURRENT
- which rows/columns the headers appear in - ROW 1 for both
- if both sheets do not have the same number of columns, what do you want to happen to the extra columns - CURRENT will always have more columns than prior.

 

[Joe4]

I need data from PRIOR to come into CURRENT

OK, maybe I am not clearly understanding your issue.

I thought that you wanted to arrange it so that both sheets had their columns in the same order, meaning both sheets have data, but the data is ordered differently.
But your statement above makes it sound like you will be moving data from one sheet to the other. Please explain this in more detail.

Better yet, can you provide a small example of the data on each sheet, and walk us through exactly what should happen in that example?
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[psulions83]

I can post screen shots if this doesnt make sense.

CURRENT tab has following columns
1 2 3 4 5

PRIOR TAB has the following columns
1 2 4 5

I want to bring the data from PRIOR into the columns in CURRENT. The columns in my example above are same named columns from both tabs. ie 1 on CURRENT equal 1 on PRIOR and so on.

 

[Joe4]

Is there any data already on the CURRENT sheet?
If so, are we just pasting below it, or will we always be pasting starting in row 2 (just under the header)?

 

[psulions83]

No data in CURRENT sheet. CURRENT just has column heading with the name. Paste in row 2 yes.

 

[Joe4]

Try this:

Sub MyCopy()

    Dim pws As Worksheet, cws As Worksheet
    Dim lc As Long, c As Long, nc As Long
    Dim lr As Long
    Dim hdr As Variant
    
    Application.ScreenUpdating = False
    
'   Set worksheet object
    Set pws = Sheets("Prior")
    Set cws = Sheets("Current")
    
'   Find last column with data in row 1 of prior ws
    lc = pws.Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Find last row with data on prior ws
    lr = pws.Range("A1").SpecialCells(xlLastCell).Row
    
    pws.Activate
'   Loop through all columns on prior ws
    For c = 1 To lc
'       Get column header from prior ws
        hdr = pws.Cells(1, c)
'       Find which column hdr is found on current ws
        On Error GoTo err_chk
        nc = cws.Rows("1:1").Find(hdr, LookIn:=xlValues, LookAt:=xlWhole).Column
        On Error GoTo 0
'       Copy data if value found
        If nc > 0 Then
            pws.Range(Cells(2, c), Cells(lr, c)).Copy cws.Cells(2, nc)
        Else
'           Message if cannot find column
            MsgBox "Cannot find matching header " & hdr & " on Current sheet", vbOKOnly
        End If
    Next c
    
    Application.ScreenUpdating = True
    
    MsgBox "Copy complete!", vbOKOnly
        
    Exit Sub
    
'   Error handling if cannot find column
err_chk:
    nc = 0
    Err.Clear
    Resume Next
    
End Sub