LinkBack URL
About LinkBacks
Hello. I want to select open files based on certain name endings, but I don't really want to use if statements. How can I do that? For example below I just want to activate sheet.
Please Login or Register to view this content.
Hello. I want to select open files based on certain name endings, but I don't really want to use if statements. How can I do that? For example below I just want to activate sheet.
Please Login or Register to view this content.
What would you like to use instead of IF statements and why are you trying to avoid them?
Case Select is an option outside of an IF statement.
http://www.excel-easy.com/vba/examples/select-case.html
http://www.exceltrick.com/formulas_m...ase-statement/
https://msdn.microsoft.com/en-us/library/cy37t14y.aspx
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Hi Mike. I'm thinking more of a variable. So you say, worksheet(1) for example. How can I create a variable that selects any sheet with a certain ending.
Well now you are talking about selecting sheets and adding them to an array which gets a bit more complicated.
I would want to know what you are trying to accomplish as you might be taking the long way around.
Hi Mike. Here is an example. I already have all my worksheets open. Now I want to select an open workbook based on it's ending and use it to reference the workbook. The reasoning is that I will never know the exact name of the workbook, just that it has a certain ending (five letters)
ThisWorkbook.Sheets.Range("A11").Formula = Workbook value & "-" Workbook last value in column value
This is the code I have so far. Essentially I have master file where code is going to be run and then I will be working with four files whose names will change each time (except for their endings). I would like to be able to reference these workbooks without getting lost in if statements.
Please Login or Register to view this content.
Why are you opening all the workbooks at the same time?
Which workbook do you want to open and work with?
If it's the one with the filename ending with 'EFTPY.xls' check the filename before you open the workbook.
If posting code please use code tags, see here.
Hi Norie. Well, I'll be copying back and forth between files and the main file, so it seems annoying to open up each one, close, open etc. I want to open and work with all of them.
Not sure I followed the check the file name comment. Thanks!!
What, in words, are you trying to do?
Are copying from one specific file, specific files or all the files in a directory?
Also, where are you copying to in the main workbook?
Is that based on some criteria, perhaps something to do with the filename?
Yeah I think we need to start from the top as you might be trying to pave a cowpath.
Also, instead of using logic to determine which workBOOK you are trying to open, why not make the user (you if needed) determine the workbook names at the beginning of the code?
Ok, let's see. Let me document it out. (@Mike Tron, that's a good solution about workbook names as well, can you write something where they can select folders and files)? And then wouldn't I need variables to refer to these workbooks so as not to have to write it out each time.
One master file, where code is being run from and where content will be copied into.
First file: copy first worksheet 4 times, rename worksheets, copy two cells from second worksheet into a formula in master sheet, filter and then sum in 1st worksheet. Copy sum into master worksheet. More calcs on remaining tabs.
I think I could actually close each file but after I repeat similar processes on the other sheets, I have to do some processes between sheets.
@ Tron. Could you give me an example of having them select the files and then using as refedrence in code? Thanks'
Is the list of workbooks static and if not do the names change frequently?
Can you not have a list in a column then loop through them?
The names change frequently, but not the endings. I suppose I could have a list. But I still don't know how to reference each open one? Opening the files is not what I have an issue with
Its not the point that you dont know how to open them... I am trying to get you to think about the easiest way to identify them and therefore define them as variables and THEN open them...
This is generally how I deal with allowing the user to define the files in VBA.
http://analysistabs.com/vba/open-fil...-example-code/
Last edited by mikeTRON; 08-31-2016 at 06:39 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks