[RESOLVED] Simple copy sheet with no formula macro

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.

I think you need two pastespecial operations to achieve that.

Try something like this:

VB Code:

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

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.

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

This is what I have done

VB Code:

'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

VB Code:

'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.