ThisWorkbook

[Ron]

Hi guys,

I have a sheet which periodically through the day takes a snapshot of
financial prices.

This works fine as long as this is the active sheet, if it isn't I get a
vba error.

I've been using a laptop as a spare standalone machine for this sheet, as I
need to be using excel throughout the same period.

Can I make the code focus to the actual workbook that contains the code and
do what it needs to do in the background?

I'm thinking along the lines of ThisWorkbook.sheets("import").range("m3")
etc, which I'm hoping will do the job?

Will this do it?

Ron

 

[Bob Phillips]

Surely, if you are competent enough to ask a question that specific and that
accurate (in terms of OM and syntax), you are competent enough to try and
find out yourself?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron]

Surely, if you are competent enough to ask a question that specific
and that accurate (in terms of OM and syntax), you are competent
enough to try and find out yourself?

Bob,

I'm unsure whether to take that as a compliment or a slap on the wrist for
asking a dumb question.

I'm trying to teach myself vba with a couple of books, practical examples
and the help of the good people on here.

I'm far from confident of my abilities as yet, but maybe I should be a bit
more confident.

Thanks for the reply anyway.

Ron

 

[Bob Phillips]

Ron,

A bit of both, but you learn far more by trying it yourself. Go for it, it
won't bite .

Keep us in reserve for when you try something which, to your mind, should
work and it doesn't, or you are truly stumped. Just asking '... will this
work?' is a bit dumb (gently said ;-), as you can easily find out.

Bob

 

[Ron]

Hi Bob,

You just knew I'd be back

I tried many variations of Thisworkbook, and to be honest had a nightmare.

In the end I've settled for the first line in the timed code to be

ThisWorkbook.Activate

At least this gives the relevant workbook focus to run the code ok.

At the moment I'm happy with this until I find alternate usage of
ThisWorkbook

My main aim was to run the code in the background without having to give
the workbook focus, I'm sure this is possible but at the moment it's
stumping me.


Ron

 

[Dick Kusleika]

Ron

What is it you're trying to do? You can certainly do almost anything
without giving the workbook focus. For instance, if you want to copy a
range, you would use

ThisWorkbook.Sheets("import").Range("a1").Copy

Be specific about what you are doing and you'll have an answer in no time.

 

[Tom Ogilvy]

Application.ScreenUpdating = False
set bk = Activeworkbook
ThisWorkbook.Activate
' code you have
bk.Activate
Application.ScreenUpdating = True

Will disguise the fact you have activated the workbook if your code is fast.

to work on a workbook in the background qualify everything from the
Thisworkbook.Object down


thisworkbook.Worksheets("Sheet1").Range("A1").Value = 6
or

with thisworkbook.Worksheets("Sheet1")
.Range("A1").Value = 6
.Range("B9").Value = 11
.Range("A1:C30").Sort Key1:=.Range("C1")
End with

 

[Lonnie M.]

Ron,
You could also try specifying explicitly which workbook:
Workbooks("YourWorkbookName.xls").Sheets("import").Range ("m3")
Regards, Lonnie M.

 

[Ron]

Thanks for the continuing help guys.

I've pasted one of the routines below that I'm trying to do in the
background.

Im getting a 'Select method of Range class failed' error at the first
line of code,

ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select

Here's the code in full...

Sub PasteMarket()
' copies the current market and pastes it lower down
ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select
Selection.Insert Shift:=xlDown
ThisWorkbook.Worksheets("WEB QUERY").Range("A1:d49").Select
Selection.Copy
ThisWorkbook.Worksheets("WEB QUERY").Range("A51").Select
Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:
= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ThisWorkbook.Worksheets("WEB QUERY").Range("l2").Copy
ThisWorkbook.Worksheets("WEB QUERY").paste Destination:
=ThisWorkbook.Worksheets("WEB QUERY").Range("d1")
End Sub

I'm sure also that you guys could trim that code down somewhat, but as I
say I'm trying to teach myself this, so please forgive any amateurish lines
of code.

Thanks guys,

Ron

 

[Dick Kusleika]

Ron

Im getting a 'Select method of Range class failed' error at the
first line of code,

ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select

You can't select a range on a worksheet that's not active. That's what the
error means. You could do:

ThisWorkbook.Activate
ThisWorkbook.Worksheets("Web query").Select
ThisWorkbook.Worksheets("Web query").Range("A51100").Select

which makes sure that the workbook and worksheet you want are active. But
you shouldn't do that. You should strive to not use Select or Activate
unless it's a necessary part of your code and it rarely is.

Here's the code in full...

Sub PasteMarket()

With ThisWorkbook.Worksheets("WEB QUERY")

' copies the current market and pastes it lower down

.Range("A51100").Insert Shift:=xlDown

ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select
Selection.Insert Shift:=xlDown

.Range("A149").Copy
.Range("A51").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'skip the rest of the arguments because they're the default values
anyway
Application.CutCopyMode = False

ThisWorkbook.Worksheets("WEB QUERY").Range("A1:d49").Select
Selection.Copy
ThisWorkbook.Worksheets("WEB QUERY").Range("A51").Select
Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats,
Operation: = _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

.Range("I2").Copy Destination:=.Range("D1")

ThisWorkbook.Worksheets("WEB QUERY").Range("l2").Copy
ThisWorkbook.Worksheets("WEB QUERY").paste Destination:
=ThisWorkbook.Worksheets("WEB QUERY").Range("d1")

End With

End Sub

Everytime you have a Something.Select followed by a Selection.DoSomething,
you can usually make it one line like Something.DoSomething. Here's some
more reading on the subject

http://www.dicks-blog.com/archives/2004/04/27/beginning-vba-select-and-activate/

I'm sure also that you guys could trim that code down somewhat, but
as I say I'm trying to teach myself this, so please forgive any
amateurish lines of code.

In a couple of years you'll look back at the code you wrote with disgust.
At least that's what I do.

 

[Ron]

In a couple of years you'll look back at the code you wrote with
disgust. At least that's what I do.

There's no doubt about that!

Thanks for the help.


Ron