copying to external workbook

[Mikeice]

Hi Guys still battling!

Have read threads but can't get this code to work.
WHat I need to accomplish is to copy the contents of Test.XLS sheet1
to Z:\July.xls sheet1 (THis sheet will be closed)

Just can't get it to work Please help.

Here is my code:

Private Sub CommandButton7_Click()

Dim wbk As Workbook
Dim wks As Worksheet
Dim SrcBook As Workbook


Set SrcBook = Active.Workbooks


Set wbk = Workbooks.Open("I:\July.xls")
Set wks = wbk.Sheet1


Application.ScreenUpdating = False

SrcBook.Sheets("Sheet1").UsedRange.Copy _
wbk:=wbk.Sheets _
("Sheet1").Range("A1")

SrcBook.Close (False)

Application.ScreenUpdating = True

End Sub


Thx in advance

[Norman Jones]

Hi Mikeice,

Try:

Private Sub CommandButton7_Click()

Dim wbk As Workbook
Dim wks As Worksheet
Dim SrcBook As Workbook


Set SrcBook = ActiveWorkbook


Set wbk = Workbooks.Open("I:\July.xls")
Set wks = wbk.Sheets("Sheet1")


Application.ScreenUpdating = False

SrcBook.Sheets("Sheet1").UsedRange.Copy Destination:= _
wks.Range("A1")

SrcBook.Close (False)

Application.ScreenUpdating = True

End Sub
---
Regards,
Norman



"Mikeice" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Guys still battling!
>
> Have read threads but can't get this code to work.
> WHat I need to accomplish is to copy the contents of Test.XLS sheet1
> to Z:\July.xls sheet1 (THis sheet will be closed)
>
> Just can't get it to work Please help.
>
> Here is my code:
>
> Private Sub CommandButton7_Click()
>
> Dim wbk As Workbook
> Dim wks As Worksheet
> Dim SrcBook As Workbook
>
>
> Set SrcBook = Active.Workbooks
>
>
> Set wbk = Workbooks.Open("I:\July.xls")
> Set wks = wbk.Sheet1
>
>
> Application.ScreenUpdating = False
>
> SrcBook.Sheets("Sheet1").UsedRange.Copy _
> wbk:=wbk.Sheets _
> ("Sheet1").Range("A1")
>
> SrcBook.Close (False)
>
> Application.ScreenUpdating = True
>
> End Sub
>
>
> Thx in advance
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile:
> http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=389659
>