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)
<tbody>
</tbody>
The destination spreadsheet already has desired header titles in place, and would like to have only 1 record per row. For example:
<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:
Any pointers on making this more efficient would be appreciated, but it functions....
Thanks all!
Name | Address | Title | Phone | Name | Address | Title | Phone | Name | Address | Title | Phone |
John | 123 Main st | Mr. | 555-5555 | Sally | 222 something rd | Ms. | Eric | 333 Adress pl | 555-1234 | ||
Bill | Mr. | 111-1111 | |||||||||
Jill | 45 45th st | Mrs. | 222-2222 | Sam | 88 8th st | Mr. |
<tbody>
</tbody>
The destination spreadsheet already has desired header titles in place, and would like to have only 1 record per row. For example:
Title | Name | Phone |
Mr. | John | 555-5555 |
Ms. | Sally | |
Eric | 555-1234 | |
Mr. | Bill | 111-1111 |
Mrs. | Jill | 222-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!