various macros help!

[legepe]

Hi all,
I'm new here, and i,m a NEWBIE with VBA and i have a big favor to ask.

I am really hoping that someone can help me correct the problems with the following macros that are not working for various reasons
I have listed them below with some explanations, i hope you can understand them.
thankyou
legepe

This is the macro used to clear the YTD contents within the base program
Problem - Problem – will not work with sheet protected

Sub Macro45()
'
' Macro45 Macro
' Macro recorded 23/10/2004 by Leigh Pender
'

'
Range( _
"C10:IV111,C118:IV219,C226:IV327,C334:IV435,C442:IV543,C550:IV651,C658:IV759,C766:IV867,C874:IV975" _
).Select
Range("C874").Activate
Selection.ClearContents
End Sub


This is the macro used to clear data inputted –
Problem – will not work with sheets protected, Plus I need it to save and close the program in its current state

Sub Macro34()
'
' Macro34 Macro
' Macro recorded 22/10/2004 by Leigh Pender
'

'
Range("V10:Y109").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=-6
Sheets("Hopper").Select
Range("H9:H108,F9:F108").Select
Range("F108").Activate
Selection.ClearContents
Sheets("Actual").Select
Range("AP9:AP108,AV9:AV108,BB9:BB108,BH9:BH108").Select
Range("BH108").Activate
ActiveWindow.ScrollColumn = 1
Range("AP9:AP108,AV9:AV108,BB9:BB108,BH9:BH108,H9:H108,T9:T108,AD9:AD108"). _
Select
Range("AD9").Activate
Selection.ClearContents
Sheets("Meter Readings").Select
Range("O8:X107").Select
Selection.Copy
ActiveWindow.ScrollRow = 8
Application.Run "SlotPro.xls!Macro4"
Range("B8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Run "SlotPro.xls!Macro2"
Range("O8:X107").Select
ActiveWindow.LargeScroll Down:=-4
Range("O8:X107,P5,V5,X4:Y5").Select
Range("X4").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("P5").Select
Sheets("Period-Results & Variences").Select
End Sub

This is the macro used to go to sheet named – Clear
I want to keep the sheet tab hidden, and only show the contents when the macro is activated,
Problem – the macro will not work when in hidden mode.

Sub Macro38()
'
' Macro38 Macro
' Macro recorded 23/10/2004 by Leigh Pender
'

'
Sheets("Clear").Select
End Sub


The macro below is for saving the data in a Value/Format. It works perfectly but closes the original base program and keeps the saved file open.
Problem – I still need to keep some information updated within the base program, therefore I need to keep it open and close and save the program in its current state after activating macro – 34 (above)

Sub ConvertAlltoVals()
Dim wsheet As Worksheet
With Application
.ScreenUpdating = False
.DisplayAlerts = False

For Each wsheet In Worksheets
wsheet.Unprotect
With wsheet.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With
wsheet.Protect
Next wsheet

Sheets("Clear").Delete

.Dialogs(xlDialogSaveAs).Show
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

 

[dyarwood]

To alter a protected shet you need to unprotect it.

Sheets("Sheetname").Unprotect

then when you are done with the alterations to the sheet protect it again

Sheets("Sheetname").Protect

The protection might need you to add in a password to the code if the sheet's protection is passworded.

To save an Excel spreadsheet use

ActiveWorkbook.Save (this will save in the current name)


To close an excel spreadsheet use the

ActiveSheet.Close True (if you want it saved)
ActiveSheet.Close False (if you don't want to save changes)


To make a hidden Excel sheetsheet visible use

Sheets("Sheetname").Visible = True

To hide it again there are two options

Sheets("Sheetname").Visible = False
and
Sheets("Seetname").Visible = xlVeryHIdden

the first makes the spreadsheet hidden and this sheet can be made visible by doing Format->Sheets->Unhide

The second makes the spreadsheet very hidden and can only be made visible with code.