Hello - I have a VERY simple macro that is supposed to unprotect a worksheet,
select all the visible worksheets and then copy/value paste all the cells in
each visible worksheet. Then it should re-protect one of the worksheets.
I've tried stepping through the macro and it does everything fine until the
first worksheet is copy/value pasted and then it clocks.

Here is the code:
Sub ValueCopy()
'
' ValueCopy Macro
' Macro recorded 10/22/2007 by Jim

Sheets("Welcome").Unprotect Password:="3033563"
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next
Sheets("Welcome").Protect Password:="3033563"
End Sub


Any help would be appreciated.

The IF statement needs to span more than just the Select statement.

That is only do the copy/paste if visible.
--
Gary''s Student - gsnu200751


"Jim" wrote:

> Hello - I have a VERY simple macro that is supposed to unprotect a worksheet,
> select all the visible worksheets and then copy/value paste all the cells in
> each visible worksheet. Then it should re-protect one of the worksheets.
> I've tried stepping through the macro and it does everything fine until the
> first worksheet is copy/value pasted and then it clocks.
>
> Here is the code:
> Sub ValueCopy()
> '
> ' ValueCopy Macro
> ' Macro recorded 10/22/2007 by Jim
>
> Sheets("Welcome").Unprotect Password:="3033563"
> Dim ws As Worksheet
> For Each ws In Sheets
> If ws.Visible Then ws.Select (False)
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
> Application.CutCopyMode = False
> Next
> Sheets("Welcome").Protect Password:="3033563"
> End Sub
>
>
> Any help would be appreciated.

Gary's Student ~

Thanks. I'd thought that's what the "next" would do. Can you give me an
example?




"Gary''s Student" wrote:

> The IF statement needs to span more than just the Select statement.
>
> That is only do the copy/paste if visible.
> --
> Gary''s Student - gsnu200751
>
>
> "Jim" wrote:
>
> > Hello - I have a VERY simple macro that is supposed to unprotect a worksheet,
> > select all the visible worksheets and then copy/value paste all the cells in
> > each visible worksheet. Then it should re-protect one of the worksheets.
> > I've tried stepping through the macro and it does everything fine until the
> > first worksheet is copy/value pasted and then it clocks.
> >
> > Here is the code:
> > Sub ValueCopy()
> > '
> > ' ValueCopy Macro
> > ' Macro recorded 10/22/2007 by Jim
> >
> > Sheets("Welcome").Unprotect Password:="3033563"
> > Dim ws As Worksheet
> > For Each ws In Sheets
> > If ws.Visible Then ws.Select (False)
> > Cells.Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues
> > Application.CutCopyMode = False
> > Next
> > Sheets("Welcome").Protect Password:="3033563"
> > End Sub
> >
> >
> > Any help would be appreciated.

The problem is that you select the sheet only if it is visible.

For Each ws In Sheets
If ws.Visible Then ws.Select (False)

The sheet referenced by WS becomes the Active Sheet only if it is visible.
Otherwise, the WS sheet is not activated. Then your code runs

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

Here, you're working on the Active Sheet, which is NOT necessarily the sheet
referenced by the WS variable. It will be the WS sheet only if WS was
visible -- otherwise, it will be the previous worksheet (the most recent
visible sheet). Step through the code line by line with a combination of
hidden and visible sheets and you'll see the error in the logic.

What you really want, I think, is

For Each WS In Sheets
If WS.Visible Then
WS.Select (False)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Jim" <(E-Mail Removed)> wrote in message
newsC906851-BC8C-401E-803B-(E-Mail Removed)...
> Gary's Student ~
>
> Thanks. I'd thought that's what the "next" would do. Can you give me an
> example?
>
>
>
>
> "Gary''s Student" wrote:
>
>> The IF statement needs to span more than just the Select statement.
>>
>> That is only do the copy/paste if visible.
>> --
>> Gary''s Student - gsnu200751
>>
>>
>> "Jim" wrote:
>>
>> > Hello - I have a VERY simple macro that is supposed to unprotect a
>> > worksheet,
>> > select all the visible worksheets and then copy/value paste all the
>> > cells in
>> > each visible worksheet. Then it should re-protect one of the
>> > worksheets.
>> > I've tried stepping through the macro and it does everything fine until
>> > the
>> > first worksheet is copy/value pasted and then it clocks.
>> >
>> > Here is the code:
>> > Sub ValueCopy()
>> > '
>> > ' ValueCopy Macro
>> > ' Macro recorded 10/22/2007 by Jim
>> >
>> > Sheets("Welcome").Unprotect Password:="3033563"
>> > Dim ws As Worksheet
>> > For Each ws In Sheets
>> > If ws.Visible Then ws.Select (False)
>> > Cells.Select
>> > Selection.Copy
>> > Selection.PasteSpecial Paste:=xlValues
>> > Application.CutCopyMode = False
>> > Next
>> > Sheets("Welcome").Protect Password:="3033563"
>> > End Sub
>> >
>> >
>> > Any help would be appreciated.

Thanks, all! Another issue is that one of the other sheets was password
protected and it couldn't continue.

Thanks so much! Works great now!


"Chip Pearson" wrote:

> The problem is that you select the sheet only if it is visible.
>
> For Each ws In Sheets
> If ws.Visible Then ws.Select (False)
>
> The sheet referenced by WS becomes the Active Sheet only if it is visible.
> Otherwise, the WS sheet is not activated. Then your code runs
>
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
>
> Here, you're working on the Active Sheet, which is NOT necessarily the sheet
> referenced by the WS variable. It will be the WS sheet only if WS was
> visible -- otherwise, it will be the previous worksheet (the most recent
> visible sheet). Step through the code line by line with a combination of
> hidden and visible sheets and you'll see the error in the logic.
>
> What you really want, I think, is
>
> For Each WS In Sheets
> If WS.Visible Then
> WS.Select (False)
> Cells.Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
> Application.CutCopyMode = False
> End If
> Next WS
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
>
>
>
> "Jim" <(E-Mail Removed)> wrote in message
> newsC906851-BC8C-401E-803B-(E-Mail Removed)...
> > Gary's Student ~
> >
> > Thanks. I'd thought that's what the "next" would do. Can you give me an
> > example?
> >
> >
> >
> >
> > "Gary''s Student" wrote:
> >
> >> The IF statement needs to span more than just the Select statement.
> >>
> >> That is only do the copy/paste if visible.
> >> --
> >> Gary''s Student - gsnu200751
> >>
> >>
> >> "Jim" wrote:
> >>
> >> > Hello - I have a VERY simple macro that is supposed to unprotect a
> >> > worksheet,
> >> > select all the visible worksheets and then copy/value paste all the
> >> > cells in
> >> > each visible worksheet. Then it should re-protect one of the
> >> > worksheets.
> >> > I've tried stepping through the macro and it does everything fine until
> >> > the
> >> > first worksheet is copy/value pasted and then it clocks.
> >> >
> >> > Here is the code:
> >> > Sub ValueCopy()
> >> > '
> >> > ' ValueCopy Macro
> >> > ' Macro recorded 10/22/2007 by Jim
> >> >
> >> > Sheets("Welcome").Unprotect Password:="3033563"
> >> > Dim ws As Worksheet
> >> > For Each ws In Sheets
> >> > If ws.Visible Then ws.Select (False)
> >> > Cells.Select
> >> > Selection.Copy
> >> > Selection.PasteSpecial Paste:=xlValues
> >> > Application.CutCopyMode = False
> >> > Next
> >> > Sheets("Welcome").Protect Password:="3033563"
> >> > End Sub
> >> >
> >> >
> >> > Any help would be appreciated.
>