Anyone have any idea why this won't work? All I am trying to do is copy the
contents on an entire worksheet from one workbook, and paste over the
contents of a sheet called "Data" in another workbook.

Set bk = Workbooks.Open(myFileName)
bk.Worksheets(1).Cells.Copy
ThisWorkbook.Worksheets("Data").Cells.Paste
bk.Close savechanges:=False

Paste is not a range method, but a worksheet method.

Set bk = Workbooks.Open(myFileName)
bk.Worksheets(1).Cells.Copy _
ThisWorkbook.Worksheets("Data").Cells
bk.Close savechanges:=False

Should work.

HTH,
Bernie
MS Excel MVP


"Steph" wrote in message ...
Anyone have any idea why this won't work? All I am trying to do is copy the contents on an entire
worksheet from one workbook, and paste over the contents of a sheet called "Data" in another
workbook.

Set bk = Workbooks.Open(myFileName)
bk.Worksheets(1).Cells.Copy
ThisWorkbook.Worksheets("Data").Cells.Paste
bk.Close savechanges:=False

You can also use

Set bk = Workbooks.Open(myFileName)
bk.Worksheets(1).Cells.Copy
ThisWorkbook.Worksheets("Data").Cells.PasteSpecial xlPasteAll
bk.Close savechanges:=False

--
Regards,
Tom Ogilvy



"Steph" wrote in message
...
Anyone have any idea why this won't work? All I am trying to do is copy
the contents on an entire worksheet from one workbook, and paste over the
contents of a sheet called "Data" in another workbook.

Set bk = Workbooks.Open(myFileName)
bk.Worksheets(1).Cells.Copy
ThisWorkbook.Worksheets("Data").Cells.Paste
bk.Close savechanges:=False