Now I've got a little more experience I've been revisiting some of th
code I've previously written to try and remove as many of the 'Select
commands as possible. However, can somebody advise why the botto
select statement here does not work? Do I really need to select th
worksheet first before selecting the cell?
Oh, 'StreetBo' is a declared worksheet.

Thanks,

Adrian

With StreetBO
.Range("AP1").Value = "Backouts"
.Range("b1").Copy
.Range("AP1").pastespecial Paste:=xlFormats, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
.Columns.AutoFit
.Range("a2").Select
End Wit

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Yes, a worksheet needs to be active to select a cell,
otherwise why select the cell?
In code one almost never needs to select a cell as these
are objects that can quite easily be handled in code. Why
would you need to select that cell...the procedure
doesn't use it


With StreetBO
.Range("AP1").Value = "Backouts"
.Range("AP1").Value = .Range("b1").Value
.Columns.AutoFit
End With


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----

Now I've got a little more experience I've been
revisiting some of the
code I've previously written to try and remove as many
of the 'Select'
commands as possible. However, can somebody advise why
the bottom
select statement here does not work? Do I really need to
select the
worksheet first before selecting the cell?
Oh, 'StreetBo' is a declared worksheet.

Thanks,

Adrian

With StreetBO
.Range("AP1").Value = "Backouts"
.Range("b1").Copy
.Range("AP1").pastespecial Paste:=xlFormats,
Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
.Columns.AutoFit
.Range("a2").Select
End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Kobayashi wrote:
Do I really need to select the
worksheet first before selecting the cell?


If the worksheet is not active then you will need to Activate it before
selecting a cell on it. Alternatively you could

Application.Goto .Range("A2")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

No, Try this.

Sub dosheetbo()
'With Sheets("thesheet")
With Sheets(sheetbo)
.Range("a10") = "Blackouts"
.Range("a11") = .Range("b1")
.Columns.AutoFit
End With
End Sub


--
Don Guillett
SalesAid Software

"Kobayashi" wrote in message
...

Now I've got a little more experience I've been revisiting some of the
code I've previously written to try and remove as many of the 'Select'
commands as possible. However, can somebody advise why the bottom
select statement here does not work? Do I really need to select the
worksheet first before selecting the cell?
Oh, 'StreetBo' is a declared worksheet.

Thanks,

Adrian

With StreetBO
Range("AP1").Value = "Backouts"
Range("b1").Copy
Range("AP1").pastespecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns.AutoFit
Range("a2").Select
End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

You have missed the "." character from your code:

With StreetBO
.Range("AP1").Value = "Backouts"
.Range("b1").Copy
.Range("AP1").pastespecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
.Columns.AutoFit
.Range("a2").Select
End With


Kobayashi wrote in message ...
Now I've got a little more experience I've been revisiting some of the
code I've previously written to try and remove as many of the 'Select'
commands as possible. However, can somebody advise why the bottom
select statement here does not work? Do I really need to select the
worksheet first before selecting the cell?
Oh, 'StreetBo' is a declared worksheet.

Thanks,

Adrian

With StreetBO
Range("AP1").Value = "Backouts"
Range("b1").Copy
Range("AP1").pastespecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns.AutoFit
Range("a2").Select
End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Many thanks to all for trying to help.
This issue is, presumably, why I have difficulty using the .usedrange
function within With statements that use a variable, albeit a worksheet
variable. I always have to end up selecting the worksheet then starting
the with statement as: 'With Activesheet' to get the .usedrange to
work. Both this .used range issue and my below problem must be related,
no?

Patrick,
I want it to be active so that when the workbook is opened by somebody
else the beginning of the worksheet is on view.

Bill,
Afraid I can't get the application.goto... to work either inside or
outside the With statement?

Don,
Thanks, the .range... = .range... is a new idea to me, which I shall
remember and use but how does this resolve me Select issue?

Ginger,

Where have I missed the "."? I checked several times and don't see
where?

Again, many thanks for all your help!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Don,
Thanks, the .range... = .range... is a new idea to me, which I shall
remember and use but how does this resolve me Select issue?
The point is that you need NOT select to get the job done.

--
Don Guillett
SalesAid Software

"Kobayashi" wrote in message
...

Many thanks to all for trying to help.
This issue is, presumably, why I have difficulty using the .usedrange
function within With statements that use a variable, albeit a worksheet
variable. I always have to end up selecting the worksheet then starting
the with statement as: 'With Activesheet' to get the .usedrange to
work. Both this .used range issue and my below problem must be related,
no?

Patrick,
I want it to be active so that when the workbook is opened by somebody
else the beginning of the worksheet is on view.

Bill,
Afraid I can't get the application.goto... to work either inside or
outside the With statement?

Don,
Thanks, the .range... = .range... is a new idea to me, which I shall
remember and use but how does this resolve me Select issue?

Ginger,

Where have I missed the "."? I checked several times and don't see
where?

Again, many thanks for all your help!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/