PDA

View Full Version : [SOLVED] This works but I know it can be written better



K. Georgiadis
03-19-2005, 07:54 PM
This procedure is my (amateurish) edited version of a much longer recorded macro.
The Sheet array was the result of simply Grouping all of the named sheets. This is a simple macro that works fine the way it is but I suspect that you can show me how to condense it further.


Sub with_Meddux()
Application.ScreenUpdating = False
Sheets(Array("Delta-Jan", "Delta-Feb", "Delta-Mar", "Delta-Apr", _
"Delta-May", "Delta-Jun", "Delta- Jul", _
"Delta-Aug", "Delta-Sep", "Delta-Oct", "Delta-Nov", _
"Delta-Dec", "Delta-Monthly", "Delta-Quarterly", "Delta-Summary")).Select
Sheets("Delta-Jan").Activate
Range("A106").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("Direct Expenses by Brand").Select
Range("A17").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("Direct Expenses by Product").Select
Range("A18").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("Navigation Menu").Select
Range("F19").Select
End Sub

Jacob Hilderbrand
03-19-2005, 08:06 PM
You do not need to actually select anything, unless you actually want it to be selected. Try this macro. Also note that "1" is text, if it is supposed to be a number do not use quotes.



Option Explicit

Sub with_Meddux()
Application.ScreenUpdating = False
Sheets(Array("Delta-Jan", "Delta-Feb", "Delta-Mar", "Delta-Apr", _
"Delta-May", "Delta-Jun", "Delta- Jul", _
"Delta-Aug", "Delta-Sep", "Delta-Oct", "Delta-Nov", _
"Delta-Dec", "Delta-Monthly", "Delta-Quarterly", "Delta-Summary")).Select
Range("A106").Value = "1"
Sheets("Direct Expenses by Brand").Range("A17").Value = "1"
Sheets("Direct Expenses by Product").Range("A18").Value = "1"
Sheets("Navigation Menu").Select
Range("F19").Select
Application.ScreenUpdating = True
End Sub

K. Georgiadis
03-19-2005, 08:12 PM
Thanks. It looks a lot more compact than my version! What is the result of changing Application.ScreenUpdating to True at the end of the macro?

Jacob Hilderbrand
03-19-2005, 08:25 PM
You should get into the habbit of setting things back to TRUE if you set them to FALSE in your code. In this case at the end of the macro it would be TRUE anyways because ScreenUpdating resets automatically, but other options do not.

K. Georgiadis
03-19-2005, 08:32 PM
Great! Thanks...:beerchug:

Jacob Hilderbrand
03-19-2005, 10:37 PM
You're Welcome :beerchug:

Take Care