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

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.

" wrote:

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

On May 2, 1:52 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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

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").Se lect
ClearedFlag = True
End If
ThisWorkbook.Worksheets("LogSheet"). _
Range("A1").Offset(NextRow, 0) = LogEntry
NextRow = NextRow + 1

End Sub



" wrote:

On May 2, 1:52 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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

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").Se lect

Any ideas?




On May 2, 10:17 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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

On May 2, 12:25 pm, wrote:
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").Se lect

Any ideas?

On May 2, 10:17 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:

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

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

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.


" wrote:

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").Se lect

Any ideas?




On May 2, 10:17 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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