[RESOLVED] Simple copy sheet with no formula macro

[FrameTech]

Trying to copy a worksheet to another workbook (got that far)

However, I only want the values and format of the original worksheet to be copied accross. No formulas.

Is this doable - I have searched for the last 2 hours on it and can not find any reference.

[Frans C]

I think you need two pastespecial operations to achieve that.

Try something like this:

Sheets("Sheet1").Cells.Copy
    Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[DKenny]

After you copy it over, you can then Select all cells, copy them and then PasteSpecial, pasting only the values back onto the cells. This will remove the formulas.

[FrameTech]

Thanks guys what you suggest works fine - I asked a tad too soon.

This is what I have done

'Macro to copy over itself to remove formulas
Sub copysheet()
 
    Sheets("Master").Select
    Range("A1:I131").Select
    Selection.Copy
    Sheets("Master").Select
    
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

Then called the macro like this

'copies Frame sheet to new workbook
Application.Workbooks("getitright").Sheets("Master").Copy Before:=Application.Workbooks("Sheet1").Sheets("Sheet1")
'calls macro to remove formula
copysheet
'calls macro to remove extra data
clearextra
'names the new sheet by the cell value
Application.Workbooks("Sheet1").Sheets("Master").Select
ActiveSheet.Name = Range("A6").Value
'returns to activate the original workbook and worksheet
Application.Workbooks("getitright").Sheets("Master").Activate

Seems to be a long way around but it works and I may or may not be able to shorten it later.

Cheers and thank you for your input. Muchly appreciated.