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

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

<(E-Mail Removed)> je napisao u poruci interesnoj
grupi:(E-Mail Removed)...
> 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

Thanks very much, that worked a treat!

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
>
> <(E-Mail Removed)> je napisao u poruci interesnoj
> grupi:(E-Mail Removed)...
> > 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
>
>
>

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

--

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


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>