Using a command button to import data into excel file

[WeAreNotAlone]

Hi all.

I'm using MS Excel 2000

What I need is fairly simple, i think? I've put it into an example below.

I have two files e.g.
C:\DIR1\file1.xls and
C:\DIR2\file2.xls.

When I press a command button in file1 I want it to import the data from cells A1:A7 in file2 and copy the DATA VALUES ONLY from it into cells A1:A7 in file1.

Any help most appriciated.

Mally

 

[Remou]

Hi
I got the code below from recording an Excel macro. I then put
Call CopyAndPaste()
into the OnClick Event of a command button.

Sub CopyAndPaste()
Workbooks.Open Filename:="C:\Book1.xls"
Windows("Book1.xls").Activate
Range("A1:A7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

There are other ways to do this.
Did you find that external ranges did not suit?

 

[WeAreNotAlone]

Thanks for your help. I've never really done anything like this before so used the Excel help pages and worked it out.

I needed to copy two sets of data with the same button so wrote this code and it works ok for me.

Private Sub BUTTON1_Click()
Workbooks("EQP.xls").Activate
Sheets("1").Activate
ActiveSheet.Range("B5:B11").Copy

Workbooks("IMPLANTMSL&LPOHandoverMB.xls").Activate
Sheets("Master Import").Activate
ActiveSheet.Range("B5:B11").Select
ActiveSheet.Paste

Workbooks("EQP.xls").Activate
Sheets("1").Activate
ActiveSheet.Range("B14:B20").Copy

Workbooks("IMPLANTMSL&LPOHandoverMB.xls").Activate
Sheets("Master Import").Activate
ActiveSheet.Range("B14:B20").Select
ActiveSheet.Paste
End Sub

There might be a different way but this works ok.

Mally