I have 2 Workbooks (file1.xls & File2.xls).

In file1 columns a,b,c & d are populated with data, this is the same for
file2.

I need to extract the data in colums a & d in file1 and a & b in file2 and
place it into a new workbook, can this be done via macro?

I sd column A on boths sheets an ID and you want to put in the new
workbook the ID in column A , the data from book1 in column b and the
data from book2 in column C?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146809

Hi,

Try this

Sub Merge()
Set newBook = Workbooks.Add
With newBook
.SaveAs Filename:="newbook.xls"
End With
mypath = "C:\" 'change to suit
Workbooks.Open Filename:=mypath & "File1.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:D" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("A1")
Workbooks("Newbook.xls").Sheets("Sheet1").Columns( "B:C").Delete
Shift:=xlToLeft

Workbooks.Open Filename:=mypath & "File2.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:B" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("C1")
End Sub


Mike

"TONYr" wrote:

I have 2 Workbooks (file1.xls & File2.xls).

In file1 columns a,b,c & d are populated with data, this is the same for
file2.

I need to extract the data in colums a & d in file1 and a & b in file2 and
place it into a new workbook, can this be done via macro?

Cheers Mike,

it worked a treat, thanks for the help. One other question if in File2 I
had 2 worksheets (Sheet1 & Sheet2) what code would I need to add to get it to
pull the data from both sheets (Same format applies to both sheets ie. I
would need to pull data from column a in sheet1 and colum a in sheet 2)


Cheers

T

"Mike H" wrote:

Hi,

Try this

Sub Merge()
Set newBook = Workbooks.Add
With newBook
.SaveAs Filename:="newbook.xls"
End With
mypath = "C:\" 'change to suit
Workbooks.Open Filename:=mypath & "File1.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:D" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("A1")
Workbooks("Newbook.xls").Sheets("Sheet1").Columns( "B:C").Delete
Shift:=xlToLeft

Workbooks.Open Filename:=mypath & "File2.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:B" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("C1")
End Sub


Mike

"TONYr" wrote:

I have 2 Workbooks (file1.xls & File2.xls).

In file1 columns a,b,c & d are populated with data, this is the same for
file2.

I need to extract the data in colums a & d in file1 and a & b in file2 and
place it into a new workbook, can this be done via macro?

Hi,

Explicitly name the sheets when working with File 2

Workbooks.Open Filename:=mypath & "File2.xls"
Lastrow = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Range("A1:B" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("C1")


Lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Range("A1:B" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("F1")

Mike

"TONYr" wrote:

Cheers Mike,

it worked a treat, thanks for the help. One other question if in File2 I
had 2 worksheets (Sheet1 & Sheet2) what code would I need to add to get it to
pull the data from both sheets (Same format applies to both sheets ie. I
would need to pull data from column a in sheet1 and colum a in sheet 2)


Cheers

T

"Mike H" wrote:

Hi,

Try this

Sub Merge()
Set newBook = Workbooks.Add
With newBook
.SaveAs Filename:="newbook.xls"
End With
mypath = "C:\" 'change to suit
Workbooks.Open Filename:=mypath & "File1.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:D" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("A1")
Workbooks("Newbook.xls").Sheets("Sheet1").Columns( "B:C").Delete
Shift:=xlToLeft

Workbooks.Open Filename:=mypath & "File2.xls"
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:B" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Shee t1").Range("C1")
End Sub


Mike

"TONYr" wrote:

I have 2 Workbooks (file1.xls & File2.xls).

In file1 columns a,b,c & d are populated with data, this is the same for
file2.

I need to extract the data in colums a & d in file1 and a & b in file2 and
place it into a new workbook, can this be done via macro?