Phantom Request to Open Backup Copy of Program File

frank_AL

Active Member
Joined
Oct 30, 2015
Messages
439
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:
VBA Code:
Set vlwb = ThisWorkbook
and variable ctws as Worksheet ("Ind Trend Backup") within Workbook vlwb
VBA Code:
Set ctws = vlwb.Sheets("Ind Trend Backup")
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.
VBA Code:
ctws.Range("A" & x + 2 & ":B" & x + 2).Copy ctws.Range("A" & x & ":B" & x + 1)
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,738
Office Version
  1. 365
Platform
  1. Windows
Do you have any formulae in those cells that are looking at the "temp" workbook?
 

frank_AL

Active Member
Joined
Oct 30, 2015
Messages
439
Fluff, thank you for the quick response. in checking for this I did find a VLOOKUP formula that is referencing a Named Range that was pointing to the '-Temp' file.
I have corrected the range name and that corrected the problem! Thank you so much for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,738
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,053
Messages
5,628,325
Members
416,311
Latest member
S991102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top