Copy entire worksheet from another workbook's worksheet without range names too.

[tfield98]

I want to manually (or with VBA) copy the all of a worksheet's cell values between workbooks. The source & destination worksheets would have the same name.

I tried use the "Move or Copy" option on the right-click pop-up menu. But this moves all of the source workbook range names to the destination workbook.

I thought about using the clipboard, but I'm worried I might run out of clipboard space. (Excel 2010: is there a maximum number of rows/data allowed on the clipboard other than available RAM?)

Are there any clever ways to copy a worksheet to another workbook and either copy no range names, or only the source worksheet range names (but not source workbook range names)?

Thanks!

 

[JoeMo]

This will copy the activesheet to a new workbook and remove all names.

Sub CopySheetSansNames()
Dim nm As Name
With ActiveSheet
    .Copy
End With
On Error Resume Next
For Each nm In ActiveWorkbook.Names
    nm.Delete
Next nm
End Sub

 

[Firefly2012]

You could copy the worksheet contents only eg:

With Activesheet.UsedRange
  .Copy
  Workbooks("Other Workbook.xlsx").Sheets("Some New Sheet").Range(.Cells(1,1).Address).PasteSpecial
End With