Copy Data From Multiple Sheets into Master Sheet if Cell Value >0

[VanillaBryce]

Good evening all!

I have a baseball card collection I have catalogued in about 70 sheets, each sheet named for a year 1950-2020. Each card has a row with data in columns A:H. Columns A:G are identifying information for the card (i.e., Year, Brand, Set, Subset, Number, Name, Style). Column H is "Quantity" and... has a numerical value representing the quantity of that particular card I have in my collection.

I want to create a new sheet called All Owned that lists every card I own. In other words, I need some kind of formula or macro that will automatically search column H in all 70 sheets, and if the value in H is greater than 0, then it will copy and paste all information in the same row A:H.

Ideally, I would like this All Owned sheet to automatically update as I update my collection in the sheets labelled 1950-2020. Thank you!

 

[kevin9999]

Unfortunately, I can't reproduce the error you're getting at my end. Using all the code I posted, if I start (as an example) with the following on the All Owned sheet (note 1 instance of 1950 in row 2)

Vanilla Bryce.xlsb
	A	B	C	D	E	F	G	H
1	Year	Brand	Set	Subset	Number	Name	Style	Quantity
2	1950	1950	1950	1950	1950	1950	1950	1
3	1951	1951	1951	1951	1951	1951	1951	1
4	1951	1951	1951	1951	1951	1951	1951	1
All Owned

Based on the following in my sheet2:

Vanilla Bryce.xlsb
	A	B	C	D	E	F	G	H
1	Year	Brand	Set	Subset	Number	Name	Style	Quantity
2	1950	1950	1950	1950	1950	1950	1950	1
3	1950	1950	1950	1950	1950	1950	1950	0
4
Sheet2

which, when I change it to this (add 1 to cell H3)

Vanilla Bryce.xlsb
	A	B	C	D	E	F	G	H
1	Year	Brand	Set	Subset	Number	Name	Style	Quantity
2	1950	1950	1950	1950	1950	1950	1950	1
3	1950	1950	1950	1950	1950	1950	1950	1
4
Sheet2

I automatically get the following in the All Owned sheet (note the additional 1950 added to row 3)

Vanilla Bryce.xlsb
	A	B	C	D	E	F	G	H
1	Year	Brand	Set	Subset	Number	Name	Style	Quantity
2	1950	1950	1950	1950	1950	1950	1950	1
3	1950	1950	1950	1950	1950	1950	1950	1
4	1951	1951	1951	1951	1951	1951	1951	1
5	1951	1951	1951	1951	1951	1951	1951	1
All Owned

Try this: remove the private sub worksheet_change code from a sheet; change a 0 to 1 in that sheet; then run the Update_Sheet_Change() sub routine and see if it makes the change to the All Owed sheet that you'd expect.

 

[VanillaBryce]

Yes your "Try this" suggestion worked perfectly.

 

[kevin9999]

Yes your "Try this" suggestion worked perfectly.

Then the problem lies in the worksheet change event code. Could you provide a screen shot of the entire VBA editor as a starting point?

 

[VanillaBryce]

Is this what you mean by "entire VBA editor"?

 

[VanillaBryce]

This...?

 

[kevin9999]

This...?

Nearly, I need to see the project explorer. From the menus at the top of the screen, select View then click on Project Explorer.

 

[VanillaBryce]

This?

 

[kevin9999]

This?

Thank you. I don't see the module containing the Add_New_Year() or Update_Sheet_Change() sub routines (I'd expect to see them below the ThisWorkbook icon. Where do you have them? Also, I don't use KutoolsforExcel at all. I'm wondering if that could be causing an issue?

 

[VanillaBryce]

I'm not sure what you mean by "Where do you have them? This is the only spot I know to view the modules- Alt F8.

 

[kevin9999]

I'm not sure what you mean by "Where do you have them? This is the only spot I know to view the modules- Alt F8.

I see, you have them in the sheet55 code area. If you double click on Sheet55 (All Owned) in the Project Explorer in the VBA Editor, you'll see them. They really should be in a standard module (not that that will solve the issue, but is is standard practice). In the VBA Editor menu at the top of the screen select Insert, Module. Move the 2 subroutines there for a start.