Macro Help

T

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

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:D" & 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
 
T

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Connect Files 4
Macro attached to Menu Command Button 2
How to code it on macro ? 10
Help - I'm getting desperate 7
copying cells from one file to another file 4
Loop 1
Loop 1
Auto Update Second File 1

Top