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
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