Copying from one worksheet to another with added complications

rnewton

New Member
Joined
Apr 12, 2018
Messages
11
Ok, I am trying to figure out a way to move records from one worksheet to another, my biggest problem is: Each row in the source worksheet can have up to 3 records, and those records have identical column header titles. I also only want to pull certain columns over to new worksheet. Here's how that looks: (there are many columns in source, this is just an example)

NameAddressTitlePhoneNameAddressTitlePhoneNameAddressTitlePhone
John123 Main stMr.555-5555Sally222 something rdMs.Eric333 Adress pl555-1234
BillMr.111-1111
Jill45 45th stMrs.222-2222Sam88 8th stMr.

<tbody>
</tbody>

The destination spreadsheet already has desired header titles in place, and would like to have only 1 record per row. For example:

TitleNamePhone
Mr.John555-5555
Ms.Sally
Eric555-1234
Mr.Bill111-1111
Mrs.Jill222-2222
Mr.Sam

<tbody>
</tbody>


How would we keep the data from crossing over or overwriting records? What I have so far:

I've declared the following:
Source Worksheet = sWS
Destination Worksheet = dWS
Source Headers = sHDR
Destination Headers = dHDR
Source Rows = sRWS

UPDATE!

Through the process of trying to thoroughly explain this problem, I was able to solve this myself. I put too much effort into this to delete, so here's what I did:


Code:
i = 1
a = 2

Dim MATCH As Range


For Each RW In sRWS
    For i = 1 To sHDR.Columns.Count
        Set MATCH = dHDR.Find(what:=sWS.Cells(1, i).Value, lookat:=xlWhole)
            If Not MATCH Is Nothing Then
Step1:
                    If IsEmpty(dWS.Cells(a, MATCH.Column).Value) = True Then
                        dWS.Cells(a, MATCH.Column) = sWS.Cells(RW.Row, i).Value
                    Else
                        a = a + 1
                        GoTo Step1
                    End If
            End If
    Next i
Next RW


Any pointers on making this more efficient would be appreciated, but it functions....

Thanks all!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,215,327
Messages
6,124,280
Members
449,149
Latest member
mwdbActuary

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top