Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro error
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. -- JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro error
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 "JT" wrote: 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. -- JT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro error
Thanks for the suggestion. I'm going to give that a try and see what
happens. Thanks again for the help........ -- JT "Duke Carey" wrote: 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 "JT" wrote: 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. -- JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
naming tab macro error runtime error 1004 | Excel Programming | |||
Macro 'Automation error' with ChemOffice Excel macro | Excel Programming |