L
legepe
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: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 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").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 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
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: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 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").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 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