Macro Help...I'm sooooooo confused!

[Steve Klenner]

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"

*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************

I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steve

You can't protect or unprotect in a group.
you have to loop through the sheets to do that

Do you know the names of the worksheets????


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"

*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************

I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Ron, look a bit closer - he is protecting and unprotecting in a loop, for
each sheet.

Could you test it in xl2003.

--
Regards,
Tom Ogilvy

Hi Steve

You can't protect or unprotect in a group.
you have to loop through the sheets to do that

Do you know the names of the worksheets????


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"

*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************

I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Sorry I read only the top of the posting
and nothing below his name

I go read it now<g>

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Ron, look a bit closer - he is protecting and unprotecting in a loop, for
each sheet.

Could you test it in xl2003.

--
Regards,
Tom Ogilvy

Hi Steve

You can't protect or unprotect in a group.
you have to loop through the sheets to do that

Do you know the names of the worksheets????


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"
*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************
I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steve

Try this example

I remove some lines and add the sht before the range so Excel know that it use the
cell in that sheet and not in the active one.

Sub test()
Application.ScreenUpdating = False
Dim sht As Worksheet
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
sht.Range("G57").FormulaR1C1 = "=R[-1]C*6%"
sht.Range("F57").FormulaR1C1 = "MI Sales Tax"
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Sorry I read only the top of the posting
and nothing below his name

I go read it now<g>

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Ron, look a bit closer - he is protecting and unprotecting in a loop, for
each sheet.

Could you test it in xl2003.

--
Regards,
Tom Ogilvy

Hi Steve

You can't protect or unprotect in a group.
you have to loop through the sheets to do that

Do you know the names of the worksheets????


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"
*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************
I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Steve Klenner]

Ron / Tom -

The last macro you provided did the trick....It works!.

I can't thank both you guys enough for the solution. Whats amazing is the
macro is simpler (by far) than my original one.

While it not important that you respond...any ideas why my original macro
won't work after updaing in Excel 2003?
Does 2003 handle macros differently than previous versions?

Thanks Again!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"

*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************

I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steve

If you want to select a range like you did in your last macro you must first select the sheet
in the loop(sht.select)
Range("G57").Select ' This will select the correct cell then on the sheet in the loop

Your macro will select always the same cells on the same sheet (the sheet that is active when you run the macro)
If you run the macro with a protected sheet active(other sheet then a sheets in the array) the code will blow because
he can't copy the formula in a cell of a protected sheet.


My macro don't select but tell the code in which sheet and cell he must copy the formulas
by using sht before the range

For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
sht.Range("G57").FormulaR1C1 = "=R[-1]C*6%"
sht.Range("F57").FormulaR1C1 = "MI Sales Tax"
sht.Protect Password:="lock"
Next sht

sht is the sheet in the loop so you are sure this way that your formulas will be copy on that sheet

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Ron / Tom -

The last macro you provided did the trick....It works!.

I can't thank both you guys enough for the solution. Whats amazing is the
macro is simpler (by far) than my original one.

While it not important that you respond...any ideas why my original macro
won't work after updaing in Excel 2003?
Does 2003 handle macros differently than previous versions?

Thanks Again!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I'm trying to recreate a macro in Excel2003 that is giving me some
problems...
Here's What I'm trying to do...
1 - Select a group of sheets in a workbook and Unprotect them.
2 - Insert formula in cells G57 and F57 in each of selected sheets
3 - ReProtect selected sheets with password.
4 - Return to "Quote Totals" worksheet

I've included two macros...
1 - This Macro works in an old workbook that still works in Excel2003..
But does NOT work when I've save it in 2003.
2 - My latest attempt at trying to get this macro to work.

I'd really appreciate someones "genius" to get this resolved!
Thanks
Steve


This Macro works in Excel2003 Workbook that was created in Excel 2000
It does NOT work in Excel2003 when I saved it as an Excel 2003 Workbook
It fails at Line.....sht.Unprotect Password:="xxxxx"

*************************************************************
Sub MISalesTAX_Totals()
Application.ScreenUpdating = False
'UnProtect Sheets for Updating
For Each sht In Sheets
**Fails Here**sht.Unprotect Password:="xxxxx"
Next sht
Sheets(Array("Circuit Cards", "Enpoints")).Select
'Select G57 and F57 in each Sheet and input formulas
Range("G57").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
'Reselect same sheets and Protect Them
For Each sht In Sheets
ActiveSheet.Select
sht.Protect Password:="xxxxx"
Next sht
'Return to Quote Totals Sheet
Sheets("Quote Totals").Select
Application.ScreenUpdating = True
End Sub
*******************************************************************
*******************************************************************

I've tried to "fix" the macro...this is my latest attempt.
It Fails At Line ...ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
************************************************************************
' MISalesTAX_Axx Macro
Application.ScreenUpdating = False
Dim sht as worksheet
'UnProtect specific sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Unprotect Password:="lock"
'Insert Formula in G57 and F57
Range("G57").Select
** Fails Here**ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F57").Select
ActiveCell.FormulaR1C1 = "MI Sales Tax"
Next sht
'Re-Protect Sheets
For Each sht In Sheets(Array("Circuit Cards", "Endpoints"))
sht.Protect Password:="lock"
Next sht
Sheets("Profile").Select
Application.ScreenUpdating = True
End Sub