pandre45
pandre45

Reputation: 49

Creating a way to copy sheets from a .xlsm file to a .xlsx file

I am trying to copy sheets from a macro enabled Excel file (.xlsm) to a non-macro enabled file (.xlsx). I believe that some change have been recently made to Excel that is preventing using the process I used before: Include in the a VBA code that saves the ".xlsm" as ".xlsx" the following instruction: "Application.DisplayAlerts = False".

I tried the following process (and it almost worked!):

  1. Create an empty non-macro enabled file (.xlsx).
  2. Open the macro enabled file (.xlsm).
  3. Select the sheets to copy from the macro enabled file (left click the sheet tab of one sheet to select it and then "shift + left click" on the others).
  4. Right click on the tab selected first. This opens a small window with some options.
  5. On the small window left click on "Move or Copy" option. This opens a new small window.
  6. On the bottom of new window select the box at left of "Create a copy".
  7. On the "To book" at the top of the new window select the ".xlsx" file.
  8. Click "OK" button, (sheets are copied to the ".xlsx" file).

I was unable to save the non-macro enabled file, created by the above process, because an error window popped up with four buttons: "Save", "Save and erase features", "Go back" and "Help". The previous method "Application.DisplayAlerts = False" no longer worked.

Code triggered by activeX controls were copied to the ".xlsx" file, as well. I solved this problem by removing sheets that had elements like: buttons, activeX controls, etc. Then I could save the ".xlsx" file. I will have to restructure, or get rid of, some code that are triggered by activeX controls.

But, some links to the ".xlsm" file were created in the ".xlsx" file. I was unable to understand the reason.

enter image description here

I do not know how, and ask help on, to avoid the creation of links to the ".xlsm" file on the ".xlsx" file.

Upvotes: 1

Views: 481

Answers (0)

Related Questions