Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)

One way if you have a key field that is unique in each worksheet, but common
between the 3 worksheets.

(I'm gonna use Location as that unique key)

create a new worksheet
copy all the data in each of the Location columns to this new worksheet--all in
column A--no gaps and only one header.

Use the technique shown at Debra Dalgleish's site to create a list of unique
items in column B of that new sheet
http://www.contextures.com/xladvfilter01.html#FilterUR

Then delete column A.

Now use a bunch of =vlookup()'s to retrieve each of the fields you're interested
in from each of the other 3 worksheets.



wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)

--

Dave Peterson

What ties a row in workbook1 uniquely with workbook2.

Location and method?

Location?

Method?

something else?

--
Regards,
Tom Ogilvy

" wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)

Thanks Dave and Tom, for your responses.

I'll try and elaborate further on this situation as ultimately I'm
trying to create a macro that would replace the manual copy and paste
routine.

The Target workbook starts out empty except for one row of column
headings - the column headers are established by me.

I receive the source workbooks and currently, starting with the first
Source workbook,
copy my preferred column from the source and paste it into the
appropriate Target workbook column.
I do this for all the columns I need from the source workbook, I then
work through all my source workbooks until I have consolidated the
source data into one long table in the target workbook.

The source workbooks do include columns which I don't necessarily have
an interest in and the columns are not laid out in the same order as
the Target table.

Note - this is a nauseatingly manual and repetitive task!!!

I dabbled with trying to create macros to do it for me.

I can create a macro to pull in the data from the first source
workbook, but the problem is it has hardcoded values (range values of
the Source data columns) which may not be relevant next week when I
receive a new source file!
Also my preferred columns within the source files don't always start on
row 2 and are not necessarily laid out in the order of my Target table.

The source workbooks are from separate sources and the data from
workbook 1 is not related/linked to workbook 2, other than they share
common data I need to analyse further.

BTW I take my hat off to each and every person who can create Excel VBA
routines - I've been on this for three days and it's taking me longer
to work out (all the workings of Range, Worksheet.Activate, Offset
etcetra), than it would to do my manual cut n paste method. <g



Tom Ogilvy wrote:
What ties a row in workbook1 uniquely with workbook2.

Location and method?

Location?

Method?

something else?

--
Regards,
Tom Ogilvy

" wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)

It sounds like there's lots of variation between the different iterations of
workbooks. And I don't see enough detail to help mechanize your procedure.



wrote:

Thanks Dave and Tom, for your responses.

I'll try and elaborate further on this situation as ultimately I'm
trying to create a macro that would replace the manual copy and paste
routine.

The Target workbook starts out empty except for one row of column
headings - the column headers are established by me.

I receive the source workbooks and currently, starting with the first
Source workbook,
copy my preferred column from the source and paste it into the
appropriate Target workbook column.
I do this for all the columns I need from the source workbook, I then
work through all my source workbooks until I have consolidated the
source data into one long table in the target workbook.

The source workbooks do include columns which I don't necessarily have
an interest in and the columns are not laid out in the same order as
the Target table.

Note - this is a nauseatingly manual and repetitive task!!!

I dabbled with trying to create macros to do it for me.

I can create a macro to pull in the data from the first source
workbook, but the problem is it has hardcoded values (range values of
the Source data columns) which may not be relevant next week when I
receive a new source file!
Also my preferred columns within the source files don't always start on
row 2 and are not necessarily laid out in the order of my Target table.

The source workbooks are from separate sources and the data from
workbook 1 is not related/linked to workbook 2, other than they share
common data I need to analyse further.

BTW I take my hat off to each and every person who can create Excel VBA
routines - I've been on this for three days and it's taking me longer
to work out (all the workings of Range, Worksheet.Activate, Offset
etcetra), than it would to do my manual cut n paste method. <g

Tom Ogilvy wrote:
What ties a row in workbook1 uniquely with workbook2.

Location and method?

Location?

Method?

something else?

--
Regards,
Tom Ogilvy

" wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)



--

Dave Peterson

I managed to get a result - I used MS Query and did the whole lot in 30
minutes.
A simple UNION ALL on the four files ensured I returned the results I
wanted, and no VBA involved!

After driving myself nutty with Excel VBA, I salute you guys out there
who can 'walk the walk' - I just can't get my head around it to do what
I wanted.

Anyway, a result for, just thought I'd let anyone who was interested,
know what I did!

cheers for the responses.

phil


Dave Peterson wrote:
It sounds like there's lots of variation between the different iterations of
workbooks. And I don't see enough detail to help mechanize your procedure.



wrote:

Thanks Dave and Tom, for your responses.

I'll try and elaborate further on this situation as ultimately I'm
trying to create a macro that would replace the manual copy and paste
routine.

The Target workbook starts out empty except for one row of column
headings - the column headers are established by me.

I receive the source workbooks and currently, starting with the first
Source workbook,
copy my preferred column from the source and paste it into the
appropriate Target workbook column.
I do this for all the columns I need from the source workbook, I then
work through all my source workbooks until I have consolidated the
source data into one long table in the target workbook.

The source workbooks do include columns which I don't necessarily have
an interest in and the columns are not laid out in the same order as
the Target table.

Note - this is a nauseatingly manual and repetitive task!!!

I dabbled with trying to create macros to do it for me.

I can create a macro to pull in the data from the first source
workbook, but the problem is it has hardcoded values (range values of
the Source data columns) which may not be relevant next week when I
receive a new source file!
Also my preferred columns within the source files don't always start on
row 2 and are not necessarily laid out in the order of my Target table.

The source workbooks are from separate sources and the data from
workbook 1 is not related/linked to workbook 2, other than they share
common data I need to analyse further.

BTW I take my hat off to each and every person who can create Excel VBA
routines - I've been on this for three days and it's taking me longer
to work out (all the workings of Range, Worksheet.Activate, Offset
etcetra), than it would to do my manual cut n paste method. <g

Tom Ogilvy wrote:
What ties a row in workbook1 uniquely with workbook2.

Location and method?

Location?

Method?

something else?

--
Regards,
Tom Ogilvy

" wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)



--

Dave Peterson