Auto Import From Other Doc

  • Thread starter Thread starter Gazz_85
  • Start date Start date
G

Gazz_85

What code would i have to put in so that everytime you clicked a button a
macro would automatically pull data from another excel sheet and drop it into
my workbook so i can do calculations with it???

Cheers
 
how about copying selected range and pasting it into the new doc via a
command button.

Go view-toolbars-control toolbox: click design mode then command button,
click where you want the buttton placed on your worksheet, while still in
design mode double click the command button this will open the vb editor
paste the following in there:


Private Sub CommandButton1_Click()

Workbooks("book1").Worksheets("Sheet1").Range("C1:C5").Copy
ActiveSheet.Paste
Destination:=Workbooks("book2").Worksheets("Sheet1").Range("c1:c5")
CutCopyMode = False

End Sub

obviously replace your names for workbooks and worksheets and ranges in the
above code.
Close the ditor and click to exit design mode.
Your command button should now copy and paste from one workbook to another.
 
Hi,

it threw an error for the colon after destination so i deleted it except it
doesnt paste it in the correct place it just sticks it in the cell which is
currently selected.

any suggestions???

Cheers

Destination:=
 
Both workbooks need to be open.

workbooks("someotherworkbook.xls").worksheets("Sheet2").range("a1:B99").copy _
destination:=activesheet.range("A1")
 
I think you can do some ADO stuff, but it's beyond me.

But your code could open the workbook, copy and paste the range and close the
workbook. You'd never know that it happened.

Dim wkbk as workbook
dim actwks as worksheet

set actwks = activesheet

application.screenupdating = false
set wkbk = workbooks.open(filename:="c:\somefile.xls", readonly:=true)
wkbk.worksheets("Sheet2").range("a1:B99").copy _
destination:=actwks.range("A1")
wkbk.close savechanges:=false
application.screenupdating = true
 
Back
Top