macro error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JT - 21 Mar 2006 21:09 GMT

7 users have their own copy of the a macro. 6 of them are using their copy
and not having an issue. The 7th one is getting incorrect results with their
own copy.

The macro (1) retrieves data from a template in the active workbook (WB1),
(2) opens a second workbook (WB2), (3) finds the first blank row in WB2, (4)
writes the data (from WB1) in the blank row in WB2, (5) "saves" WB2, and (6)
closes WB2.

Application screenupdating is turned off, so users don't see WB2 opening and
closing.

For 6 users, this works great and there aren't any issues. But the 7th user
is another matter.

This is the code to open WB2:

Workbooks.Open (vPath & vFile) ' both variables defined earlier
LogBook = ActiveWorkbook.Name

.....then there is code to find the correct sheet and row in WB2.........

The issue occurs at this line of code (where the data is written in WB2):

ActiveCell = "CR" & a & b

For all other users, the activecell is the first blank row and column A in
WB2. For the 7th user, this line of code takes the user back to WB1 and
starts writing the data in the activecell in WB1.

Why? Any suggestions would be greatly appreciated. All users have Excel
2003. I have also tried it with screenupdating on and off and I get the same
results each time. I have also checked the references and they are the same
for all of the users. I'm stumped as to why this one user is getting
different results with the macro. Thanks for the help.

Signature

Reply to this Message

Duke Carey - 21 Mar 2006 22:08 GMT

Hard to say why, but you can probably avoid it by creating a workbook
variable and using it when you open WB2

dim wb as workbook
dim ws as worksheet
set wb = Workbooks.Open (vPath & vFile)
LogBook = wb.Name

then, when you find the correct sheet in WB2, set the ws variable to that
sheet

set ws = wb(worksheets("sheet1") ' for instance

Now, don't use ActiveCell, but qualify the range with the worksheet variable
And you don't have to select the cell to set its value.

ws.range("A"&16).value = "CR" & a & b

> 7 users have their own copy of the a macro. 6 of them are using their copy
> and not having an issue. The 7th one is getting incorrect results with their
[quoted text clipped - 31 lines]
> for all of the users. I'm stumped as to why this one user is getting
> different results with the macro. Thanks for the help.

Reply to this Message

JT - 21 Mar 2006 22:18 GMT

Thanks for the suggestion. I'm going to give that a try and see what
happens. Thanks again for the help........

Signature

> Hard to say why, but you can probably avoid it by creating a workbook
> variable and using it when you open WB2
[quoted text clipped - 49 lines]
> > for all of the users. I'm stumped as to why this one user is getting
> > different results with the macro. Thanks for the help.

Reply to this Message