[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Re: Copy - Paste Special (Values

an01digital

12/19/2006 10:06:00 AM

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 <M...@discussions.microsoft.com> 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
2 Answers

Martin Fishlock

12/19/2006 11:53:00 AM

0

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 <M...@discussions.microsoft.com> 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
>
>

Jon Peltier

12/19/2006 1:16:00 PM

0

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://Pelti...
_______


"Martin Fishlock" <martin_fishlock@yahoo.co.uk.cutthis> wrote in message
news:558E49E3-C022-4D35-942E-1950A942DEB9@microsoft.com...
> 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 <M...@discussions.microsoft.com> 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
>>
>>