Hi all,

I want to know if its possible to select all data from a workbook on
Sheet1 and paste it into my current open workbook without opening it.

If its possible, how would I go about doing this?

All help welcome.....cheers.


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=518700

You could do it with ADO, but why bother, open it and close it, it will be
quick, and by setting ScreenUpdating to false, you can hide it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gti_jobert" wrote
in message ...

Hi all,

I want to know if its possible to select all data from a workbook on
Sheet1 and paste it into my current open workbook without opening it.

If its possible, how would I go about doing this?

All help welcome.....cheers.


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=518700

Cheers bob, I will do as you suggested! A quick question though - I hav
a cmdButton on a sheet and am trying to select a range but it kee
coming up with an error "Method 'Range' of Object '_Worksheet' Failed"
I have the following code;


Code
-------------------
Private Sub cmdGet_Click()

If Not FileExists("S:\MHE Monitor\subcon.xls") Then
MsgBox "The file doesn't exist!"
Else
Workbooks.Open "S:\MHE Monitor\subcon.xls"
Sheets("Sheet1").Select
ActiveSheet.Cells(1, 1).Select
*Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Copy*
Workbooks("Subcon Out.xls").Activate
ActiveSheet.Cells(1, 3).Select
'Selection.Paste
'Workbooks("subcon.xls").Close SaveChanges:=False
End If

End Su
-------------------

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=51870

Private Sub cmdGet_Click()

If Not FileExists("S:\MHE Monitor\subcon.xls") Then
MsgBox "The file doesn't exist!"
Else
Workbooks.Open "S:\MHE Monitor\subcon.xls"
Sheets("Sheet1").Select
With Activesheet
.Cells(1, 1).Select
.Range(.Range("A1"), _
ActiveCell.SpecialCells(xlLastCell)).Copy
End With
Workbooks("Subcon Out.xls").Activate
ActiveSheet.Cells(1, 3).Select
'Selection.Paste
'Workbooks("subcon.xls").Close SaveChanges:=False
End If

End Sub

--
Regards,
Tom Ogilvy


"gti_jobert" wrote
in message ...

Cheers bob, I will do as you suggested! A quick question though - I have
a cmdButton on a sheet and am trying to select a range but it keep
coming up with an error "Method 'Range' of Object '_Worksheet' Failed".
I have the following code;


Code:
--------------------
Private Sub cmdGet_Click()

If Not FileExists("S:\MHE Monitor\subcon.xls") Then
MsgBox "The file doesn't exist!"
Else
Workbooks.Open "S:\MHE Monitor\subcon.xls"
Sheets("Sheet1").Select
ActiveSheet.Cells(1, 1).Select
*Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Copy*
Workbooks("Subcon Out.xls").Activate
ActiveSheet.Cells(1, 3).Select
'Selection.Paste
'Workbooks("subcon.xls").Close SaveChanges:=False
End If

End Sub
--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=518700