Copy, Paste Special Values all sheets in workbook
 

I'd like to write a macro that goes to each sheet in turn, selects all
cells and paste special values. I've got his far but it only seems to
work for the active sheet:

Sub removelinks()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook


For Each wks In wb.Worksheets

Cells.Select
Cells.Copy
Cells.PasteSpecial Paste:=xlValues

Next

End Sub

Any help appreciated....thanks

Copy, Paste Special Values all sheets in workbook
 

of course, so you need to activate a sheet you want to deal with

je napisao u poruci interesnoj
roups.com...
I'd like to write a macro that goes to each sheet in turn, selects all
cells and paste special values. I've got his far but it only seems to
work for the active sheet:

Sub removelinks()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook


For Each wks In wb.Worksheets


'---------- add next line after for each
wks.activate

Copy, Paste Special Values all sheets in workbook
 

Thanks very much, that worked a treat!

Copy, Paste Special Values all sheets in workbook
 

You don't need to select. Use this instead.

Sub removelinks()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook

For Each wks In wb.Worksheets

wks.Cells.Copy
wks.Cells.PasteSpecial Paste:=xlValues

Next

End Sub


"sali" wrote:

of course, so you need to activate a sheet you want to deal with

je napisao u poruci interesnoj
roups.com...
I'd like to write a macro that goes to each sheet in turn, selects all
cells and paste special values. I've got his far but it only seems to
work for the active sheet:

Sub removelinks()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook


For Each wks In wb.Worksheets


'---------- add next line after for each
wks.activate

Copy, Paste Special Values all sheets in workbook
 

See also
http://www.rondebruin.nl/values.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
I'd like to write a macro that goes to each sheet in turn, selects all
cells and paste special values. I've got his far but it only seems to
work for the active sheet:

Sub removelinks()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook


For Each wks In wb.Worksheets

Cells.Select
Cells.Copy
Cells.PasteSpecial Paste:=xlValues

Next

End Sub

Any help appreciated....thanks