• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to run a VBA in a new workbook from the existing one

S

soniaalalwani

New Member
Hi Chandoo,


I am trying to run a VBA code in a workbook and after making changes i save the file with a different name. I need to run this macro n number of times. But when its run for the first time and the sheet is saved with the changes, i have to open the master workbook again through the code itself and continue the macro now in this workbook and repeat the procedure for n times. But once the sheet is saved and i open the master workbook the macro code does not continue in the master workbook but remains in the save as workbook. Please help me out from jumbled up problem
 
Is the macro code being saved in/with the new workbook, or is all the coding in the master workbook and you need to call it from the new workbook?
 
That might help. My guess is that somewhere you are calling the macro using the workbook name like:

Run "MyBook.xls!MyMacro"


In which case, you'll want to change it to just:

Call MyMacro
 
Well let me explain my problem once again. I have a master file with n number of clients. I have created a code which will take the input from the user the names of the clients whose data is required. this code is in the Master file.

Now as the first name is input the formating and editing is done in the master file and the same file is saved with the client's name.

Now when the second client details have to be searched i need the code to open a new master file and then search the name there. But once the first file is created the code still runs in the already edited file.


I hope that its clear now. :(


The last few lines of the codes are as below. The rest is just to take the inputs from the user and formatting of the data


Name = ClientName (j) & ".xls"

ActiveWorkbook.SaveAs "D:Files" & Name, FileFormat:=_

x1Excel18, Password:="",WriteRespassword:="", ReadOnlyRecommended: =False, CreateBackup:=False


Workbooks.Open Filename: "D: Master file.xls"
 
Ah. I think that breaks down to:

1. Open Master File

2. Do Stuff

3. Save Master File w/ new name

4. Need to be back in Master file

5. Go back to step 2


The VB code line that should help is

ThisWorkbook.SaveCopyAs "D:Files" & Name
 
Thank you so much for the instant replies but..... :( i am still not able to do what is required. I am making another effort to make my problem clear


Process


1. Open Master File

2. Do Stuff ( Here the master file gets edited- except the particular client's detail the rest of the data gets deleted)

3. Save Master File w/ new name

4. Need to be back in Master file

5. Go back to step 2 (Now the search for the client is done in the edited master file. Here i want the code to either close the opened master file without saving, open it again and then go to step 2. or anything else that will help)
 
So, it sound like you need to save the Master File after you do the first set of edits

the search for the client is done in the edited master file
Click to expand...

then you do more stuff which you don't want saved.


From a macro standpoint, I think I'm seeing what the problem is now. There's no one workbook that stays open all the time, so there's no one place for the macro to be...

perhaps we could use a 3rd "dummy" workbook? It could:

open the master file

make first edit and save as master file

make more client-specific edits and save as client specific

close client specific file, re-open master file


Now the macro is always in a workbook that's open. Is that more on the right track?
 
Sonia,


Here is your above code...

Code: 
Name = ClientName (j) & ".xls"

ActiveWorkbook.SaveAs "D:Files" & Name, FileFormat:=_

x1Excel18, Password:="",WriteRespassword:="", ReadOnlyRecommended: =False, CreateBackup:=False


Workbooks.Open Filename: "D: Master file.xls"




Try to use the below




Name = ClientName (j) & ".xls"

ActiveWorkbook.SaveAs "D:Files" & Name, FileFormat:=_

x1Excel18, Password:="",WriteRespassword:="", ReadOnlyRecommended: =False, CreateBackup:=False


ActiveWorkbook.close True 'this will close the newly created file


Workbooks.Open Filename: "D: Master file.xls"


Let us know if this helped...


~VijaySharma
 
Thanks Vijay. But i have already tried this. When i close the active workbook the macro too gets closed with it and it does not continue in the new work book. I have thought of a new way. I will create two different macros and call the second one using the first one in any open workbook. I hope that works. If it will i will surely post the code. In the mean while if anyone of you gets a better idea pls help
 
Did you try the suggestion of putting your code in another workbook besides "Master file"?


With your overall approach to the task, you will have the exact problem you are having.


Try creating a new workbook such as "Master File Automater" or what have you. Put the code there. You will have to modify any references to "active workbook" to refer to the opened "master file", or else after opening "master file" make it active like:

[pre]
Code: 
Dim w As Workbook
Set w = Workbooks.Open(Filename:="D:Master file.xls")
w.Activate
[/pre]
Notice that you get a variable pointing to the workbook you've opened with these changes as well, and you could just use that variable for your Workbook object instead of depending on which workbook is active for most purposes anyway.


Asa
 
An alternate solution would be to NOT modify "Master file" with your macro, but copy the desired data from Master File to a nwe workbook, and save that instead.
 
Sonia,


Yes there is always another way out...


Code: 
Sheets(array("sheet1","sheet2","sheet3")).Copy


The above line will automatically create a new workbook for you with the sheets copied from the master... (you need to change the names as per the actual ones).


You will now have 2 workbooks open...your master workbook and a new (UNSAVED) workbook containing 3 sheets (as per the example above).


write code to save the new workbook with the name of the client...(this workbook will not have any macros at all).


Give this a shot and let us know...


~VijaySharma
 
You must log in or register to reply here.
Back
Top