copying to external workbook

  • Thread starter Thread starter Mikeice
  • Start date Start date
M

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
 
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
 
Back
Top