Copy - paste special values only

[Pedro Morais]

Hey guys,

I'm looking for sum code to select all sheets in a workbook, copy all cells and paste values only.
Can anyone help me with that?

Cheers
Pedro

 

[dafan]

Use the macro recorder..

 

[Pedro Morais]

Thanks for the answer, probably i didnt explain myself too well...

the number and name of sheets will vary... over 60...
so i need a formula that selects all sheets instead of going selecting one by one.
I dont know how to do that in excel or the code to do it in vba.

Cheers

 

[dafan]

Dim ws As Worksheet


For Each ws In Sheets


    If ws.Visible Then ws.Select (False)


Next

Credits to NateO

Edit this to fit your needs:

    Cells.Select
    Selection.Copy
    Workbookname here.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E11").Select

 

[Pedro Morais]

Cheers!

That worked perfectly