How to Copy From Multiple Sheets Based On 1 Sheets Input

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.
 
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("[COLOR="#FF0000"]B[/COLOR]")



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("[COLOR="#FF0000"]B[/COLOR]")    '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.
 
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.
 

Attachments

  • FinalEdit.xlsm
    67.4 KB · Views: 4
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:A1[B][COLOR=#ff0000]00[/COLOR][/B]").Value)
to match your change:
Code:
Set rngCrit = Sheets("Input").Range("C1:C[COLOR=#ff0000][B]100[/B][/COLOR]")    '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…~~
 

Attachments

  • ExcelGuru10414FinalEdit.xlsm
    69.4 KB · Views: 5
Last edited:
Well that did it! Thank you for your patience and all the help. Is there something I can do to commend you?!
 
Back
Top