Hi Manos,

The code below will loop through all the sheets in a book and paste
special as values all the cells....




Sub test()

Dim sheet As Object

For Each sheet In ActiveWorkbook.Sheets

sheet.Cells.Copy
sheet.Range("a1").PasteSpecial (xlValues)

Next sheet
Application.CutCopyMode = False


End Sub

Regards
Ankur
www.xlmacros.com

On Dec 19, 2:45 pm, Manos wrote:
Dear all

I have create a big excel file (2003) with 30 different sheets and that
contains a huge amoun of omulas and links with an ERP system.
This means that there are links and formulas that whe someone else can ty to
open the file he can see either errors or values, becuase he do not have
access to ERP folder.

There is any possibility to create a macro that wil copy and paste special,
everything as values in order for the rest of the people to be able to see
the figures and the diagams without any problem?

I want to go sheet by sheet and make a copy and paste special everything as
values. Have in mind that excel also contains charts also.

Thanks in advance
Manos

Hi Manos and Ankur.

Please becareful as the sheets collection can contain charts as well as
worksheets

So to be sure one should do the follwoing this also removes the selection
from thesheets:

Sub test()

Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets
sheet.Activate
sheet.Cells.Copy
sheet.Range("a1").PasteSpecial xlValues
sheet.Range("a1").Select
Next sheet
Application.CutCopyMode = False
End Sub

---
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"ankur" wrote:

Hi Manos,

The code below will loop through all the sheets in a book and paste
special as values all the cells....




Sub test()

Dim sheet As Object

For Each sheet In ActiveWorkbook.Sheets

sheet.Cells.Copy
sheet.Range("a1").PasteSpecial (xlValues)

Next sheet
Application.CutCopyMode = False


End Sub

Regards
Ankur
www.xlmacros.com

On Dec 19, 2:45 pm, Manos wrote:
Dear all

I have create a big excel file (2003) with 30 different sheets and that
contains a huge amoun of omulas and links with an ERP system.
This means that there are links and formulas that whe someone else can ty to
open the file he can see either errors or values, becuase he do not have
access to ERP folder.

There is any possibility to create a macro that wil copy and paste special,
everything as values in order for the rest of the people to be able to see
the figures and the diagams without any problem?

I want to go sheet by sheet and make a copy and paste special everything as
values. Have in mind that excel also contains charts also.

Thanks in advance
Manos

Be careful using a keyword (sheet) as a variable name. Use something like
this instead:

Dim wksht As Worksheet

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Martin Fishlock" wrote in message
...
Hi Manos and Ankur.

Please becareful as the sheets collection can contain charts as well as
worksheets

So to be sure one should do the follwoing this also removes the selection
from thesheets:

Sub test()

Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets
sheet.Activate
sheet.Cells.Copy
sheet.Range("a1").PasteSpecial xlValues
sheet.Range("a1").Select
Next sheet
Application.CutCopyMode = False
End Sub

---
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"ankur" wrote:

Hi Manos,

The code below will loop through all the sheets in a book and paste
special as values all the cells....




Sub test()

Dim sheet As Object

For Each sheet In ActiveWorkbook.Sheets

sheet.Cells.Copy
sheet.Range("a1").PasteSpecial (xlValues)

Next sheet
Application.CutCopyMode = False


End Sub

Regards
Ankur
www.xlmacros.com

On Dec 19, 2:45 pm, Manos wrote:
Dear all

I have create a big excel file (2003) with 30 different sheets and that
contains a huge amoun of omulas and links with an ERP system.
This means that there are links and formulas that whe someone else can
ty to
open the file he can see either errors or values, becuase he do not
have
access to ERP folder.

There is any possibility to create a macro that wil copy and paste
special,
everything as values in order for the rest of the people to be able to
see
the figures and the diagams without any problem?

I want to go sheet by sheet and make a copy and paste special
everything as
values. Have in mind that excel also contains charts also.

Thanks in advance
Manos