merge two worksheets with different columns and data

[Moon]

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

 

[David McRitchie]

--

 

[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

--
---
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

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

 

[David McRitchie]

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

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

--
---
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

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

 

[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

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

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

--
---
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

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

 

[David McRitchie]

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

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

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

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

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

 

[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

 

[David McRitchie]

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.

--