Loop through headers and copy and paste

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,712
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@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.

VBA Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@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

Well-known Member
Joined
Sep 5, 2004
Messages
1,712
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@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

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,924
Messages
5,639,025
Members
417,064
Latest member
oryngirl

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
Top