Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy - Paste Special (Values)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy - Paste Special (Values)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy - Paste Special (Values)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Values only without Paste Special | Excel Discussion (Misc queries) | |||
Copy & Paste Special Using Values | Excel Programming | |||
Copy Paste Special Values | Excel Programming | |||
copy paste special values | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |