Do this in bite size chunks. I'd start from the simplest (and most useful) steps first (from your description, I'd work backwards).
I'd start by using the Macro Recorder built into the VB Editor in Excel.
Setup your normal sheet as you would, "import" your data as you would, once you are ready to start manipulating your data as you need to, start the recorder.
Alt-F11, Macro > Record.
Now switch back to your sheets, and start doing your data manipulations in small steps (like if you transpose a bunch of headers first, use the recorder to show you how to do that).
After awhile of doing this, you'll start to see how you do all of this work via VBA. Once you start to get a handle on this, you can start writing procedures and functions that allow you to put repeat work into a module, so that you can
easily do all of the same work in a single line.
Here is a quick example...
This chunk of code, simply grabs "Col C" from Sheet 2, Copies it.
I then switch to Sheet1, search for "Col C", and I then paste the entire column from Sheet2 to Sheet1 in the corresponding Column.
Code:
Sub Macro()
Columns("C:C").Select
Selection.Copy
Sheets("Sheet1").Select
Cells.Find(What:="Col C", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Columns("C:C").Select
ActiveSheet.Paste
End Sub
Now, this seems very simple, and it is. However, if you have to perform this 10+ times, writing all this code repeatedly gets very tedious and time consuming.
You'll want to take advantage of Procedures (Called Subs in excel) and Functions (the only difference... Subs do work without returning values, functions can do work and return values).
So, I'd write a function like the following (it uses other functions that I'm not going to include, but it reads very easily):
(now, I will warn you, this is from another project I'm working on, so it's quite complicated, but if you can learn how this function works, your well on your way to understanding VBA).
Code:
Public Function CopyDataByHeader(SourceSheet As Worksheet, SourceHeader As String, DestinationSheet As Worksheet, _
DestinationHeader As String, Optional SourceStartRow As Long = 2) As Range
'
' Example: Call CopyDataByHeader(Sheet2, "Last Name", Sheet1, "LastName")
'
' Declare Variables
'*******************************************
Dim nCopyRow As Long
Dim nPasteRow As Long
Dim nCopyCol As Range
Dim nPasteCol As Range
Dim SelRange As Range
Dim DestRange As Range
' Declare the Array used to copy and paste the source to destination
Dim SourceArray() As Variant
Set nCopyCol = SearchForCellLocation(SourceHeader, SourceSheet, True)
Set nPasteCol = SearchForCellLocation(DestinationHeader, DestinationSheet, True)
' Figure out the last Rows used for Source and Destination ranges
nCopyRow = GetRowNumber(SourceSheet, SourceHeader, False)
nPasteRow = GetRowNumber(DestinationSheet, DestinationHeader, True)
' This sets SelRange with the actual Source Range from the Source Worksheet based on HeaderName
Set SelRange = SourceSheet.Cells.Range(Cells(SourceStartRow, nCopyCol.Column).Address, Cells(nCopyRow, nCopyCol.Column).Address)
' This sets DestRange with the actual Calculated Destination as a Range based on the requested HeaderName
' Code still has to be added to determine if it should start at the next blank row, or force start from where the user specifies
Set DestRange = DestinationSheet.Cells.Range(DestinationSheet.Cells(nPasteRow, nPasteCol.Column).Address, DestinationSheet.Cells(nPasteRow + SelRange.Rows.Count - 1, nPasteCol.Column).Address)
' Expand the Array
ReDim SourceArray(1 To SelRange.Rows.Count)
' Populate the Array from the Source range selection
SourceArray = SourceSheet.Range(SelRange.Address)
' This is what actually "dumps" the Source Array information into the Destination.
DestRange.Value = SourceArray
Set DestRange = Nothing
Set SelRange = Nothing
Erase SourceArray
End Function
This is a chunk of code that you would stick in a module (not a Class module however).
You would then "call" this function from your main program code like so:
Code:
Call CopyDataByHeader(Sheet2, "Col C", Sheet1, "Col C")
So you'll notice that with a single "line" of code, I can now repeat all of the above functions that was in our first example, but also takes the functionality to a more advanced level (This particular function allows me to search for both "source" and "destination" columns by the header name, potentially on different sheets (all within the same workbook mind you). I also happen to use a method which is much faster at copying whole columns of data using Arrays. They can be confusing at first, but are incredibly powerful once you understand them.
One of the best sources that I have found for simple raw information is Chip Pearson's online database of information : (Mods, feel free to kill this link if this isn't allow to cross post) :
CPearson.com Topic Index
I hope this helps direct you a little, There is no better way to learn this then simply rolling up your sleeves and digging into it... and having a project to work on gives you direction.
Happy learning/coding.
