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?