Posts by Billy_Kisuke


    It would seem from your private message ... that you do keep on modifying your worksheets' structures ...

    Please attach the very last version 5 to your next message ...

    Hi Carim,

    Thanks for your response.

    Regret for inconvenience caused.

    I have uploaded V5 for your ready reference, requesting you to provide the solution.

    Thanks in advance again for your time & support.

    - Billy

    Hi Carim,

    Thanks for your response. Please go through below clarifications:

    1. I am not getting this message while running the macro.

    2. Yes I understood 255 quantifiable data is the upper limit set in Excel. But this additional data is not hampering my assessment except for the pop up window appearing very frequently during selection of options available in slicers.

    So I was wondering is there a macro to suppress this window on real time whenever it pops up.

    - Billy

    Hi Carim,

    This code doesn't help as after the slicers are reset & whenever I am selecting required option from slicers, I get the pop up window "The maximum number of data series per chart is 255". Is there an option to disable to this pop up as soon as it occurs? Or is that there is no solution for this?

    - Billy

    Hello Excel Masters,

    I am using Excel dashboard features with slicers for graphical assessment.

    Whenever the slicers are reset to default, a pop up alert going by "The maximum number of data series per chart is 255" is appearing numerous times.

    I know since there is huge data but I want this pop up to be suppressed by selecting as 'OK' using VBA. Thus I hereby sincerely request to help in providing the code.

    My current working spreadsheet is named as 'Evaluation'.

    Thanks for your time & support in advance.

    - Billy.

    Hi Carim,

    I cracked the code to modify as per my requirements.

    Thank you again.

    - Billy

    Thank you, thanks a lot for your support.

    Its embarrassing for me to ask you so many queries. Since I am a noob in Macro, I am not able to crack the code efficiently.

    The last code going by:

    col = 1
    Set rng = Sheet2.Range(Cells(2, 1), Cells(last2, 1))
    For Each c In rng
    i = c.Row
    c = Evaluate("=INDEX(Actual!" & Col_Letter(col + 8) & "1:" & Col_Letter(col + 8) & last1 & ",MATCH(J" & i & "&K" & i & ",Actual!B1:B" & last1 & "&Actual!C1:C" & last1 & ",0))")
    c = IIf(IsError(c), "", c)
    Next c

    If I need to change the column number then I tried col = 2 but I am not able to fetch the results in Column 2 instead the results are appearing in Column 1 only. I am really sorry to pester you, this shall be the last query as all of my requirements has been cracked by you.

    Once again I thank you very much from bottom of my heart for helping me out.

    - Billy

    Glad to hear the macro does what you were expecting :wink:

    Thanks for .. both your Thanks ..AND for the Like :smile:

    Hi Carim,

    I am sorry to bug you over & over.

    I have one last query, what will be the change in the code if I am changing the spreadsheets names i.e. Sheet1 as Actual, Sheet2 as PT, Sheet3 as Planned.

    As the VBA is not functioning if I am changing the spreadsheets names.

    Thanks for your kind support again.

    - Billy

    Yes Carim, the test file is meeting all the requirements defined & indeed is delivering desired results.

    But I failed it mapping to actual requirements as defined above.

    Its my sincere request for you to help me.

    Again, thanks for your response & support in advance.

    - Billy

    Hi Carim,

    I tried to map the code to my actual requirements but the code is not working to my bad luck.

    I am still a rookie to VBA & thus I failed miserably.

    I have attached updated file with actual requirements (Columns have been moved in both Sheet1 & Sheet2 as per my requirements). I once again kindly request you to provide the solution.

    Further in Sheet2 in Column A the operation in identical but limited to Column A only along with above part.

    Thanks for your kind support.

    - Billy

    Hello Excel Masters,

    Firstly I apologize if I am asking too many requirements in this thread for a single operation.

    Please find attached document having the requirements indicated below:
    1) There are two spreadsheets, Sheet1 having inputs while Sheet2 having outputs.
    2) Sheet2 has Index match multiple criteria with excel formula until the last row of the reference column.
    3) Spreadsheet name & columns are dynamic in nature.

    The number of rows are exceeding more than 20,000+, hence it is becoming cumbersome to use excel formula leading to high file size and as well as Excel document is hanging by consuming too much of time.

    Henceforth it's my request to help me in performing this operation via VBA. I hope it is feasible.

    Thanks for your time & support in advance.

    - Billy

    A couple more possibilities.

    Sub Maybe_A()
    Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column)).Select
    End Sub

    Sub Maybe_B()
    Cells(Selection.Row, Selection.Column).Resize(, Cells(Selection.Row, Columns.Count).End(xlToLeft).Column - Selection.Column + 1).Select
    End Sub

    But you mentioned that the selection should be copied for further processing.
    Where do you want it to end up (pasted)?
    You should avoid selecting if not absolutely required and it sounds like you don't need to select here. As long as we know where to transfer that range to.

    Thanks for your reply again.

    The complete operation requires to select the cell as per above requirement, offset the selected range to one row below, copy this newly selected range & paste it another spreadsheet.

    - Billy

    Dear All,

    I am looking for VBA code which will select entire range in a row from active cell until last non empty blank cell.

    To elaborate further, in first row the range of cell begins from D1 to AA1 (there are blank cells in this range). At any moment if I run the VBA code, from active cell until AA1, the selection should be copied for further process.

    It's my sincere request to help me in providing the VBA code.

    Thanks for your time & support in advance.

    - Billy

    I have attached your sample file with new code. This code will work for any number of "BO FY" sheets and any number of rows and columns on the "BO FY" sheets.

    Click the button on the "BO FY" sheet.

    Code assigned to the button is

    Thanks a lot!!!!!!!!!!!!!!!!!!

    It is absolutely working fine.

    - Billy

    Hi KjBox,

    Once again I am sorry to post these missing requirements:
    1) The data in the rows is dynamic in nature in each of the spreadsheet.
    2) Similarly the number of rows will get added in the very near future.
    3) In the array, the number of spreadsheets will also get added.

    I really don't know whether I am asking too much from you but this is my problem (I am looking for solution from past one week).

    I sincerely request you to help me.

    P.S - I succeeded in attaching the file.

    - Billy