Back again with another copy conundrum.

Have read through various threads and still cannot get the VBA to do
what I need.

I need to copy the used range of a single worksheet in a closed
workbook and then drop it in to an existing worksheet in the active
workbook.

As always help greatly appreciated.


==


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=386667

Is there any reason why you can't open the workbook and copy the
sheet?

Tim


"Kstalker"
wrote in message
...

Back again with another copy conundrum.

Have read through various threads and still cannot get the VBA to do
what I need.

I need to copy the used range of a single worksheet in a closed
workbook and then drop it in to an existing worksheet in the active
workbook.

As always help greatly appreciated.


==


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread:
http://www.excelforum.com/showthread...hreadid=386667

Hi Kristan,

Open book1, effect the copy operation, close Book1.

If this operation needs to be hidden, set ScreenUpdating to False before
Boo1 is opened and restore it to true after Book1 is closed.

Perhaps, something like:

Sub One()
Dim SrcBook As Workbook
Dim DestBook As Workbook
Dim MyPath As String

Set MyPath = Application.DefaultFilePath '<<==Change

If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\"

Set DestBook = ActiveWorkbook
Set SrcBook = Workbooks.Open(MyPath & "YourBookName.xls")

Application.ScreenUpdating = False

SrcBook.Sheets("SheetToCopy").UsedRange.Copy _
Destination:=DestBook.Sheets _
("DestinationSheet").Range("A1")

SrcBook.Close (False)

Application.ScreenUpdating = True

End Sub

Amend the path, workbook and sheet names to suit.

---
Regards,
Norman



"Kstalker" wrote in
message ...

Back again with another copy conundrum.

Have read through various threads and still cannot get the VBA to do
what I need.

I need to copy the used range of a single worksheet in a closed
workbook and then drop it in to an existing worksheet in the active
workbook.

As always help greatly appreciated.


==


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=386667

Thanks for that Norman.

Again it was just what I needed.

Regards

Kristan


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=386667