Macro to filter and copy data from one sheet to another sheet

I need vba to filter data and Copy in a new sheet

I have a excel sheet with 2 sheets (Data) and (Results).

First
In Sheet (Data), I want to apply filter in cell G1 wth Criteria is Begins with “Resolved” and “Sent” and want to copy visible records from A to E and paste in Sheet (Results) from cell A1.
Also assign new serial number starting from 1 from cell A2 to till the end of last record.

If no records, then show message box “No record found” and exit macro. If record found then do above task and move to Second

Second
In Sheet (Data), I want to apply filter in cell G1 with Criteria is Begins with “Resolved” and “Sent” and in cell F1 “Yes” and want to copy visible records from A to E and paste in Sheet (Results) 4 lines after the above pasted records.

Also assign new serial number starting from 1 for newly pasted records in column A.

If no records, then show message box “No second record found” and execute only First macro and exit. If record found then do above task and move to Third


Third

I want to add one sentence in excel sheet (Results) between both first and second i.e. “Below records are YES Records and Below records are excludes no Records”

If there is no record found as per Second filter then ignore Third

Please refer attachment for example.

Request you to provide code

hey thanks.

But shows error in line

.AutoFill .Resize(rowCount, 1), xlFillSeries

while running the code in attached data

can you please check?

Hey again it shows error in line

.AutoFill .Resize(rowCount, 1), xlFillSeries

while running the code in attached data

can you please check?

Hey in Second part heading is missing

also when iam running the macro while i am on sheet Results it shows "No Records". it works only when iam on sheet data

Hey sorry, it works perfectly.

Can you please help me with one more code in continuation.

i need code to give border line to the copied data in sheet "results" as per attachment

Dear Mumps1,
Thank you very much for your code. This is what Exactly I wanted.

I need one last help from you. Trust me I will not ask anything more.

Your code is perfect is per my requirement.

Can you please modify the code for below situation.

In case the set of first range and second range is same to same (as given attachment Sheet “Results”,
Then paste only the first range (ignore second range). Also Add text 2 rows after the last record “ NO OTHER RANGES ARE AVAILABLE”

Optional:
Also If have any idea about the macro integration with Outlook, please help me with code to paste both the ranges in outlook email body (not as attachment).
Please help. This will be my last requirement

Hey,
I think there is some problem with the code.

it paste only the first range

attached sample file for your reference.

Please help

Sorry,

Refer this attachments. Earlier attached wrong file

Dear Mumps1,

I am still not able to figure out the problem in the code.

Please refer attachment.

In My sheet there is one "yes" in column F. But while running the code it shows "No Yes Records Found"

can please correct the data. I have attached Example file.

Dear Mumps1,
I am extremely sorry to bother you.

The above issue has been resolved. But one more problem has been raised.

please find attachment. I have mentioned earlier that If both the ranges are same then there should be only one range in the sheet Results.

Please refer attachment. I have given example.

yes somehow it works. Along with message box "No 'Yes' records found or both ranges are the same."

I also wanted to insert the same line ("No 'Yes' records found or both ranges are the same.") above the data range. Please insert 3 rows above the data and insert this sentence in cell B2.

Also let me know if I want to apply first filter in column H instead G which line of code to be changed and how?

hey got it. wants border line only within a table. in the above code it gives border line to empty cell also above first range.

Inserting 3 Rows above is perfect.

After inserting 3 above rows insert sentence "No 'Yes' records found or both ranges are the same." in b2 .

Please note need border line only in table

Dear Mumps1,
I am extremely sorry to bother you. It might irritate you.

i have mentioned by requirement in attachment in details.

There are possibility of 3 scenarios.

I and I have given examples for data format for 3 scenarios. Please change your code accordingly.

Please note the heading text and cell number of Heading etc. Dont change the position. This is going to be final format

Please Please Please Please Please

You Boss!!! Genius!!! Wonderful!!!!

Thank you so much friend.

Thanks a ton. It works perfectly.

Thank you very much for your help.

I really appreciate your tech skill and patience.

Genius You are ... Great Mr.Mumps!

Hi Mumbps,

I am facing some problem again.

I am getting error on this line while running code in attachment.

.AutoFill .Resize(LastRow2 - 4, 1), xlFillSeries

Please refer attachment as well.

hi Mumps,

Connecting after a long time.

I am facing a small problem in a code.

I have added one more filter criteria as below
Sheets("Data").Range("A1:I" & LastRow1).AutoFilter Field:=6, Criteria1:="=India"


and changed the column numbers accordingly. But it show no records. below is my new code.

Can you please help me on this?

Formula:

Please Login or Register  to view this content.

Hi,

Thank you for your revert

I am getting an error on below line

.AutoFill .Resize(LastRow2 - 4, 1), xlFillSeries


If there is no records in the name of India, then should show msg box stating no records found.

How to do that? can u please help me?

Hi,
I am getting an error on below line.

Sheets("Data").Range("A1:E" & LastRow1).SpecialCells(xlCellTypeVisible).Copy Sheets("Results").Cells(4, 1)


Pls help

Hi Mumps,

Yes identified that later. Thanks for your help.

I just need one last help from you.

In attached file in Sheet "Results" Records under "Below records are 'YES' Records excluding 'NO' Records", Border lines are not coming for all records.

Border line is appearing for only heading and first record.

Can you let me know how to apply border line for all 5 records.

Also number of records might change case to case as per the changes in data.

So border line should apply dynamically in visible records.

Please help buddy

No Worries sir