need a macro or formula for consolidation of data

[rockymp]

i have a table updated on every month by user

see attcahed for reference.
Column A and B remains the same in the template and the
user updates only the range from column C to column G on monthly basis

before updating, user copies the last month data and pastes to adjacent columns from column J
(example if feb month data is processed, Jan month data is taken a copy and pasted in column J and this is a continuous process on monthly basis.

I need a macro to see a consolidated data based on history dates ..refer output sample sheet in attached xls.

[PCI]

Does sheet "output sample" already exists ?
In others words, the macro just need to copy new data from sheet "input" and past it in sheet "output sample" and filter it ?
Note : in sheet "output sample" there is only Apple you need others fruits , or how do we filter the data ,

[PCI]

Here a first draft

Please Login or Register  to view this content.

[rockymp]

your code shows a run time error at
Set Wkrg = Range(.Cells(Rows.Count, LC).End(3)(2), .Cells(Rows.Count, LC - 1).End(3).Offset(0, 1))


in output sample i mentioned only "Apples" with consolidated history data with month name - "H" column

same way i need macro for all the product consolidation with month ... please help

[rockymp]

note J2 to BQ5 range has only apple product history dates.. it is consolidated in output sample. same way .. all the product has to be consolidated with month name

[PCI]

See test file attached, try and comment
I have the feeling that sheets' names are not the same..!

[rockymp]

is any other way to do it?

[PCI]

is any other way to do it?

What dio mean ??
Is it not working properly ?

[rockymp]

your code shows a run time error at
Set Wkrg = Range(.Cells(Rows.Count, LC).End(3)(2), .Cells(Rows.Count, LC - 1).End(3).Offset(0, 1))


in output sample i mentioned only "Apples" with consolidated history data with month name - "H" column

note J2 to BQ5 range has only apple product history dates.. it is consolidated in output sample. same way .. all the product has to be consolidated with month name

[PCI]

your code shows a run time error at
Set Wkrg = Range(.Cells(Rows.Count, LC).End(3)(2), .Cells(Rows.Count, LC - 1).End(3).Offset(0, 1))

Not in the file sent ...is not it ? rockymp_sample data.xlsm

[PCI]

note J2 to BQ5 range has only apple product history dates.. it is consolidated in output sample. same way .. all the product has to be consolidated with month name

All products are present in the file sent, is it ?

[rockymp]

Hi Your code picks only feb 2020 consolidation... is either way to consolidate all months data.. i mean feb 2020 till jun 2019.. )data in range till BQ 53 in input?

[PCI]

Hi Your code picks only feb 2020 consolidation... is either way to consolidate all months data.. i mean feb 2020 till jun 2019.. )data in range till BQ 53 in input?

So it is not the same data and you did not answer my first question.
What is the data to use ?? Today data from columns A to G is used and the date added is the date of today ...!

It seems you want to rebuilt the complete data set each month ...? It means sheet "output sample" has to be cleaned up each time !!!
You could just add the new data from column A to G

[PCI]

Folowing my last questions and comments here another possiblity

Please Login or Register  to view this content.

[rockymp]

This is what i needed. thanks a Ton.. you are a Champ.

[PCI]

You're welcome, it tooks time to understand the specifications ...!