copying sheet to new book

[rhombus4]

I know how to copy a sheet to a new book, however the sheet I want to copy has lots of formulas that relate to that book.

Also it has a few formats as well. I know you can pastee special and just use values but that would lose the other formats.

Maybe a macro could be the answer but not sure

Thanks

 

[VoG]

Not sure if you need the entire code. Here is a snippet

Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With

 

[rhombus4]

Not sure if you need the entire code. Here is a snippet

Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With

What I did was record new Macro to get Macro1 then inserted your code

Sub Macro1()
'
' Macro1 Macro


'
'
End Sub

Sub Macro1()
'
' Macro1 Macro
'
Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

End Sub

When I run it from the sheet I want to copy, I get a Runtime error9

Does it need something adding in the macro to opena new workbook and paste it there

 

[VoG]

Do you have a sheet called Sheet1?

 

[rhombus4]

Do you have a sheet called Sheet1?

Apologies i edited my sheet name to sheet 1 instead of sheet1.

It copies and opens a new sheet with the sheet in it, however where all the formulas are on the original sheet every one has #ref

 

[VoG]

That would happen if you closed the original workbook before saving the copied sheet. Try like this

Sub test()
Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
With ActiveWorkbook
    .SaveAs Filename:="fred.xls"
    .Close
End With
End Sub

 

[rhombus4]

That would happen if you closed the original workbook before saving the copied sheet. Try like this

Sub test()
Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
With ActiveWorkbook
    .SaveAs Filename:="fred.xls"
    .Close
End With
End Sub

I didnt close the original workbook, both were open when it opened the new book.

the top of the sheet to be copied has a reference which copies somebodys name, that part works great.

Its just below in the table that has formulas which are if statements if blank then blank else whatever is in the relevant formuala, all those are the ones that carry over the #ref, whereas the one with a formula of someones name is ok.

When I tried the new one above it save a copy of the sheet called fred in my documents and closed it. When I located it and opened it, it was the same names at top copied ok but all the formulas still have ref#

I dont want it to save then close, especially in a separate location

PS I'm assumin Excel2003, not sure if that makes a difference or not

 

[VoG]

Strange. Does your sheet refer to a closed workbook because I cannot reproduce that error.

 

[rhombus4]

Strange. Does your sheet refer to a closed workbook because I cannot reproduce that error.

No. It just flashes goes back to sheet1. I had to search for fred.xls because I wasnt sure where it had gone.

My workbook is in My documents but in a subfolder so not sure why the fred.xls wetn to the rot of my documents.

The first MAcro you did just opened Book1 then Book2 book3 etc if I clicked the macro again, the second one obviously closed the fred.xls then asked if i wanted to save again as already existing sheet

 

[rhombus4]

Just looking at the formulas in the original sheet which are copied from other sheets. The ones which are showing as #ref in the new workbook are formulas with indirect in them

This is the formula which generates the error and as they are copied down creates all the ref# error.

=IF(INDIRECT("'"&A1&"'!C5")="","",VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

I did manually change one of the formulas to a simple =Sheet2!C5 and when i run your macro it transfers te number which the formula generates. So must be the formula above which it has a problem with