Please explain why this simplest bit of VBA select doesn't work

[Johnny C]

Here's the code. There's 2 workbooks, the intention is to copy from one to the other. Both workbook names are strings. Both files have worksheets codenamed wksSourceData/wksTargetData (it doesn't make any odds if I change them).

But I'm getting 'Objest doesn't support this property or method' on either of the select statements (oddly sometimes it's the forst sometimes it's the second)

I've been using this code for years, what am I doing wrong? I think I'm going senile

Workbooks(ThisFile).Activate
ActiveWorkbook.wksSourceData.Range("A5:A2000").Select
Selection.Copy
Workbooks(CopyFile).Activate
ActiveWorkbook.wksTargetData.Range("N4").Select

 

[Fluff]

It should be

wksSourceData.Range("A5:A2000").Select

But that will refer to the sheet in the workbook that contains the code.

 

[Johnny C]

Yes, that's the intention. There's a worksheet in the workbook that contains the code codenamed "wksSourceData"

Even this gives an error

' First set this file as the source file
ThisFile = ActiveWorkbook.Name

Workbooks(ThisFile).Activate
ActiveWorkbook.wksSourceData.Select

 

[Fluff]

You still have ActiveWorkbook in there, get rid of it.

 

[Johnny C]

It doesn't work without it either.

The only way I have been able to get round it is add a loop through all worksheets in both, if the sheet.codename = the one I want set a string to the sheet name.

I'm convinced whatever version of Excel 365 I'm on VBA is riddled with bugs. Things just don't work when they should. I grab bits of code from all sorts of places even stuff from Chandroo falls over. I spent 3 days last week trying to get VBA to set a pagefield multiple selection in a pivot table.

 

[Fluff]

As I said wksSourceData.Select will only work for the workbook that contains the code, also that workbook needs to be the active workbook.
If you are trying to access a sheet in anything other than "ThisWorkbook" you cannot directly use the code name, unless you set a reference to that workbook.
This is not a "bug" it is how it has always worked.

Also it is very rare that you need to select or activate anything.

 

[Johnny C]

That was how I started off, using Set .

Even the simplest VBA won't work

Sub TestSelect()
Dim ThisWB As Workbook

Set ThisWB = ActiveWorkbook
[B]ThisWB.wksSourceData.Range("A:A").Select[/B]

crashes Object doesn't support this property or method. It knows wksSourceData is there because it capitalises it.

 

[Johnny C]

It was crashing on this earlier, strFile was not a valid datatype even though I could see in the Locals browser it was a variant/String.
Now its suddenly working and I haven't touched anything in it.

Dim strFile As Variant, CopyFile$
strFile = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*), *.xls*", Title:="Choose a TM1 Input file to open", MultiSelect:=False)
If strFile = False Then
    MsgBox "No file selected"
    Exit Sub
End If
Workbooks.Open Filename:=strFile
CopyFile = Right(strFile, Len(strFile) - InStrRev(strFile, "\"))

 

[Fluff]

You cannot do this ThisWB.wksSourceData it needs to be as I showed.

 

[Johnny C]

And now application.ScreenUpdating no longer works. I will get in touch with our IT, something is very wrong.