Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 25

Thread: How to Copy From Multiple Sheets Based On 1 Sheets Input

  1. #11
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by Pahickham View Post
    1. If i manually clear the "section" sheets then run the filter it creates an error. Was just curious as to why that happens and if that can be edited to allow me to clear it by hand and it still run?
    The attachment in msg#9 has been updated to cater for that.
    If the Data sheet is empty, that too will cause an error - I haven't done anything about it though.




    Quote Originally Posted by Pahickham View Post
    2. Out of curiosity why's the range for "Input" sheet set for column C when the numbers are in A. It works, but I was just curious didn't see an offset or anything in there.
    Column C of the Input sheet is the criteria range for the advanced filter. The code puts things in that column. If you comment out the penultimate line of the blah macro (rngCrit.ClearContents) you'll be able to see what goes there.

  2. #12
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Yep that worked. One more question, if I wanted to have some nested formulas in the "Manually Input" columns (the formulas would be based on values within that row like D1-C1 and so on) is there a way it can permanently remain in the columns?

  3. #13
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Pahickham View Post
    if I wanted to have some nested formulas in the "Manually Input" columns (the formulas would be based on values within that row like D1-C1 and so on) is there a way it can permanently remain in the columns?
    I'm not sure; it depends on the formulae and what they refer to. Try it and tell me if it goes wrong.

  4. #14
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    It clears them every time the filter button is clicked. Do you know if there's a way to keep the formulae in designated cells?

  5. #15
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    I can't reproduce this here.
    Setup a workbook where this happens, but you'll have to set it up in the before-the-button-is-clicked state, such that the formulae are there, attach it here then I'll be able to click the button and see what happens to the formulae.

  6. #16
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    I've added the Formulae to all of the desired sheets

    ExcelGuru10414 Formulae(5).xlsm

  7. #17
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    The formulae disappear from the blank rows, but are kept for the existent rows. Those formulae are in the range of columns headered by 'Manually Inputted', their premise being that nothing was automatic about them; now they are!
    So there are several ways to approach this. The one I've plumped for is to reinstate the formulae each time, whether or not they were there before. This is quite easy because the same formulae are applied to the same columns in the Filtered Data sheet and all the Section n sheets. See attached.
    The little Sub which is called to do this is:
    Code:
    Sub ReInstateFormulae(myRng)
    myRng.Columns("K").FormulaR1C1 = "=IF(ISBLANK(RC[5]),"""",IF(RC[6]=""Y"",""E-SPARE"",""OOS""))"
    myRng.Columns("M").FormulaR1C1 = "=IF(ISBLANK(RC[-6]),"""",IF(RC[-6]=""V"",0,IF(RC[-6]=""W"",1,2))+(IF(RC[1]=1,0,0))+(IF(RC[1]=2,1,0))+(IF(RC[1]=3,2,0))+(IF(RC[2]=""Y"",0,1)+(IF(RC[3]=""Y"",1,0))))"
    myRng.Columns("Q").FormulaR1C1 = "=IF(OR(ISBLANK(RC[1]),ISBLANK(RC[-16])),"""",RC[1]-RC[-15])"
    myRng.Columns("R").FormulaR1C1 = "=IF(ISBLANK(RC[-13]),"""",TODAY())"
    myRng.Columns("S").FormulaR1C1 = "=IF(ISBLANK(RC[-18]),"""",""MECH"")"
    End Sub
    Should you wish to change the formula in one of the columns, you need to record a macro of you first selecting that cell, then putting the new formula in the topmost cell. (To do this you don't need to start with an empty cell; the new formula can already be there, you just have to go into the cell as if you were editing it, but just press Enter on the keyboard). When you do that you'll get a macro like:
    Code:
    Sub Macro3()
    '
    ' Macro3 Macro
    '
    
    '
        Range("K2").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(ISBLANK(RC[5]),"""",IF(RC[6]=""Y"",""E-SPARE"",""OOS""))"
        Range("K3").Select
    End Sub
    where you just take the bit in red and substitute it the appropriate line of the ReInstateFormulae sub.
    Attached Files Attached Files

  8. #18
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Thank you! I appreciate the time you've put in and the reasoning behind your work.

  9. #19
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    However, I do have one last question. I've edited the sheet and made it into its final format.
    We had previously discussed having a specific column, which at the time was column C and is now column B (Order Type), determine whether data would be deleted or remain on the last 5 sheets. I noticed that if i change one number in the "Order Type" column it will just continue to stack on the other sheets instead of delete the previous one that no longer exists. This might be due to the format change, but if you could help me with that I believe it will be my final question!


    FinalEdit.xlsm

  10. #20
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    You've really confused me with this question.
    I see you've changed the headers on the destination sheets so I doubt things are turning up where you want them to, this is solvable though, but this is leading to my confusion about which columns are which.
    Before you address your last question I expect you'll want to put things in the right columns right? (I'll help.)
    Last edited by p45cal; 2020-01-30 at 12:01 AM.

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •