Copy Worksheet to new Workbook

[johnston_joan]

How can I copy a worksheet to a new workbook without the formulas referencing the old workbook? ie, i want the formulas to reference the new workbook.

 

[Jim North]

Do you mean you want the code that does the copying to be in the new workbook?

 

[johnston_joan]

Actually, it's simpler than that. I want to move/copy one worksheet to a new workbook without the formula reference to the old workbook, but rather, to the new workbook

 

[Jim North]

ThisWorkbook.Worksheets.Copy Before:=Workbooks("Book1").Sheets(1)

 

[applebyd]

The easiest way is to click in the top left
corner between the A and1 to highlight the whole sheet.

Then control C to copy.

Change to the new book. Click in cell A1
then Right Click the mouse and select
Paste Special.

Paste Values and then Formats. There may be a quicker way but I don't know it.


HTH

DaveA

 

[jfromm]

This is a really good question. I don't have a proper answer for you, but one method would be to do the paste and then search the entire sheet for the original workbook name in the formulas '[workbookname]' and replace it with nothing (leave the replace with option blank). This will work, but it's not a real fix.
Hope it helps.
J