copy worksheet

[Kstalker]

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.


==

 

[Tim Williams]

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

Tim


"Kstalker" <[email protected]>
wrote in message
news:[email protected]...

 

[Norman Jones]

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.

 

[Kstalker]

Thanks for that Norman.

Again it was just what I needed.

Regards

Kristan