Macro Help...I'm sooooooo confused!

S

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
*******************************************************************
*******************************************************************
Click to expand...
Click to expand...
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
 
R

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)




Steve Klenner said:
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
*******************************************************************
*******************************************************************
Click to expand...
Click to expand...
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
Click to expand...
 
T

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

Ron de Bruin said:
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
*******************************************************************
*******************************************************************
Click to expand...
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
Click to expand...
Click to expand...
 
R

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)




Tom Ogilvy said:
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

Ron de Bruin said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

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)




Ron de Bruin said:
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)




Tom Ogilvy said:
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

Ron de Bruin said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Steve Klenner said:
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
*******************************************************************
*******************************************************************
Click to expand...
Click to expand...
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
Click to expand...
 
R

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)




Steve Klenner said:
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Steve Klenner said:
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
*******************************************************************
*******************************************************************
Click to expand...
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Macro 1
Workbook open code to do 2 things 1
Macro not protecting every sheet 7
Automatically update SheetName in workbook sub if SheetName changes 3
Copy Tab Names from Several Sheets and Copy Last Row from Same She 7
Run macro on all files in a specific folder 3
Macro to generate FileName of the source file 1
Problem in adding macro to ribbon through Add-in 7

Top