application.workbooks.worksheets.range.offset.select problem

[RET79]

application.workbooks.worksheets.range.offset.select

This particular line of code is annoying me lately, the VBE doesn't always like it. I tend to make statements like this at the beginning of my programs

Set result = application.thisworkbook.worksheets("results")

Set Source = application.workbooks("SourceFile.xls").worksheets("data")

But, when later in the program I do this sort of thing:

result.range("A1:A4").Offset(1,0). Select

Source.Select


Then it doesn't work! Why is this the case? Sometimes they only work if I am on that active sheet/book only. This is very annoying, any help appreciated.

RET79

[NateO]

Error 1004 eh? Might want to try selecting the sheet first, like:

Sub no1004()
result = Worksheets("sheet2").Name
Sheets(result).Select
[a1:a4].Offset(1, 0).Select
End Sub

Hope this helps.

[RET79]

To start answering my own question.... I tried this just now

Dim dest As Object
Dim source As Object
Set dest = Application.ThisWorkbook.Worksheets("Sheet2")
Set source = Application.Workbooks("test.xls").Worksheets("Sheet1")

and then did stuff like this

source.activate
Range("A20:C20 = a

and this works fine.

However, is using this "activate" thing the best way to code? Every time I want to select a sheet to do something to it, I have to do result.activate first, which is a bit of a pain. Why should I have to do this when I have already defined the workbook and sheet at the beginning? Why can't I just write

result.range("Q34") = 4

when i do that I get error messages if result is not the current active sheet.

Thanks.

RET79

[Adcb]

You can't select a range without activating/selecting the sheet first.

But ... why do any selecting?
You can do your thing to the range without having to select anything.

[Ivan F Moala]

On 2002-03-27 11:37, RET79 wrote:
application.workbooks.worksheets.range.offset.select

This particular line of code is annoying me lately, the VBE doesn't always like it. I tend to make statements like this at the beginning of my programs

Set result = application.thisworkbook.worksheets("results")

Set Source = application.workbooks("SourceFile.xls").worksheets("data")

But, when later in the program I do this sort of thing:

result.range("A1:A4").Offset(1,0). Select

Source.Select


Then it doesn't work! Why is this the case? Sometimes they only work if I am on that active sheet/book only. This is very annoying, any help appreciated.

RET79

Using Select with your Object coding will
only work if the workbook is the active workbook.
Try using Activate instead ie. replace all
your Select with Activate. Using the keyword
Activate for a Worksheet Object is the
equivalent to clicking the sheet’s tab.


eg using your code, which by the way is a good way to reference the Workbooks.....

application.workbooks.worksheets.range.offset.Activate

Set result = application.thisworkbook.worksheets("results")

Set Source = application.workbooks("SourceFile.xls").worksheets("data")

But, when later in the program I do this sort of thing:

result.range("A1:A4").Offset(1,0).Activate

Source.Activate


Ivan

[Colo]

Hello. Just my opinion...
I do not like use "Activate method" or "Select method" except when absolutely required.

One of the reasons is that execution speed is slow.
Another reason , "Select or Activate" is not the purpose.
The purpose is write a value to a cell.

if Workbook("test.xls") is open, I think I do not have to "Select" or "Activate".
An error does not come out in my environment.
Regards,

Sub sample()
Dim dest As Object
Dim source As Object, a As String
Set dest = Application.ThisWorkbook.Worksheets("Sheet2")
Set source = Application.Workbooks("test.xls").Worksheets("Sheet1")
a = "sample"
'The following code works even if "test.xls" is not activeworkbook
source.Range("A20:C20").Value = a
End Sub

[Ivan F Moala]

On 2002-03-27 20:41, Colo wrote:
Hello. Just my opinion...
I do not like use "Activate method" or "Select method" except when absolutely required.

One of the reasons is that execution speed is slow.
Another reason , "Select or Activate" is not the purpose.
The purpose is write a value to a cell.

if Workbook("test.xls") is open, I think I do not have to "Select" or "Activate".
An error does not come out in my environment.
Regards,

Sub sample()
Dim dest As Object
Dim source As Object, a As String
Set dest = Application.ThisWorkbook.Worksheets("Sheet2")
Set source = Application.Workbooks("test.xls").Worksheets("Sheet1")
a = "sample"
'The following code works even if "test.xls" is not activeworkbook
source.Range("A20:C20").Value = a
End Sub

Colo I agree 100%....the example was in answer to the original Question and not about the best method to use as you suggest.

Ivan

[Dave Hawley]

Agree completetly with Colo and Ivan, get away from selecting, select, activate and active. BUT if you really must just use:

Application.Goto result.range("A1:A4").Offset(1,0)

[RET79]

Thanks guys, I agree 100% about not liking to use activate or select, this is one reason I posted this message in the first place.

Could there be something wrong with my Excel 'environment' that I am having difficulties with such an easy task? It is very annoying to waste so much time trying to figure out why application.workbooks("file.xls").worksheets("Sheet1").Range etc. does not work even though file is open and so on. I am convinced that perhaps some setting needs changing on excel, or perhaps someone at work has some built in macro on startup that means that it won't accept this sort of method.

If anyone has any hints as to how I woudl check this out then please let me know.