merge two worksheets with different columns and data

Hi all,
I have two worksheets from different workbooks that I'd like to combine
to one worksheet. The two worksheets have different column names and
different data, meaning there aren't any common value that would link
them together.
So in sheet 1 I have columns:

Col1,Col2,Col3

and sheet 2:

Col4, Col5

I want them combined to Col1,Col2,Col3,Col4,Col5 with data from the
first sheet appended to the last row of Col3.
Any help is greatly appreciated!
Moon

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Moon" <[email protected]> wrote in message news:[email protected]...
> Hi all,
> I have two worksheets from different workbooks that I'd like to combine
> to one worksheet. The two worksheets have different column names and
> different data, meaning there aren't any common value that would link
> them together.
> So in sheet 1 I have columns:
>
> Col1,Col2,Col3
>
> and sheet 2:
>
> Col4, Col5
>
> I want them combined to Col1,Col2,Col3,Col4,Col5 with data from the
> first sheet appended to the last row of Col3.
> Any help is greatly appreciated!
> Moon
>

Hi David,
I've actually been to your site and have been fiddling with some of
your codes but I couldn't figure out how to append the data from sheet
1 to the last row, last column of sheet 2. The reason why I want to do
this is because data from sheet 1 doesn't relate to data in sheet 2.
The column headers would however be concatenated from sheet 1 to sheet
2 in row 1 of sheet 2. Hope I'm not too confusing here!
Thanks,
Moon
David McRitchie wrote:
> --
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Moon" <[email protected]> wrote in message
news:[email protected]...
> > Hi all,
> > I have two worksheets from different workbooks that I'd like to
combine
> > to one worksheet. The two worksheets have different column names
and
> > different data, meaning there aren't any common value that would
link
> > them together.
> > So in sheet 1 I have columns:
> >
> > Col1,Col2,Col3
> >
> > and sheet 2:
> >
> > Col4, Col5
> >
> > I want them combined to Col1,Col2,Col3,Col4,Col5 with data from the
> > first sheet appended to the last row of Col3.
> > Any help is greatly appreciated!
> > Moon
> >

Hi Moon,
Sorry that wasn't meant to be a reply, I hadn't even seen
the posting, don't know what happened.

Based on the original question, I came up with

Sub Macro1()
Dim wsSheet As Worksheet
Dim wsWorkbook As Workbook
Set wsWorkbook = ActiveWorkbook
Set wsSheet = ActiveSheet

Windows("workbookC_2.xls").Activate
Sheets("sheet1").Activate

Range("a:b").Copy
wsWorkbook.Activate
wsSheet.Activate
Range("D1").Select
ActiveSheet.Paste
End Sub

which would place the data from the second workbook columns
D:E to the right of Columns A:C of the current workbook.
Since you have to specify the second workbook name and
the sheet within that workbook within the macro, I don't really
see what is going to be gained from the macro if that is all it is to
do, because it might just be faster to open the second workbook
manually, make the selection in the desired worksheet manually
and paste back to the original worksheet. In any case for the
macro both workbooks must be open.

The last sentence looked confusing, but your reply cleared that up.
I don't understand why you want to waste the space within the used range in two quadrants. But it appears rather than

A1_1 B1_1 C1_1 D1_2 E1_2
A2_1 B1_1 C2_1 D2_2 E2_2

you want
A1_1 B1_1 C1_1
A2_1 B1_1 C2_1
D1_2 E1_2
D2_2 E2_2

Sub Macro1()
Dim wsSheet As Worksheet
Dim wsWorkbook As Workbook
Set wsWorkbook = ActiveWorkbook
Set wsSheet = ActiveSheet
'-- prepare to active cell on original sheet to receive paste
'-- based on content of last cell in column A, and offset
'-- over to column D i.e. 3 columns over
Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Select
'-- you have to specify the second workbook name and worksheet name
Windows("workbookC_2.xls").Activate
Sheets("sheet1").Activate
'-- can't paste entire columns except at top of another
'-- so have to reduce scope of the cells to be copied
Intersect(ActiveSheet.UsedRange, Range("a:b")).Copy
wsWorkbook.Activate
wsSheet.Activate
ActiveSheet.Paste
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Moon" <[email protected]> wrote in message news:[email protected]...
> Hi David,
> I've actually been to your site and have been fiddling with some of
> your codes but I couldn't figure out how to append the data from sheet
> 1 to the last row, last column of sheet 2. The reason why I want to do
> this is because data from sheet 1 doesn't relate to data in sheet 2.
> The column headers would however be concatenated from sheet 1 to sheet
> 2 in row 1 of sheet 2. Hope I'm not too confusing here!
> Thanks,
> Moon
> David McRitchie wrote:
> > --
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Moon" <[email protected]> wrote in message
> news:[email protected]...
> > > Hi all,
> > > I have two worksheets from different workbooks that I'd like to
> combine
> > > to one worksheet. The two worksheets have different column names
> and
> > > different data, meaning there aren't any common value that would
> link
> > > them together.
> > > So in sheet 1 I have columns:
> > >
> > > Col1,Col2,Col3
> > >
> > > and sheet 2:
> > >
> > > Col4, Col5
> > >
> > > I want them combined to Col1,Col2,Col3,Col4,Col5 with data from the
> > > first sheet appended to the last row of Col3.
> > > Any help is greatly appreciated!
> > > Moon
> > >
>

Hi David,
I tried your last code and it worked perfectly except that I need to
have the column headers from the first sheet pasted to the first row of
sheet two.
You're totaly right, a simple copy paste would work as well but this
has to be done by someone else on a lot of different sheets with alot
of columns so this would minimize errors.
Thanks so much for your help!
Moon
David McRitchie wrote:
> Hi Moon,
> Sorry that wasn't meant to be a reply, I hadn't even seen
> the posting, don't know what happened.
>
> Based on the original question, I came up with
>
> Sub Macro1()
> Dim wsSheet As Worksheet
> Dim wsWorkbook As Workbook
> Set wsWorkbook = ActiveWorkbook
> Set wsSheet = ActiveSheet
>
> Windows("workbookC_2.xls").Activate
> Sheets("sheet1").Activate
>
> Range("a:b").Copy
> wsWorkbook.Activate
> wsSheet.Activate
> Range("D1").Select
> ActiveSheet.Paste
> End Sub
>
> which would place the data from the second workbook columns
> D:E to the right of Columns A:C of the current workbook.
> Since you have to specify the second workbook name and
> the sheet within that workbook within the macro, I don't really
> see what is going to be gained from the macro if that is all it is to
> do, because it might just be faster to open the second workbook
> manually, make the selection in the desired worksheet manually
> and paste back to the original worksheet. In any case for the
> macro both workbooks must be open.
>
> The last sentence looked confusing, but your reply cleared that up.
> I don't understand why you want to waste the space within the used
range in two quadrants. But it appears rather than
>
> A1_1 B1_1 C1_1 D1_2 E1_2
> A2_1 B1_1 C2_1 D2_2 E2_2
>
> you want
> A1_1 B1_1 C1_1
> A2_1 B1_1 C2_1
> D1_2 E1_2
> D2_2 E2_2
>
> Sub Macro1()
> Dim wsSheet As Worksheet
> Dim wsWorkbook As Workbook
> Set wsWorkbook = ActiveWorkbook
> Set wsSheet = ActiveSheet
> '-- prepare to active cell on original sheet to receive paste
> '-- based on content of last cell in column A, and offset
> '-- over to column D i.e. 3 columns over
> Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Select
> '-- you have to specify the second workbook name and worksheet
name
> Windows("workbookC_2.xls").Activate
> Sheets("sheet1").Activate
> '-- can't paste entire columns except at top of another
> '-- so have to reduce scope of the cells to be copied
> Intersect(ActiveSheet.UsedRange, Range("a:b")).Copy
> wsWorkbook.Activate
> wsSheet.Activate
> ActiveSheet.Paste
> End Sub
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Moon" <[email protected]> wrote in message
news:[email protected]...
> > Hi David,
> > I've actually been to your site and have been fiddling with some of
> > your codes but I couldn't figure out how to append the data from
sheet
> > 1 to the last row, last column of sheet 2. The reason why I want to
do
> > this is because data from sheet 1 doesn't relate to data in sheet
2.
> > The column headers would however be concatenated from sheet 1 to
sheet
> > 2 in row 1 of sheet 2. Hope I'm not too confusing here!
> > Thanks,
> > Moon
> > David McRitchie wrote:
> > > --
> > > ---
> > > HTH,
> > > David McRitchie, Microsoft MVP - Excel [site changed Nov.
2001]
> > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
> > >
> > > "Moon" <[email protected]> wrote in message
> > news:[email protected]...
> > > > Hi all,
> > > > I have two worksheets from different workbooks that I'd like to
> > combine
> > > > to one worksheet. The two worksheets have different column
names
> > and
> > > > different data, meaning there aren't any common value that
would
> > link
> > > > them together.
> > > > So in sheet 1 I have columns:
> > > >
> > > > Col1,Col2,Col3
> > > >
> > > > and sheet 2:
> > > >
> > > > Col4, Col5
> > > >
> > > > I want them combined to Col1,Col2,Col3,Col4,Col5 with data from
the
> > > > first sheet appended to the last row of Col3.
> > > > Any help is greatly appreciated!
> > > > Moon
> > > >
> >

Then use the 1st attempt. That pastes the sheet that you
are not on onto the current sheet at the first row.

"Moon" <[email protected]> wrote in message news:[email protected]...
> Hi David,
> I tried your last code and it worked perfectly except that I need to
> have the column headers from the first sheet pasted to the first row of
> sheet two.
> You're totaly right, a simple copy paste would work as well but this
> has to be done by someone else on a lot of different sheets with alot
> of columns so this would minimize errors.
> Thanks so much for your help!
> Moon
> David McRitchie wrote:
> > Hi Moon,
> > Sorry that wasn't meant to be a reply, I hadn't even seen
> > the posting, don't know what happened.
> >
> > Based on the original question, I came up with
> >
> > Sub Macro1()
> > Dim wsSheet As Worksheet
> > Dim wsWorkbook As Workbook
> > Set wsWorkbook = ActiveWorkbook
> > Set wsSheet = ActiveSheet
> >
> > Windows("workbookC_2.xls").Activate
> > Sheets("sheet1").Activate
> >
> > Range("a:b").Copy
> > wsWorkbook.Activate
> > wsSheet.Activate
> > Range("D1").Select
> > ActiveSheet.Paste
> > End Sub
> >
> > which would place the data from the second workbook columns
> > D:E to the right of Columns A:C of the current workbook.
> > Since you have to specify the second workbook name and
> > the sheet within that workbook within the macro, I don't really
> > see what is going to be gained from the macro if that is all it is to
> > do, because it might just be faster to open the second workbook
> > manually, make the selection in the desired worksheet manually
> > and paste back to the original worksheet. In any case for the
> > macro both workbooks must be open.
> >
> > The last sentence looked confusing, but your reply cleared that up.
> > I don't understand why you want to waste the space within the used
> range in two quadrants. But it appears rather than
> >
> > A1_1 B1_1 C1_1 D1_2 E1_2
> > A2_1 B1_1 C2_1 D2_2 E2_2
> >
> > you want
> > A1_1 B1_1 C1_1
> > A2_1 B1_1 C2_1
> > D1_2 E1_2
> > D2_2 E2_2
> >
> > Sub Macro1()
> > Dim wsSheet As Worksheet
> > Dim wsWorkbook As Workbook
> > Set wsWorkbook = ActiveWorkbook
> > Set wsSheet = ActiveSheet
> > '-- prepare to active cell on original sheet to receive paste
> > '-- based on content of last cell in column A, and offset
> > '-- over to column D i.e. 3 columns over
> > Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Select
> > '-- you have to specify the second workbook name and worksheet
> name
> > Windows("workbookC_2.xls").Activate
> > Sheets("sheet1").Activate
> > '-- can't paste entire columns except at top of another
> > '-- so have to reduce scope of the cells to be copied
> > Intersect(ActiveSheet.UsedRange, Range("a:b")).Copy
> > wsWorkbook.Activate
> > wsSheet.Activate
> > ActiveSheet.Paste
> > End Sub
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Moon" <[email protected]> wrote in message
> news:[email protected]...
> > > Hi David,
> > > I've actually been to your site and have been fiddling with some of
> > > your codes but I couldn't figure out how to append the data from
> sheet
> > > 1 to the last row, last column of sheet 2. The reason why I want to
> do
> > > this is because data from sheet 1 doesn't relate to data in sheet
> 2.
> > > The column headers would however be concatenated from sheet 1 to
> sheet
> > > 2 in row 1 of sheet 2. Hope I'm not too confusing here!
> > > Thanks,
> > > Moon
> > > David McRitchie wrote:
> > > > --
> > > > ---
> > > > HTH,
> > > > David McRitchie, Microsoft MVP - Excel [site changed Nov.
> 2001]
> > > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > > Search Page:
> http://www.mvps.org/dmcritchie/excel/search.htm
> > > >
> > > > "Moon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > > Hi all,
> > > > > I have two worksheets from different workbooks that I'd like to
> > > combine
> > > > > to one worksheet. The two worksheets have different column
> names
> > > and
> > > > > different data, meaning there aren't any common value that
> would
> > > link
> > > > > them together.
> > > > > So in sheet 1 I have columns:
> > > > >
> > > > > Col1,Col2,Col3
> > > > >
> > > > > and sheet 2:
> > > > >
> > > > > Col4, Col5
> > > > >
> > > > > I want them combined to Col1,Col2,Col3,Col4,Col5 with data from
> the
> > > > > first sheet appended to the last row of Col3.
> > > > > Any help is greatly appreciated!
> > > > > Moon
> > > > >
> > >
>

That works but how do I get the data (not including the headers) from
sheet 1 to append to the last row, last column of sheet 2.
Your second code did that but it also copied the headers. I'd like the
headers to be pasted to the first row of the second sheet.
Thanks!
Moon

Hi Moon,
The best way to learn this is to record a macro, and
that might be all that you need in this case. I don't know.
Usually one records a macro to see what kind of instructions
might be helpful to solve a problem and them look up the
instructions in HELP.

I think between you recording a macro and what I presented
as switching to a sheet in the other workbook and returning
can be cannibalized and used with your coding to provide what
you want. If you can't figure it out after spending two hours
on it then post back and someone will answer.

Recording a macro is from the Tools menu, and more information
is in help.

The macro will record what you do. Sound like when you record
you want to do the following:

Go to the second sheet and insert 3 empty columns by
sleeting columns A:C then Insert (menu), Columns

Return to original sheet and copy columns A:C using Ctrl+C
Return to second sheet select cell A1 and paste (Ctrl+V)

Stop recording with the square button.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Moon" <[email protected]> wrote in message news:[email protected]...
> That works but how do I get the data (not including the headers) from
> sheet 1 to append to the last row, last column of sheet 2.
> Your second code did that but it also copied the headers. I'd like the
> headers to be pasted to the first row of the second sheet.
> Thanks!
> Moon
>