newsgroup advice "selecting"
 

1st post, btw.........
i've read a lot of advice from you wonderful MVP people advising
against having a macro "select" cells all the time, for the sake of
saving time. i don't understand exactly how you write the code without
selecting things............. could you pls. amend this code below so
that i can see how you write it without selecting things?
(pls. excuse it the code - i'm sure it could be written much more
neatly but i'm still learning! like i realize all the "extra" parts of
the paste special can be deleted - just haven't got there yet!)
thanks very much! :D
susan
----------------
Sub UpdateCount()
'
' UpdateCount Macro
' Macro recorded 1/23/2006 by Susan
'
' revised 9/19/2006 to hide "X" worksheet
'
Application.ScreenUpdating = False

Sheets("X").Visible = True
Sheets("X").Select
Columns("A:A").Select

Selection.ClearContents

Range("A1").Select
Sheets("FOR SBH ONLY").Select
Columns("B:B").Select
Selection.Copy
Sheets("X").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="furnace & plumbing:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="wells & excavation:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("A:A").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D3").Select

ActiveWindow.SelectedSheets.Visible = False
Sheets("FOR SBH ONLY").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub

newsgroup advice "selecting"
 

Sub UpdateCount()
'
' UpdateCount Macro
' Macro recorded 1/23/2006 by Susan
'
' revised 9/19/2006 to hide "X" worksheet
'
Application.ScreenUpdating = False

Sheets("X").Visible = True
Sheets("X").Select
Columns("A:A").ClearContents

Sheets("FOR SBH ONLY").Columns("B:B").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
..
..
..
and so on

hth

Greetings Carlo

"Susan" wrote:

1st post, btw.........
i've read a lot of advice from you wonderful MVP people advising
against having a macro "select" cells all the time, for the sake of
saving time. i don't understand exactly how you write the code without
selecting things............. could you pls. amend this code below so
that i can see how you write it without selecting things?
(pls. excuse it the code - i'm sure it could be written much more
neatly but i'm still learning! like i realize all the "extra" parts of
the paste special can be deleted - just haven't got there yet!)
thanks very much! :D
susan
----------------
Sub UpdateCount()
'
' UpdateCount Macro
' Macro recorded 1/23/2006 by Susan
'
' revised 9/19/2006 to hide "X" worksheet
'
Application.ScreenUpdating = False

Sheets("X").Visible = True
Sheets("X").Select
Columns("A:A").Select

Selection.ClearContents

Range("A1").Select
Sheets("FOR SBH ONLY").Select
Columns("B:B").Select
Selection.Copy
Sheets("X").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="furnace & plumbing:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="wells & excavation:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("A:A").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D3").Select

ActiveWindow.SelectedSheets.Visible = False
Sheets("FOR SBH ONLY").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub

newsgroup advice "selecting"
 

On 20 Sep 2006 04:55:59 -0700, "Susan" wrote:

1st post, btw.........
i've read a lot of advice from you wonderful MVP people advising
against having a macro "select" cells all the time, for the sake of
saving time. i don't understand exactly how you write the code without
selecting things............. could you pls. amend this code below so
that i can see how you write it without selecting things?
(pls. excuse it the code - i'm sure it could be written much more
neatly but i'm still learning! like i realize all the "extra" parts of
the paste special can be deleted - just haven't got there yet!)
thanks very much! :D
susan

You just reference the cells directly. See below


----------------
Sub UpdateCount()
'
' UpdateCount Macro
' Macro recorded 1/23/2006 by Susan
'
' revised 9/19/2006 to hide "X" worksheet
'
Application.ScreenUpdating = False

Sheets("X").Visible = True
XXX Sheets("X").Select
XXX Columns("A:A").Select

XXX Selection.ClearContents

Sheets("X").Columns("A:A").ClearContents


XXX Range("A1").Select
XXX Sheets("FOR SBH ONLY").Select
XXX Columns("B:B").Select
XXX Selection.Copy

Sheets("FOR SBH ONLY").Columns("B:B").Copy


You should be able to figure the rest out from these examples



Sheets("X").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="furnace & plumbing:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="wells & excavation:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("A:A").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D3").Select

ActiveWindow.SelectedSheets.Visible = False
Sheets("FOR SBH ONLY").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub

--ron

newsgroup advice "selecting"
 

try this. Since it's long and I didn't test so I may have missed something.

with Sheets("X")
.. Columns(1).ClearContents

..columns(1).value= Sheets("FOR SBH ONLY").Columns(2).value
.. Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="furnace & plumbing:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="wells & excavation:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.. Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

. Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

end with


--
Don Guillett
SalesAid Software

"Susan" wrote in message
oups.com...
1st post, btw.........
i've read a lot of advice from you wonderful MVP people advising
against having a macro "select" cells all the time, for the sake of
saving time. i don't understand exactly how you write the code without
selecting things............. could you pls. amend this code below so
that i can see how you write it without selecting things?
(pls. excuse it the code - i'm sure it could be written much more
neatly but i'm still learning! like i realize all the "extra" parts of
the paste special can be deleted - just haven't got there yet!)
thanks very much! :D
susan
----------------
Sub UpdateCount()
'
' UpdateCount Macro
' Macro recorded 1/23/2006 by Susan
'
' revised 9/19/2006 to hide "X" worksheet
'
Application.ScreenUpdating = False

Sheets("X").Visible = True
Sheets("X").Select
Columns("A:A").Select

Selection.ClearContents

Range("A1").Select
Sheets("FOR SBH ONLY").Select
Columns("B:B").Select
Selection.Copy
Sheets("X").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="furnace & plumbing:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="wells & excavation:", Replacement:="",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("A:A").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D3").Select

ActiveWindow.SelectedSheets.Visible = False
Sheets("FOR SBH ONLY").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub

newsgroup advice "selecting"
 

thanks all so much...... now that i can SEE what you mean versus what i
wrote, i understand what you're saying.
thanks!
susan