I'm working on the following macro. It opens a file then copies some data over. Then I want the macro to insert the formula in the col I choose and repeat for every sheet. Right now the macro inserts the formula and asks me what column for all 4 sheets but keeps inserting formula on same sheet. I only want macro to ask me once what column then do it for all sheets.
Not sure how to fix.
Thank you very much for your time and help.
Please Login or Register to view this content.
As a start, if you only want the input box shown once, take it outside of the loop, that way it will only be shown once, you would also want to take the next line, that checks for valid data outside of the loop as well.
Also I would define wrkSheet as Worksheet rather than Variant
Last edited by RobynC; 11-12-2007 at 11:22 AM.
Hi again,
The reason that it's not doing all the sheets is that the way the code is written, it required the worksheet to be the activesheet, when your code is looping through the sheets it doesn't activate them and so the formular is just copied to the cells on the same sheet for as many times as there are sheets.
the following code will transfer the formular to all the sheets and only ask you for the input box once. The method though I'm sure is not very efficient, and there is a better way to do this, but it works (have not changed the first part of the code)
Looking at the first part of the code though, i would say that it's not a very efficient method of performing the action. I've not been working with VBA very long, so would not feel comfortable suggesting a different way, but it may be worth asking some of the really clever guys on here the best way to tidy it up.Please Login or Register to view this content.
Hope this helps in the meantime. Robyn
ThisWorkbook refers to the workbook the code is in, not the workbook you've just opened.
If you are working with multiple workbooks you should create references to them and then use those in your code.
For example:
Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.
robynC,
works perfect thank you very much.
Norie,
Thank you for the advice I will make changes now. I might post back if I have a ?. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks