Very simple Select problem?

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

Kobayashi

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
 
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
 
Kobayashi said:
Do I really need to select the
worksheet first before selecting the cell?
Click to expand...

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
 
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
 
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!
 
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?
Click to expand...
The point is that you need NOT select to get the job done.
 
Kobayashi said:
Bill,
Afraid I can't get the application.goto... to work either inside or
outside the With statement?
Click to expand...

Works for me.
You did remember the "." before Range?
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Back
Top