Sporadic Error Copying Sheets from one workbook to another

[jay]

First post. I appreciate any assistance on this issue:

I have created VBA code within a worksheet that does the following:
a) create 1 new worksheet
b) open another excel workbook.
c) go to a worksheet within new workbook
d) ActiveSheet.Cells.Select
e) paste the selection into the new worksheet in the first workbook.

This process works consistently on my machine, but when I mail the
workbook to someone else, it doesn't work on their machine.
Error that they get:
-creates new worksheet
-opens the other workbook
-copies the data
-pastes the data into a different worksheet or doesn't copy and paste
at all (different errors at different times).

Any ideas?

There is other VBA code that seems to work correctly, it's just this
code that doesn't seem to work all the time.
specifics:
Exell 2000
VBA 6.0

thanks

 

[Guest]

It would help to see the code that does this - it may be a workbook or
worksheet name problem that's causing things to go south.

 

[jay]

It would help to see the code that does this - it may be a workbook or
worksheet name problem that's causing things to go south.

Here's the code:

'create new sheets
Set finsheet = Sheets.Add
finsheet.Name = FSName & " Fin Stmt ##"
Sheets(FSName & " Fin Stmt ##").Move after:=Worksheets("FS Upload
##")
Sheets("FS Upload ##").Activate


'get the financial statement data
Workbooks.Open (FSLocation & FSName)
Workbooks(FSName).Sheets(1).Activate
ActiveSheet.Cells.Select
Selection.Copy

'paste the financial statement data
Workbooks(Model).Sheets(FSName & " Fin Stmt ##").Activate
'Sheets(FSName & " Fin Stmt ##").Unprotect
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Protect
ActiveSheet.Range("B19").Select

'create the variance sheet
Set varsheet = Sheets.Add
varsheet.Name = FSName & " Variance ##"
'Worksheets(FSName & " Variance ##").Move after:=Worksheets(FSName
& " Fin Stmt ##")

'get the variance data
Workbooks(FSName).Sheets(2).Activate
ActiveSheet.Cells.Select
Selection.Copy

'paste the variance data
'This is the process that doesn't seem to be working consistently.

Workbooks(Model).Sheets(FSName & " Variance ##").Activate
Sheets(FSName & " Variance ##").Unprotect
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("B19").Select

 

[Guest]

Where I see a potential for error/confusion is where you use an index number
to reference a worksheet, as .Sheets(1) and .Sheets(2). Since you seem to
know the name of those sheets, you may want to reference them specifically.

I'm assuming that other things we don't see defined here, as Model , FSName
and FSLocation are defined elsewhere since things apparently are working
earlier in the code where they are used.

I notice you have one .Unprotect line commented out - intentional? Same for
one .Move sheet statement. Rhetorical questions.

Finally, are the users actually getting any error messages at any time or
are things "just not working" without any real error indication given to the
user? If they aren't getting error messages, unless somewhere in you have an
On Error Resume Next statement, then that would mean that the operations
requested are being performed - they just are not being performed from/on the
places you think they should be.

You could try to track things down by adding a sheet to the book with the
code in it, call it LogSheet. Somewhere near the beginning of all of this
processing, or a little ahead of where you think the problem is, you can
start logging what the code is doing and look at that log sheet when things
don't go right - just have the other person copy that sheet to a separate
workbook and email it to you or even send you the complete file. Then you
can look and see exactly what Excel was doing.

Here's a snippet from your code with some lines added to show how to do
this. with the LogIt(LogEvent as String) sub that I've set up. Needs a sheet
in that workbook named "LogSheet", obviously. Be generous in the information
you place into the log - for this situation, I'd echo all the variables that
are to be used in the following active statement, as the workbook name, sheet
name and action to be performed. Might even add a number to each one to
easily identify where in the code you are when that particular event took
place since you will probably have numerous similar types of actions. Be
generous in setting up the logging code and I think it'll pay off for you.

I hope this all helps some. I just don't see the immediate problem, and
while it may be staring me in the face, I'm apparently blind to it.

By the way: I'd set up Const values in the ccode for some of the standard
added parts of worksheet names as " Variance ##" and " Fin Stmt ##" - and use
those instead of the string literals in the code. This will accomplish 2
things: it will allow the code to run just a little quicker, but more
importantly it will guarantee consistency and remove the risk of accidentally
adding a space to one of those entries within the code. Something like this
near the start of the routine:
Const VariancePhrase = " Variance ##"

then to use it, instead of
Workbooks(Model).Sheets(FSName & " Variance ##").Activate
you would use
Workbooks(Model).Sheets(FSName & VariancePhrase).Activate


'paste the variance data
'This is the process that doesn't seem to be working consistently.

'*****
'sample LogIt entry
LogIt "E1: Activating Workbook: " & _
Model & " Sheet: " & FSName & " Variance ##"

Workbooks(model).Sheets(FSName & " Variance ##").Activate
'*****
'sample LogIt entry
LogIt "E2: Active Workbook/Sheet is now: " & _
ActiveWorkbook.Name & " " & ActiveSheet.Name
LogIt "E3: Unprotecting Workbook/Sheet: " & _
ActiveWorkbook.Name & " " & ActiveSheet.Name

Sheets(FSName & " Variance ##").Unprotect
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("B19").Select
'..... your code continues

Sub LogIt(LogEntry As String)

Static NextRow As Long
Static ClearedFlag As Boolean

If Not ClearedFlag Then
ThisWorkbook.Worksheets("LogSheet").Cells.Clear
ThisWorkbook.Worksheets("LogSheet").Range("A1").Select
ClearedFlag = True
End If
ThisWorkbook.Worksheets("LogSheet"). _
Range("A1").Offset(NextRow, 0) = LogEntry
NextRow = NextRow + 1

End Sub

 

[jay]

Thank you for your suggestions. I responded to you earlier, but my
response didn't show up in the forum, for some reason.
I'm adding in the log function at this time - thank you for the code
for that.

I've also changed the reference to the external sheets. Your idea on
the const for the rest of the new sheet name is a good idea.

As for your question - the other user is not getting an error message
- the routine simply pastes the variance data into the financial
statement page, instead of the variance sheet.


Note that I added in the LogIt Sub that you sent me, and I'm getting
the following error:
"Run Time Error: '1004'. Select Method of Range class failed.
At this line:
ThisWorkbook.Worksheets("LogSheet").Range("A1").Select

Any ideas?

 

[jay]

Thank you for your suggestions. I responded to you earlier, but my
response didn't show up in the forum, for some reason.
I'm adding in the log function at this time - thank you for the code
for that.

I've also changed the reference to the external sheets. Your idea on
the const for the rest of the new sheet name is a good idea.

As for your question - the other user is not getting an error message
- the routine simply pastes the variance data into the financial
statement page, instead of the variance sheet.

Note that I added in the LogIt Sub that you sent me, and I'm getting
the following error:
"Run Time Error: '1004'. Select Method of Range class failed.
At this line:
ThisWorkbook.Worksheets("LogSheet").Range("A1").Select

Any ideas?

I commented out the "select" line and the log works great. Thanks
again.

 

[jay]

I commented out the "select" line and the log works great. Thanks
again.

Last update that I will make - I received word back from the LogIt
version.
I had also commented out the "On Error Resume Next" .
Now, it appears that he is getting Run Time Error 9: Sub-script out of
range errors. Constantly. He had a sub-script out of range before
the first log entry- when the routine tried to close an open excel
file (it opened it okay).
I commented out this function and the routine failed at the next step.

Any ideas why I'd get Run-Time "9"?
I have started a search on the web, it seems that run time errors are
associated with mis-named worksheets?

 

[Guest]

My boo-boo (one I seem to be prone to)
Cannot select Sheet and Range at same time. Delete that entire instruction
from the code - isn't even needed.

 

[Guest]

Runtime Error 9 is, as you noted, Subscript out of range. Normally you will
see it if you have an array dimensioned as myArray(1 to 10) and you try to
reference an element that does not exist as myArray(11).

In the case of VB code in Excel you could be trying to either reference a
Workbook or Worksheet using the wrong name. Or the name could be correct,
but not exist in the workbook.

This is also where those new constants for things like " Variance ##" and
such can really help. The log entries also. You're going to need to examine
the actual sheet names in the books (as on their tabs) very closely. An
error I see almost as often as the one in that code I provided is to have an
extra space character either at the start of or end of a sheet name. You
don't see it, but it's there and Excel considers it part of the sheet name.
So when you go to do something like
Worksheets("ABC").Select, but the sheet name is really "ABC ", you end up
with a perplexing Run Time Error 9.

You can change some of the calls to LogIt to help you identify such a thing.
More work, but it might pay off. Take a line like:
LogIt "E1: Activating Workbook: " & _
Model & " Sheet: " & FSName & " Variance ##"

Change that to enclose the parameters you're reporting within special
characters so you can see exactly where they start/end:
LogIt "E1: Activating Workbook: '* & _
Model & "* Sheet: *" & FSName & VariancePhrase & "*"
That will make it easier to see extra leading/trailing phrases.

Of course the actual line of code in the routine that is highlighted when
you go to Debug when the Error 9 pops up is going to be a big clue also.

 

[Guest]

TYPO correction - the new form of the call to LogIt should have been like this:

LogIt "E1: Activating Workbook: *" & _
Model & "* Sheet: *" & FSName & VariancePhrase & "*"