I could use some assistance trying to copy columns of data from one workbook to another in Excel 03'. What I have in
workbook1 is 'x' number of worksheets. Each worksheet has column/attribute names in row 1. Row 2 contains corresponding
metadata/characteristics (such as data type, data length, etc.) about the column/attribute names in row 1. Rows 3 thru 'y'
contain actual data relating to the column name. There can also be blank rows between rows 3 and 'y'. Separately, outside
of the columns that have actual attribute names listed in row 1, there can be comments to clarify what the data means (so if
the last column with an attribute name is 'F', the user can put comments from column 'G' on but there will not be a column
header from 'G' on.
Workbook2 is what I'll call a template workbook. It also contains a number of worksheets.
There could be more or fewer worksheets than what is in workbook1. The pattern for each worksheet in rows 1 and 2 is similar
in nature to what I described for workbook1 (attribute names and metadata). The remainder of the rows in the worksheets are
What I need to do is to match the worksheets names in workbook1 to the worksheet names in the template
workbook. Once I match up the worksheets based on worksheet name, I then need to try to match up the column names from row 1
in workbook1 with the column names in row 1 in the template workbook. These columns may or may not be in the same order. If
I match up the column names, I need to copy the data from rows 3 thru 'Y' for that specific column from workbook 1 into the
rows 3 thru 'Y' in corresponding column in the template workbook. I can't simply copy the entire column from workbook1 to
the template workbook because the metadata characteristics in row 2 may have changed. This is why I only want to copy rows 3
I have managed to put together the logic to match up on the worksheets and the column names. What I'm having
difficulty with is putting together the code that will find the last piece of data for a given column (again, where there
could be multiple blank rows in the column) then selecting rows 3 to 'x' in the workbook1 and pasting it into the template
workbook starting in row 3.
Separately, for any column of data in workbook1 that doesn't match to a column in
the template workbook I plan on appending the entire column of data in the next blank column in the template. I then also
plan on copying the various comments from workbook1 into the template workbook after any of the appended columns (if there
are any). I believe I can figure out this piece of the logic unless someone feels this might be tricky?
admit that I'm new to coding macros and have not had any formal training in using vba so I'm struggling to pull together the
logic to copy rows 3 thru y to the template. Any help/suggestions you can provide would be greatly appreciated.
Here is what a worksheet in the template workbook might look like:
Group CodeGroup Code NameGroup Comment Pointer NumberPK
DECIMAL (11, 0)
Here is what a worksheet in workbook1 might look like:
Group DescriptionGroup CodeGroup Comment Pointer NumberGroup Code Name
VARCHAR (100)New carsAUxyzAutoUsed bikesMTRCYabcMotorcycleUsed trucksTRKdefTruckTrucks may be renamed laterList is not
complete as of 2/19/08