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]...
>
> 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
>

[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.

---
Regards,
Norman



"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Kstalker]

Thanks for that Norman.

Again it was just what I needed.

Regards

Kristan