Posts by Rockymtngirl

    Re: choose specific worksheets in a workbook

    So I substituted jolivanes code where he said to substitute, and it did not work.

    My code now looks like this:

    The above code actually deleted all the columns in the first sheet and did nothing to any of the other sheets.

    What did I do wrong?

    Re: choose specific worksheets in a workbook

    I want to delete columns B and C. They usually don't contain data.

    I looked at your code, but I don't understand what it is doing, especially with the MsgBox stuff.

    What I want a macro to do is select sheets 1, 2, & 3 and then the odd numbered ones after that up to however many sheets are in the workbook. The part I don't know is how many sheets are going to be in a workbook because I would like to apply this macro to the many different workbooks that I have to process.

    Once the sheets are selected my macro deletes columns B and C from those sheets. Example of my code is below. Since each workbook can contain different numbers of sheets, I just need the macro to 1) figure out how many sheets are in the workbook, and then 2) make the array expand or contract to match the number of sheets. I don't know how to do that.

    If there is an easier way to do this, let me know.


    I would like to choose specific worksheets in a workbook and delete certain columns that normally don't contain data. How do I call those specific worksheets?

    The number of worksheets will vary with each workbook and the sheets have different names, so there is no standard sheet name to use in the code. I do need to choose the 1st, 2nd, and 3rd sheets every time. After the 3rd sheet, then I need to choose every other one from there, so for example sheets 5, 7, 9, etc. The only problem is I don't know how many sheets will be in a workbook, and I want to be able to use the macro on many workbooks.

    Thank you.

    Re: Call changing workbook names into code

    Well, yes that's sort of the end result I'm after. But I have many such workbooks that I’m opening and overwriting the previous month data with the new month data which is why I thought there might be a way to incorporate a changing workbook name into a macro, so that this monthly process can be more automated.

    I have a macro that does most of the (lining up) work. I have included it below this paragraph. The part that is missing from it is the part telling it to pull the data from the new workbook into the old workbook (the workbook names change and this is the part I can't figure out how to automate). I wanted to refine the macro to automatically pull that data from the workbook and place it in column M of the SUMMARY sheet of the old workbook. After that is accomplished, I've used a manual process of reviewing the data between the two months. I line up the line item descriptions and then either insert or delete rows for any mismatches between the two months. When that looks good, I use another macro to finish the overwrite process (the code that I had previously included in initial post). This finishes the process and creates the new month's report when it is saved with the new month's name.

    Code for doing preliminary line up work for comparing the two months:

    The reason I have to do it this way is because the cells in the variance sheet are linked to cells in the SUMMARY sheet. If I just copy and paste the current month’s data into column A of the previous month’s (March) SUMMARY sheet, then it corrupts the links in the variance sheet and I get a bunch of #REF errors in the variance sheet. I have found that if I only copy the data and not the line item descriptions of the current month’s data (April) and overwrite the old data (March) starting in column B (SUMMARY sheet), then I don’t get #REF errors on the linked variance sheet. However, in order to do that, I do have to compare the line items against each other, April to March and then either insert or delete rows accordingly. I know, it sounds convoluted, but this is what I figured out to do. I am not a programmer. If there is a better way, I’d be happy to know about it.

    I'm looking for code to pull the data from the new workbook into the old workbook, but the problem is that the workbook name changes each time because I am working with different department reports each time. That's the part I don't know how to do.


    I create actual to budget reports each month for many departments. The workbooks contain a variance report sheet that is automatically updated through cell links referencing data in the SUMMARY sheet of the workbook. Each month I overwrite the SUMMARY sheet so that the variance sheet will update with the new information.

    The process used to overwrite is accomplished with a macro after preliminary work is done. The preliminary work entails running the current month’s report out of a separate report writer, exporting it to Excel, and then copying and pasting this result (SUMMARY sheet data) into a vacant, adjacent column in the SUMMARY sheet of the previous month’s report. This allows me to review and align line item descriptions between the two months’ data with insertions or deletions of rows as necessary. After this is done, I can run the macro that will overwrite the previous month’s data with the new data. I can then save the previous month’s workbook with its new name defining it as the current month.

    What I need help with is copying the SUMMARY sheet data from the current month into the previous month’s data at column M, so that the overwrite macro can complete the process. The issue is not knowing how to call the workbook in a macro since the workbook name changes each time I repeat the process with new departments. I don’t want to have to go into the macro each time the workbook name changes to change the name of the workbook. Can this be done?

    Attached are two sample workbooks and the code that runs after I have pasted the current month’s SUMMARY data into the previous month’s SUMMARY sheet at column M (OverwritefinishDeptFS).

    NOTE: there are usually many more sheets in the reports, and I have excluded those in my samples as they are irrelevant to the task (I think).

    Thanks in advance for any assistance.

    Re: Matrix lookup

    This also worked, but the second formula has an error in the H and I cell references. It should be row 4, not row 5.

    Thank you for your input. I've had less success understanding this code, mostly because I don't use lookup so am not familiar with its syntax, but will study, along with index/match.

    All of the folks who replied - your responses will work for me. Thanks!

    Re: Matrix lookup

    This was very helpful and am so appreciative of your input. It works the way I would like it to and as an added plus, I was able to understand it.

    Thanks in advance for attention and help.

    Each month I must look up teams based on IDs and date periods. A Vlookup doesn’t really work well nor does a pivot table do this efficiently, as I still have to visually search row by row within the results of any pivot table.

    I am trying to compare two lists with each other that will give me the team value from the second list based on the client ID, period from and period to dates in the first list. I have attached a very small data sample that could represent the issue. My real data is actually quite huge.

    What I want to do is to first look up the client ID in the first list (Adj Teams list) and match the client ID, its “period from” AND period to” dates in the second list (YTD IR list). When all three criteria match, I need the result to give the corresponding Team value from the YTD IR list. In essence, it’s a matrix lookup, but I can’t figure out how to do it.

    I’ve heard and read about index match and combining or nesting those two functions together, but I simply don’t have a good grasp of either of those functions separately, never mind together, and I can’t seem to get anywhere on my own trying to figure out how to do this.

    Would appreciate help in understanding this and perhaps a direction to take in solving this problem I encounter every month.


    Re: VBA autofilldown formula

    Thank you, Luke. I realize I'm probably a scourge to sites like this, since I actually know very little of how to write VBA code, unlike most other posters here probably do. Unfortunately, I must rely on what I record and then try and decipher what it's doing after the fact. I don't have the money or the time for courses, so it puts me at a disadvantage, but I do appreciate your help on this one.


    This code bombs at the fill down command. Any help would be greatly appreciated.
    Only the portion of the code that is bombing is included, as the code commands work up to that point. I've also attached some of the data. As expected, the data is much, much larger than shown; hence, the desire for VBA filling down.

    The object of the code uses VLookup where it is to look up the rates in the range M1:N5 based off of the location types in column V. It then needs to fill down the rates appropriately, each row referencing its corresponding value in column V. Then it goes to column O8, extends the rates by multiplying the days by the rate it looked up (column M*N) and fills those extended amounts down the entire set of data. It always bombs at the first attempt to fill down the rates at N8. It will likely also bomb at the next filldown for the extension amounts. I don't know what is wrong and how it should be written. Any help would be greatly appreciated. Thank you.

    I have code that replaces some calculations and formatting based off of a search for certain text in the sheet’s first column. I can’t revise it so that it will loop through each sheet in the workbook. I would like the code to automatically loop through each sheet in the workbook and apply the code to each sheet and not just the one sheet that is named in the macro code as it currently is. Thank you for your assistance.

    This problem has me a bit stumped. I would like to subtotal the calculated elapsed time for each change in “new Shift” in the Shift column, but the subtotal must also include the “same” rows beneath the “new Shift” designation until it reaches the next “new Shift”. At that point, a new subtotal would be needed for the next “new Shift” and its subsequent “same” rows. In other words, each time a new shift starts, the calculated elapsed time needs to sum anew. New shift indicates a new shift has started. “Same” indicates it belongs to the “new shift” previously designated above it. This subtotal would have to iterate many more times down the spreadsheet than is shown in the given example data. This is only a subset of the data.

    If I try to use the Excel subtotal feature, what happens is that it subtotals on “new Shift” and subtotals on “Same” rows separately. I need it subtotal the first instance of “new Shift” and all the “Same” rows beneath it until it gets to the next “new Shift”.

    I also tried a pivot table but seem to run into similar problem.

    In the attached workbook, I give a sample of the raw data, and in a separate sheet a sample of what the desired results might look like.

    Any guidance would be greatly appreciated.

    Thank you.

    Re: Find phrase in one column & apply formula to different columns same row

    Thank you Jindon,

    You are an extremely helpful member! The code is now working on my workbook. Yay! The only thing I saw that was different in your most recently posted code and your original one that I copied into my VBA module was the "Option Explicit" line. I had to chuckle at the addition of your button (though useful) and bold fonting to the result. Now that is going the extra mile! Thank you ever so much.

    Re: Find phrase in one column & apply formula to different columns same row

    Dear Stanley,

    Thank you for reply post and all the work you did on this. It is greatly appreciated. It does work, but there a couple of glitches. One is that, for each report that I will be applying this to, the program revenues (divisor) may appear on a different row. I know in my previous post I said it was absolute, and it is within its own workbook sheet, except I failed to realize that a different report (workbook) might put program revenues somewhere else. Is there a way for the code to look up the word "program revenue" in column 1 and then lock that row down which would then become the divisor in the formula?

    Secondly, in my original post I had copied my data from my original report into a new workbook for loading as an attachment to my post; therefore, the workbook's sheet name defaulted to "Sheet1." The actual reports assign a specific sheet name to the report, so that is a glitch as well. I do believe I will be able to fix this particular snafu myself though. My apologies.

    Thank you ever so kindly for all your support again. You are awesome.

    Re: Find phrase in one column & apply formula to different columns same row

    I would like the macro to put the formula in as well because that is a significant part of the procedure that I want to repeat from workbook to workbook and not have to input it each time manually. However, per your request, I have reposted the workbook with the formuals in the pertinent cells. The formula calculates a percentage of costs to revenue, so the revenue row is absolute but its column is not.

    Thanks a million.