I have a program I created for my son's company that analyzes monthly Sales data (along with Margin and Commissions) for individual agents. The filename is "Commission File Update Program.xlsm". As part of the program I sum up each month's data and write it to a table that I maintain on a worksheet that resides within my program workbook. I recently rewrote this particular code module in an effort to optimize the time required for the module to run. As some point I saved a copy of the program file using the existing filename with "-Temp" added so I could maintain the integrity of data that is kept within the file. Once I retrieved the data I then resaved as "Commission File Update Program.xlsm".
I have declared variable 'vlwb' to be ThisWorkbook:
and variable ctws as Worksheet ("Ind Trend Backup") within Workbook vlwb
I have another variable 'currmonth' that is set to the month of the new dataset that is received every month.
So, the code module summarizes the monthly data for each agent and maintains a rolling 12 month view for each agent. Column C contains Month/Year values. In order to accomplish this, I Loop through the worksheet where the 12 month view is maintained looking for the value in Column C is equal to currmonth. I delete that row.
I then look for the value of "GRAND TOTAL" in column C and delete that row as it will be rewritten with updated values later in the code module. At this point, in order to reset this worksheet in preparation for the new month's data, I insert 2 Rows and then copy the agents name and agent code to the 2 Rows I just added.
This is where the problem begins.
When this line of code runs to perform the copy function, I am presented with a file open dialog box and the filename the program is looking for is: "Commission File Update Program-Temp.xlsm".
The other interesting fact is that since this code module was rewritten, 3 new agents have been added. When that happens I write them to this worksheet as the first agent by adding rows and inserting information for that agent. When stepping through the code line by line, the line of code the performs the Copy function works without presenting the file open dialog box for these 3 new agents. But on the 4th agent (that has been in existence prior to the code rewrite) when the Copy function is executed is when the file dialog box is presented. I have tried a couple different approaches to rewrite this line of code to no avail.
Any ideas why the code would be opening the file dialog box and what can I do to prevent it from happening?
Any and all help would be much appreciated!
I have declared variable 'vlwb' to be ThisWorkbook:
VBA Code:
Set vlwb = ThisWorkbook
VBA Code:
Set ctws = vlwb.Sheets("Ind Trend Backup")So, the code module summarizes the monthly data for each agent and maintains a rolling 12 month view for each agent. Column C contains Month/Year values. In order to accomplish this, I Loop through the worksheet where the 12 month view is maintained looking for the value in Column C is equal to currmonth. I delete that row.
I then look for the value of "GRAND TOTAL" in column C and delete that row as it will be rewritten with updated values later in the code module. At this point, in order to reset this worksheet in preparation for the new month's data, I insert 2 Rows and then copy the agents name and agent code to the 2 Rows I just added.
VBA Code:
ctws.Range("A" & x + 2 & ":B" & x + 2).Copy ctws.Range("A" & x & ":B" & x + 1)When this line of code runs to perform the copy function, I am presented with a file open dialog box and the filename the program is looking for is: "Commission File Update Program-Temp.xlsm".
The other interesting fact is that since this code module was rewritten, 3 new agents have been added. When that happens I write them to this worksheet as the first agent by adding rows and inserting information for that agent. When stepping through the code line by line, the line of code the performs the Copy function works without presenting the file open dialog box for these 3 new agents. But on the 4th agent (that has been in existence prior to the code rewrite) when the Copy function is executed is when the file dialog box is presented. I have tried a couple different approaches to rewrite this line of code to no avail.
Any ideas why the code would be opening the file dialog box and what can I do to prevent it from happening?
Any and all help would be much appreciated!
