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 the


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.

"Gazz_85" wrote:

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

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:=

"Atishoo" wrote:

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 the


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.

"Gazz_85" wrote:

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

Both workbooks need to be open.

workbooks("someotherworkbook.xls").worksheets("She et2").range("a1:B99").copy _
destination:=activesheet.range("A1")



Gazz_85 wrote:

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

--

Dave Peterson

is there anyway to do this without both workbooks being open??

"Dave Peterson" wrote:

Both workbooks need to be open.

workbooks("someotherworkbook.xls").worksheets("She et2").range("a1:B99").copy _
destination:=activesheet.range("A1")



Gazz_85 wrote:

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

--

Dave Peterson

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







Gazz_85 wrote:

is there anyway to do this without both workbooks being open??

"Dave Peterson" wrote:

Both workbooks need to be open.

workbooks("someotherworkbook.xls").worksheets("She et2").range("a1:B99").copy _
destination:=activesheet.range("A1")



Gazz_85 wrote:

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

--

Dave Peterson


--

Dave Peterson