VBA, make code move across from main file to extract file

Gerald Higgins · Oct 29, 2019

Thanks in advance for any help !

So I’m still learning VBA, using a combination of the macro recorder, this website, trial and error, and so on.

I have a file, let’s call it “FILE A”, which has a number of worksheets. The file includes some code that pulls in data from a number of other files. This all works fine.

There is also some code to take one of the worksheets in the file, and create a separate file containing just that single worksheet, let’s call it “FILE B”. This also works fine.

The intention is that FILE B will get emailed out to a number of people, who won’t have direct access to FILE A.

There is some code in FILE A that does some minor formatting to the single sheet that gets turned into FILE B. This code is very simple, basically changes some number formats and column widths, and nothing else. It allows users to toggle between two different number formats. This code works fine in FILE A, but I’d like it to also work in FILE B, and that’s what I’m having a problem with.

When I try this, running the code in FILE B, it tries to access the code from FILE A. But this isn’t going to work once the file is mailed out to people, as they won’t have access to FILE A.

The code was originally sitting in FILE A in a standard worksheet module. I then tried moving the code onto the relevant sheet that becomes the standalone file, and this still didn’t work.

What do I need to do to make this bit of code get copied into the extract file, FILE B, so that it will run correctly for anyone who does not have access to FILE A ?

Thanks again in advance for any help.

Fazza · Oct 29, 2019

Hi , Gerald

Maybe a different approach. What if to make file B, the VBA copies the ENTIRE file A then delete the worksheets you don't want?

regards, Fazza

Gerald Higgins · Oct 29, 2019

Thanks for the suggestion Fazza, I hadn't thought of that. I'll give it a try. I have to say I'm not optimistic, but let's see. I'll post back. Thanks again !

Fazza · Oct 29, 2019

If it doesn't work, then we need to know why. Single step (F8) through the code in File B and see what is happening.

Fazza · Oct 29, 2019

Late in my day. I'll check back tomorrow.

I remember when I first started coding, worked all day Friday at work and then all night at home til the sun was up. Geez, it took a long, long, long time to sort out very simple stuff.

Stick with it

Gerald Higgins · Oct 29, 2019

So the code is launched from a button (a standard rectangle shape with a macro assigned to it).
When I right click on the button in FILE B to check what macro is assigned, it shows the file path and file name for FILE A.
And when I click on the button in FILE B (having disconnected my network connection so that I know that FILE A is not accessible) , I get an error message which says . . .
"Could not open . . . FILE A" (or words to that effect).

Fazza · Oct 29, 2019

OK. Googling with "Excel VBA macro button links to original file when copied"

First hit for me was https://stackoverflow.com/questions...-of-macro-to-command-button-when-copying-file
And that linked to https://www.thespreadsheetguru.com/...igned-macro-links-after-copying-worksheet-vba

BTW, simpler might be to use a command button instead. Something like

Private Sub CommandButton1_Click()
YourSub
End Sub

Gerald Higgins · Oct 30, 2019

Fazza - those links look really interesting, that's my exact problem

Thanks very much for posting these.
I've also dabbled a little with the option of saving a copy of the whole file and then deleting the sheets I don't want, and it looks like that might also work, despite my original pessimism

It might take me a day or two before I can check this fully.
In the meantime, thanks again !

Fazza · Oct 30, 2019

you're very welcome, Gerald. all the best

Gerald Higgins · Oct 31, 2019

So i went with the code at www.thespreadsheetguru.com (via Fazza), and it worked perfectly !

Thanks again Fazza !

I suspect Fazza's other proposal, of simply saving a copy of the entire file and then deleting the unwanted sheets, would probably have also worked but I didn't pursue it further as I guessed that it might be very slightly slower than the method I went with.

VBA, make code move across from main file to extract file

Gerald Higgins

Well-known Member

Excel Facts

Fazza

MrExcel MVP

Gerald Higgins

Well-known Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Gerald Higgins

Well-known Member

Fazza

MrExcel MVP

Gerald Higgins

Well-known Member

Fazza

MrExcel MVP

Gerald Higgins

Well-known Member

Similar threads

Forum statistics

Share this page

VBA, make code move across from main file to extract file

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock