Macro Help

[TONYr]

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?

 

[joel]

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

 

[Mike H]

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" & Lastrow).Copy _
Destination:=Workbooks("Newbook.xls").Sheets("Sheet1").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("Sheet1").Range("C1")
End Sub


Mike

 

[TONYr]

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]

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("Sheet1").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("Sheet1").Range("F1")

Mike