macros fail to work on extracted sheet (links to other data sources)

[mtcolombo]

I hope someone has a solution, because this is driving me bonkers!

I have several macro buttons (not command buttons) on a sheet. One of the buttons extracts that sheet and saves the file to our server with a specific name (based on 2 cells) and to a specific folder. All of that is working fine. However, when that file is opened (by anyone OTHER than the original creator,) the macros no longer work and reference the original file name from the primary workbook.

How can I get these macros to pertain to the active sheet and 'follow' the sheet to the new file (new name and new location) and NOT reference the original file?

If I right click on the button, and click 'assign macro', the macro name is 'FILENAME'!MACRONAME. If I try to select the macro from the list below that, it just shows the MACRONAME, but when I click on it again, the filename returns to .

The error that I get when I open it on any other machine is "This workbook contains links to other data sources." The file in question that it is trying to reference CANNOT be on the server, so that is not an option.

Thanks in advance
M

[xladept]

Can you post the extraction and save code?

[mtcolombo]

the 'test' file is the original (i have removed the other tabs due to company security.)
the 'extracted' file is after the 'extract' button was pressed. the file was created to a location on our server.

please let me know if you need more info.

[xladept]

Hi MT,

Maybe:

Private Sub extract_page_Click()

Application.ScreenUpdating = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs _
Filename:="T:\Project Equipment Lists\" & Range("F3") & _
"-" & Range("B7") & ".xlsm", FileFormat:=52
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

[mtcolombo]

I was finally able to try this out this morning, and I still get the same error. On the extracted sheet, it is trying to reference the macros from the original sheet.

[xladept]

None of these are included in the extract:

copy_job_number
copy_project_addy
copy_project_name
copy_project_contact
copy_equip
copy_notes

They need to be in the module with the others.

Once you clean up the code, this may accomplish what you want:

Private Sub extract_page()
Dim P As String, N As String: P = ActiveWorkbook.Path: N = ActiveWorkbook.Name
Dim FN As String: FN = P & "/" & N
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"T:\Project Equipment Lists\" & Range("F3") & "-" & Range("B7") & ".xlsm", FileFormat:=52
Workbooks.Open Filename:=FN
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Ignore the file, I couldn't detach it Check out this one instead TestBookXTract.xlsm

[xladept]

Hi MT,

Your buttons are specifying the source sheet. You may need to use ActiveX controls instead of the standard ones!