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" wrote in message
...
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" wrote in message
...
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.