Loop through headers and copy and paste

[hartsie]

So, I am new to VBA. I have been trying to figure out looping and how to loop through my column headers to copy data from one sheet to the next. This seems like a very simple task, but its been killing me. I have shared my attempts below as a proof of my foolish efforts -- please ignore for your sanity's sake.

I simply want to copy from one sheet to another by searching the headers of Destination sheet and pasting the data with the same headers from the Source Sheet. So, it would look something like:

Destination Sheet
----------------------------
Header A = Account Number
Header B = Date
Header C = Name
Header D = Amount

Source Sheet
----------------------------
Header A = Transaction Date
Header B = Posting Date
Header C = Name
Header D = Account Number
Header E = Amount

Since the headers might not always exist in the same column, I want to be able to define a range of headers found on the destination sheet and loop through the headers on the source sheet to find the variable number of values below the headers and copy and paste to the destination sheet.

----------------------------
My failed attempts at trying this:
------------------------------------------------

Sub foreachlearn()

Dim myrange As Range
Dim mycell As Range
Dim srcrange As Range
Dim srccell As Range

Sheets("Master Sheet").Select
Range(Range("A1"), Range("A1").End(xlToRight)).Select
Set myrange = Selection

For Each mycell In myrange

If mycell.Text = "Account Number" Then
Selection.Copy

End If

Next mycell



End Sub
-------------------------------------------------------------------
Sub name_find_hdr()
'I am going to copy and paste the values from the consolidation sheet _
to the master sheet


Dim mstrWs As Worksheet 'This is the destination sheet
Dim srcWs As Worksheet 'This is the source sheet
Dim consolHdr As Range 'This is the header for the source sheet
Dim mstrHdr As Range 'This is the header for the destination sheet
Dim lkupHdr As Range 'This is the header for the property values in the destination sheet
Dim functHdr As Range 'This is the header with the functions in the destination sheet
Dim rng As Range
Dim strg As String
Dim consend1 As Range
Dim mycell As Range
Dim myrange As Range

'Set the worksheet names
Set mstrWs = Sheets("Master Sheet")
Set srcWs = Sheets("Account Consolidation")

'Create the header ranges for the source sheet
srcWs.Activate
Range("A1").End(xlToRight).Select
Set consend1 = Selection
Set consolHdr = Range("A1", consend1)

'Create the header ranges for the destination sheet
mstrWs.Activate
Set mstrHdr = mstrWs.Range("A1:L1")
Set lkupHdr = mstrWs.Range("M1:U1")
Set functHdr = mstrWs.Range("V1", Range("V1").End(xlToRight))

'mstrHdr.Find("account number").Select

srcWs.Select
consolHdr.Find(mstrWs.Range("b1").Text).Select







End Sub

 

[jolivanes]

@hartsie
Please don't quote unnecessarily. Just extra clutter we don't need.
Refer to a Post number or the person's moniker.

If you have data in the sheet where you copy into and you want to paste the copied data below the existing data, this should do that.
Like the second code, Post # 3, select any amount of headers, adjacent or not, and run this macro.

Sub Or_Maybe_So()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fnd As Range
Set sh1 = Worksheets("Source Sheet")    '<----- Change sheet name as required
Set sh2 = Worksheets("Destination Sheet")    '<----- Change sheet name as required
Application.ScreenUpdating = False
With sh2
    For Each c In Selection
        Set fnd = sh1.Rows(1).Find(c.Value, , , 1)
            If Not fnd Is Nothing Then
                sh1.Range(fnd.Offset(1).Address, sh1.Cells(Rows.Count, fnd.Column).End(xlUp).Address).Copy .Cells(.Rows.Count, c.Column).End(xlUp).Offset(1)
            End If
    Next c
End With
Application.ScreenUpdating = True
End Sub

 

[hartsie]

@jolivanes ok - by "quote" are you referring to my responses to the postings? I guess I haven't used the forum long enough to learn the nuances of the culture.

Thank you for the follow up.

 

[jolivanes]

@hartsie.
It is not that it is not allowed but like in Post #9 there are 150+ lines that don't mean anything because these lines are also in an earlier Post.
Thank you for your prompt response.

 

[hartsie]

@jolivanes I’ll try to be more aware going forward.

 

[hartsie]

@jolivanes Re:#9, This also works very well. I don't know if I will need to append the data for my current task. However, I will definitely be using this in the future. I am going to study the differences to learn how you changed the operation. Thank you!!!