Page 3 of 3 FirstFirst 1 2 3
Results 21 to 25 of 25

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

  1. #21
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013


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

    No It all works the way it did before. I made the edits because now this is the desired/required format. The only thing I need your help fixing is when I change column B on the "Data" sheet by one number (which was shown on the most recent document upload) it doesn't replace the old row with the new row. Tough the explain. When you look at the "2B Board Items" sheet you can see that they are all the same except the Order type column is off by one number, this is because i manually changed the number on the "Data" sheet then hit filter. I would appreciate it if you could get it to only keep the most current rendition of the row. So instead "2B Board Items" should only have one row, being the most recent edit.

  2. #22
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    The 2B Board Items sheet and the 4 Complex n sheets are treated quite differently.
    The Complex n sheets I think is quite easy to determine what's removed:
    In the RemoveRowsFromComplexSheets macro:
    change:
    Code:
    Set rngDataSht = Sheets("Data").Range("A1").CurrentRegion.Resize(, 9).Columns("E")
    to:
    Code:
    Set rngDataSht = Sheets("Data").Range("A1").CurrentRegion.Resize(, 9).Columns("B")


    and change:
    Code:
    Set rngSectionSht = sht.Range("A1").CurrentRegion.Resize(, 9).Columns("E")    'change this??
    to:
    Code:
    Set rngSectionSht = sht.Range("A1").CurrentRegion.Resize(, 9).Columns("B")    'change this??


    As far as the 2B Board Items sheet is concerned it's only the Input sheet data in column A in conjunction with the 2B Board Items sheet's column H that determines whether a row on that sheet stays or not.
    To change this behaviour will need a fair bit of code to be written/re-written in the RemoveRowsFrom2BBoardItems macro, so choose once and for all, all the factors that affect whether a row will be removed from that sheet (Input sheet, Data sheet columns, 2B Board Items columns) because I'm not going to be keen to revisit the code later, then tell me. Clearly.

  3. #23
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Thank you for your patience. The suggested edits above worked as you know. As for "2B Board Items" yes the original idea stays the same as to where it is determined by the value in the "Input" sheet in relation to the "Data" sheet. Unfortunately I mustn't have mentioned that I need the data cleared that no longer corresponds with column B of "2B Board Items" and column B of "Data", same as what's been done for the "Complex" sheets.
    Attached Files Attached Files

  4. #24
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    See attached. For changes, see macro RemoveRowsFrom2BBoardItems between comments:
    '''***********************Start of added code:
    and
    ''**********End of added code


    Also (I haven't done this):
    Code:
    InputVals = Application.Transpose(Sheets("Input").Range("A2:A17").Value)
    will probably need changing to:
    Code:
    InputVals = Application.Transpose(Sheets("Input").Range("A2:A100").Value)
    to match your change:
    Code:
    Set rngCrit = Sheets("Input").Range("C1:C100")    'you can extend or reduce the input range here.


    ps. You could simplify the code in several places now that your FinalEdit file is showing a real Excel Table in the Data sheet called (confusingly) Sheet1:
    This:
    Code:
    Set rngDataSht = Sheets("Data").Range("A1").CurrentRegion.Resize(, 9).Columns("B")
    Set rngDataSht = Intersect(rngDataSht, rngDataSht.Offset(1))
    DataShtVals = rngDataSht.Value
    can be reduced to one line:
    Code:
    DataShtVals=Range("Sheet1[Order]").value
    and this:
    Code:
    Sheets("Data").Range("A1").CurrentRegion.Resize(, 9).AdvancedFilter…~~
    condenses to:
    Code:
    Range("Sheet1[#All]").AdvancedFilter…~~
    Attached Files Attached Files
    Last edited by p45cal; 2020-01-31 at 03:15 PM.

  5. #25
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Well that did it! Thank you for your patience and all the help. Is there something I can do to commend you?!

Page 3 of 3 FirstFirst 1 2 3

Posting Permissions

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