Macro to populate master spreadsheet from others based on headers
 

Hi all:

I'm developing a tool to amalgamate all of our program plans into one
worksheet of one workbook. I am trying to figure out how to copy the
data from one worksheet to another, matching column headers.

I can restrict the template so they cannot add/remove columns, but
staff are going to be listed in the header row and we may have more
added.

This is what I am starting with – I’m not sure how much of it will
work, or how it will work.

Sub GetColumn()

' declarations to capture currently selected column/row address
Dim iRow As Long
Dim iColumn As Long


' I need an object to hold the current column
Dim cColumn As Long
' cRow will be the last row of data in the worksheet; as each source
will likely be different, I need to make sure that it appends the data
to the master worksheet rather than replacing existing.
Dim cRow As Long

‘ aCell is intended to grab the active cell contents and use it for
the search criteria.
Dim aCell As ActiveCell

‘ this is where the active cell is captured
iRow = ActiveCell.Row
iColumn = ActiveCell.Column

‘ for the find by column header. I just can’t manage to wrap my head
around all this.
For cColumn = iColumn + 1 To 45
Sheets("Master2").Select
Cells.Find(What:=aCell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


Range("A2") = iColumn
ActiveCell.SpecialCells(xlLastCell).Select
Range("A3") = iRow

End Sub

Thoughts? Questions?


Steven