How to Copy From Multiple Sheets Based On 1 Sheets Input

Pahickham

New member
Joined
Jun 5, 2017
Messages
25
Reaction score
0
Points
0
Excel Version(s)
2013
Sorry in advance for the confusing description, I couldn't think of and easier way to type this.

I'm in need of help regarding copying and pasting data from 2 different data sheets to one "filtered data" sheet based on the inputs of a fourth sheet called "Input"

Currently I have a query setup to the "data" sheet. Every time I download the most recent data entries from a server and save, it will actively change the Inputs on the "data" sheet. From there I go to the "Input" sheet and type the top priority numbers so it will filter the desired data from "data" and "data 2" sheets to the "filtered data" sheet.

Attached are a before and after example of what I'm looking form. Is this possible?

Thank you for any responses in advance.


View attachment Start.xlsx View attachment Finish.xlsx



Mr.Excel Link:
https://www.mrexcel.com/board/threa...tiple-sheets-based-on-1-sheets-input.1119689/
 
Last edited:
In the attached, enter your search criteria in any of the cells A2:A17 on the Input sheet and click the button.

It looks for the presence of the criteria you enter anywhere in the description column, so if you just enter wor you will get all the rows with working in the description. This behaviour can be tweaked if you want.
 

Attachments

  • ExcelGuru10414Finish.xlsm
    29.3 KB · Views: 10
Last edited:
Sorry for the late response.

Your attachment worked perfectly. However, I was hoping you could help me with the final edit. Unfortunately, some things needed to be changed.

The "Data" sheet is a dynamic sheet that info can be changed weekly.

The goal on this sheet is like before. Copy and paste from "Data" to "Filtered Data" based on the value in "Input"
The changes are there's no "Data 2" sheet now and if a value has been typed on "Filtered Data" in the manually inputted columns then I'd like for it to stay with that row each time the filter button is clicked, as long as that input number is still active, otherwise delete the entire row.

Also added 4 more sheets:
- the data in "Section 1" is based on the "Data" sheet anytime column B says 1 then copy and paste that data to "Section 1" it will also have manually inputted data that i would like to keep as long as that info is on the "Data" sheet after the next update.
- the data in "Section 2" is based on the "Data" sheet anytime column B says 2 then copy and paste that data to "Section 2" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.
- the data in "Section 3" is based on the "Data" sheet anytime column B says 3 then copy and paste that data to "Section 3" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.
- the data in "Section 4" is based on the "Data" sheet anytime column B says 4 then copy and paste that data to "Section 4" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.

Reference:
View attachment Start.xlsm View attachment Finish.xlsm
 
Copy and paste from "Data" to "Filtered Data" based on the value in "Input"
The changes are there's no "Data 2" sheet now and if a value has been typed on "Filtered Data" in the manually inputted columns then I'd like for it to stay with that row each time the filter button is clicked, as long as that input number is still active, otherwise delete the entire row.
What determines if it's still active; that it's on the Data sheet or that it's on the Input sheet?
 
Also added 4 more sheets:
- the data in "Section 1" is based on the "Data" sheet anytime column B says 1 then copy and paste that data to "Section 1" it will also have manually inputted data that i would like to keep as long as that info is on the "Data" sheet after the next update.
With these four sheets, is whether the info is on the Data sheet determined by a match in all columns A to I? (If you say yes to this, that row on the Section n sheet will be deleted if there's a difference in just one of those columns.)
 
The "Data" sheet is where all of the information comes from. The "Input" sheet only determines the "Filtered Data" sheet. The Section sheets are pulling from the "Data" sheet. For the Section sheets i need the full row to be copy and pasted over, it's column B that determines what gets transferred over. If column B of of "Data" = 1 copy and paste to "Section 1", if column B = 2 copy and paste to "Section 2", etc.

Not sure how you would approach this, but a thought of mine was for the first macro to copy and paste from the manually input columns to the "Data" sheet so that when the next macro copy and pastes the specified data over it contains whatever was typed by the user. Might be an easier way though.
 
The "Data" sheet is where all of the information comes from. The "Input" sheet only determines the "Filtered Data" sheet.
Understood.



The Section sheets are pulling from the "Data" sheet. For the Section sheets i need the full row to be copy and pasted over, it's column B that determines what gets transferred over. If column B of of "Data" = 1 copy and paste to "Section 1", if column B = 2 copy and paste to "Section 2", etc.
Yes, but you added the proviso: as long as that info is on the "Data" sheet, and I need to know what decides if that info (row) is on the Data sheet; is it a match on all columns A to I? (If you say yes to this, that row on the Section n sheet will be deleted if there's a difference in just one of those columns.)



Not sure how you would approach this, but a thought of mine was for the first macro to copy and paste from the manually input columns to the "Data" sheet so that when the next macro copy and pastes the specified data over it contains whatever was typed by the user. Might be an easier way though.
I'll sort that out, don't worry.
 
Understood, column E can be used to confirm whether it's still on the Data sheet. If the "Orders" match then keep it.
 
Attached.
 

Attachments

  • ExcelGuru10414.xlsm
    43.9 KB · Views: 14
Last edited:
Thank you that appeared to do the trick. Couple of quick questions:
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?
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.
 
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.




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.
 
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?
 
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.
 
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?
 
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.
 
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 = _
        [COLOR=#ff0000]"=IF(ISBLANK(RC[5]),"""",IF(RC[6]=""Y"",""E-SPARE"",""OOS""))"[/COLOR]
    Range("K3").Select
End Sub
where you just take the bit in red and substitute it the appropriate line of the ReInstateFormulae sub.
 

Attachments

  • ExcelGuru10414Formulae(6).xlsm
    46.9 KB · Views: 3
Thank you! I appreciate the time you've put in and the reasoning behind your work.
 
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!


View attachment FinalEdit.xlsm
 
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:
Back
Top