Thanks for your efforts Chip.
I have tried both "just for fun" ideas.
1. I typed the Application.GoTo line into the code in lower case as I
usually do and look for anything not converted to camel case by the editor.
reference:= and scroll:= remained in lower. It generated runtime error 9 -
subscript out of range even after I corrected the quoted sheet name.
2. Option was initially set to "Unhandled". After I changed it to "Break
All" I had to REM the call to WBIsOpen because the Set w = statement
wouldn't let me past. Having avoided that, the result was unchanged. No
other errors. What is diff between "Unhandled" and "Break in Class"?
One other thing though - earlier in my experiments when I forgot to unREM
the On Error, the Set w = threw a runtime error 9. I did the unREM, set next
statement as the On Error line and pressed F5. It went on to work properly.
That has been the only time it worked other than by single stepping. Is this
a clue??
--
Len
__________________________________________________ ____
"Chip Pearson" wrote in message
...
Please correct me here if I misunderstand, but shouldn't the A21.Select
statement operate on the active sheet whatever it is.
You are correct. As long as your code is in a standard code module like
Module1, the references will roll up from Range to ActiveSheet to
ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet
module. But since your code is in fact in a standard code module, this
should not be an issue.
As I see it, the Resume Next cannot fail to be undone by the final line
of the function.
That is correct. In fact, the final On Error Goto 0 is not necessary, but
it is harmless.
Initially, the code used to say
Workbooks(stExportBook).Sheets(".. capex..").select
You cannot select a cell on a sheet that is not the active worksheet. You
must first Activate the workbook, then Select or Activate the sheeet (the
difference between Select and Activate is clear when you have grouped
sheets), then finally Select or Activate the Range. You can't do it all on
one line of code.
Just for fun, try commenting out
Workbooks.Open stExportFull, 0 ' so open it without updates
Sheets("Capital Work (CAPEX)").Select
Range("A21").Select
and replace it with the code
Application.Goto _
Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _
Scroll:=True
This *shouldn't* make a difference, but you are in an odd set of
circumstances, so who knows, it might work. Beyond that, your code looks
sound and I didn't see anything that looked troublesome.
Again, just for fun, in VBA go to the Tools menu, choose Options, then the
General tab, and change "Error Trapping" to "Break On All Errors", just to
see if some error is getting ignored. The proper setting of this option
for normal execution is "Break In Class Module".
I'm out of ideas.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Len B" <gonehome@optusnet:con:au wrote in message
...
Thanks Chip,
The code is in Module1. Also in Module1 is the WBIsOpen function - the
only code anywhere with On Error.
Module2 contains DelData() which resets certain ranges to empty.
Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range).
All other sheets including ThisWorkbook are empty
Private Function WBIsOpen(stWBName As String) As Boolean
Dim w As Workbook
On Error Resume Next
Set w = Workbooks(stWBName)
If Err = 0 Then
WBIsOpen = True
'Looking fresh, I think I should have "Set w = Nothing" here???
Else
WBIsOpen = False
End If
On Error GoTo 0
End Function
As I see it, the Resume Next cannot fail to be undone by the final line
of the function.
In any case, I REMmed out the If WBIsOpen and matching End If so that the
function would not execute at all. The result was entirely the same.
Then, I even REMmed the OnError Resume Next, saved and re-launched. No
change in result.
Please correct me here if I misunderstand, but shouldn't the A21.Select
statement operate on the active sheet whatever it is. As an experiment, I
moved the cell pointer to B35 in every sheet in both workbooks and saved
them both. Not one cell pointer moved when I executed the code.
Initially, the code used to say
Workbooks(stExportBook).Sheets(".. capex..").select
and that didn't work so I pasted over it with code from the macro
recorder to reassure myself about invisible (to me) typos.
I am assuming that's what you mean by fully qualified.
Any other thoughts?
--
Len
__________________________________________________ ____
"Chip Pearson" wrote in message
...
Where is the code located? If it is in the ThisWorkbook module or a
Sheet module, references resolve differently than if the code is in a
standard module. You should fully qualify the objects. Also, remove any
On Error statements you may have. It might be the case that perhaps the
results depend on what sheet is active and an error is being ignored by
an On Error Resume Next.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Len B" <gonehome@optusnet:con:au wrote in message
...
I have a situation where I want to open a different workbook and go to
the next available row and paste some data there from the calling
workbook.
The following code works if I single step through it but not if it runs
in real time. Why is that? Is there a better way to achieve what I am
after?
In real time it just opens the workbook to where it was last saved - a
different sheet altogether and it doesn't select A21 in that sheet
either. So that's 2 statements that do not execute in real time.
If Not WBIsOpen(stExportBook) Then ' workbook isn't open so
Workbooks.Open stExportFull, 0 ' so open it without updates
Sheets("Capital Work (CAPEX)").Select
Range("A21").Select
End If
TIA
--
Len
__________________________________________________ ____