various macro problems
 

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

THIS IS THE MACRO USED TO CLEAR THE YTD CONTENTS WITHIN THE BAS
PROGRAM
PROBLEM – WILL NOT WORK WITH SHEET PROTECTE

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

'
Range( _

"C10:IV111,C118:IV219,C226:IV327,C334:IV435,C442:I V543,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 SAV
AND CLOSE THE PROGRAM IN ITS CURRENT STAT

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").S elect
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 WHE
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 WORK
PERFECTLY BUT CLOSES THE ORIGINAL BASE PROGRAM AND KEEPS THE SAVED FIL
OPEN.
PROBLEM – I STILL NEED TO KEEP SOME INFORMATION UPDATED WITHIN THE BAS
PROGRAM, THEREFORE I NEED TO KEEP IT OPEN AND CLOSE AND SAVE THE PROGRA
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 Su

--
legep
-----------------------------------------------------------------------
legepe's Profile: http://www.excelforum.com/member.php...fo&userid=1548
View this thread: http://www.excelforum.com/showthread.php?threadid=27183

various macro problems
 

QUESTION 1. You must either unprotect those cells or unprotect the
sheetclearreprottect. And, you do NOT need to select to do the action or
even be on that sheet.

Range("A4,B6").ClearContents
or
sheets("yoursheet").Range("A4,B6").ClearContents
=======
question 2
Range("V10:Y109").ClearContents
Sheets("Hopper").Range("H9:H108,F9:F108").ClearCon tents
etc, etc.
In other words you need to write your macros so they will work from anywhere
and unlock the protected cells, either by format or by macro.



--
Don Guillett
SalesAid Software

"legepe" wrote in message
...

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 all.
thankyou

THIS IS THE MACRO USED TO CLEAR THE YTD CONTENTS WITHIN THE BASE
PROGRAM
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:I V543,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").S elect
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


--
legepe
------------------------------------------------------------------------
legepe's Profile:
http://www.excelforum.com/member.php...o&userid=15485
View this thread: http://www.excelforum.com/showthread...hreadid=271832