copy a range from another workbook

[Spencer Hutton]

i want to copy a range ("A:L") in another workbook.
C:\Workbooks\Data.xls

into my active workbook
i do not want the user to see the workbook "data" when it is open. i did
screenupdating = false on the active workbook, but "Data" still gets shown.
i am sure this is a simple statement, but i do not know everything about
VBA.

 

[Nick Hodge]

Spencer

Hide your 'data.xls' through the user interface first and then save it.
Then you can use code similar to below. (I have used 'False' in the open to
stop it being added to the MRU list...not sure it would anyway, but just in
case) and cleared the clipboard or with two whole columns copied it will ask
is you wish to save it.

Sub GetData()
Dim wbExt As Workbook
Set wbExt = Workbooks.Open("C:\Workbooks\Data.xls", _
, , , , , , , , , , , False)
wbExt.Worksheets("Sheet1").Range("A:L").Copy
'do what you want here
Application.CutCopyMode = False
wbExt.Close SaveChanges:=False
Set wbExt = Nothing
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)