macro

Please provide a sample workbook.

Here you go. Thanks for your comment.

Please let me know if you would like to have more details.

SANTABABY please let me know if you need additional details.

@pauledwardian

Santababy requested that you upload a workbook.  You posted a screenshot.

Please post a workbook.

Here you go. Please look at the Sheet1 and Sheet6 for my explanations.

Sample.xlsx

Paul

This should come close.

Option Explicit

Public Sub CostAnomaly()
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngCell As Range
    Set rngSrc = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("AE2"), Worksheets("Sheet1").Range("AE2").End(xlDown))
    Set rngTgt = Worksheets("Sheet6").Range("A:A").Find("Sample1")
    If rngTgt Is Nothing Then
        Stop
    Else
        Set rngTgt = rngTgt.Offset(1)
    End If
    Application.ScreenUpdating = False
    For Each rngCell In rngSrc
        If rngCell.Value > rngCell.Offset(0, 1).Value Then
            rngCell.EntireRow.Copy rngTgt
            Set rngTgt = rngTgt.Offset(1)
        End If
    Next
    rngTgt.Value = "Sample2"
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Thank you so much aikimark. It worked with my sample I provided. Would you please look at this new workbook. The code did not work with this workbook. Maybe because of the spaces that are on Sheet1 between the lines of data. Please advice. I marked the spaces with green. Main-Sample.xls

@pauledwardian

Thank you for the repost.  When the EE experts ask you to upload a file, they want to see a representative example of the data.  Please remember this for your next question.

aikimark,

I apologize for this inconvenience. I am fairly new to EE. I will remembered this for my next question. :)
Would you please assist on this?

Paul

@pauledwardian

Please explain the following:

1. What is the significance of the "Sample1" placement in Sheet6?

2. In you problem description, you want "Sample2" to be placed immediately after the selected rows have been copied.  Now that I see the data, it looks like you want each section 'header' to be used.  Please describe this requirement in more detail.

3. Is there a reason why there are space characters in the column A cells that aren't section headers?

Sure.

1. What is the significance of the "Sample1" placement in Sheet6?
Basically I will need to copy couple if information from Sheet1 into sheet6. So, I asked in my other requests to have a code that would copy a couple of rows in sheet1 which are sample1 and sample3 rows and then insert them to sheet6.
So, in this question I asked for a code that would do a different operation on sheet1 and compare two columns and then paste the appropriate rows on sheet6 under sample1.
I just brought up sample1 as an example so it would be like a title for that specific operation that is being done on the AE and AF columns.
Also, if you noticed in my other question in (https://www.experts-exchange.com/questions/27432225/vba.html) that I will need the code to copy two different sections of sample1 & sample3 in sheet1 and insert them under sample1 and then inserts a new row called sample4. So, here I am trying to have a code that would paste the information under a row called sample1(which technically should be sample4) .

2. In you problem description, you want "Sample2" to be placed immediately after the selected rows have been copied.  Now that I see the data, it looks like you want each section 'header' to be used.  Please describe this requirement in more detail.
Again I apologize for this misunderstanding. I asked for this requirement because I posted another question in (https://www.experts-exchange.com/questions/27432283/vba-macro.html)
that I need a code that would compare the dates and paste the Past Current date into sheet6.
So, sample2 would be a title for the next set of data that will be copied according to my other question regarding the dates.

3. Is there a reason why there are space characters in the column A cells that aren't section headers?

This report gets generated from an ERP system which was designed with crystal reports.
So, here in the report there will always be 2 rows of space after the last row of data and the next title. So, for instance there will be 2 spaces in after the last row of sample1 and sample2 title.

Paul

The reason I divided my questions into multiple posts was that I found it rude to other experts and inappropriate to submit a long question in a single post.
Because I know most of the experts are doing this only to help out others and it is totally voluntarily.

Thanks aikimark for promping for the output. Is there anyway we can have this code combined with the previous code so it finds the sample1 on sheet6 and inserts sample2 at the end when it is done executing the code? So, it wouldn't ask for output? But, again I appreciate you did that extra option to help me out with it :)

That goes to my previous question...How does the "Sample1" get placed into sheet6?

Did you look at the output in sheet6?  It copies whatever headers it finds in Sheet1.

Sample1 with always be on sheet6 as default. But, each time in a different location in column A.
And that is great that the code had all the titles in sheet1 copied in sheet6 and I really appreciate you did that.
But, it would be still better if it looks for sample1 in sheet6 and copies those information below sample1 and also generates a row called sample2 below the last row of data that was copied from sheet1.


Paul

@Paul

Do you place the "Sample1" text into Sheet6?

Yes sir

Then what difference does it make whether you type "Sample1" or you click on the cell?

What happens if your first header in Sheet1 is something other than Sample1?

My code copies the headers and rows no matter what the headers are.

aikimark,

Because this would be only a single part of the code and it will combine with another macro. The other macro will generate sample1 where-ever it finished copying the rows. Lets say it finished copying the data at row 34 and then it will insert a row called "sample1".
So, im trying to have your code to be continued after sample1. Basically I'm trying to group them. So, sample1 would only separate the previous information with the ones that your code will define.
This is my other post that I asked an expert to help me out for a macro. His code will generate a row called sample1 after the last row of data. And then your code can execute and take care of the two columns of AE and AF and execute their rows under sample1.
If you look at my other post at ( https://www.experts-exchange.com/questions/27432225/vba.html ) which you actually added a comment ; I have asked the experts to provide me a code that would copy the information for specific rows. So, in your code if it adds a row called "sample2" then the other code will continue after "sample2" and have the same regulation. Basically Sample1, 2, 3, etc is a way for me to group each execution and separate each part.
I hope this makes sense.

Thanks again for your time,
Paul

@Paul

You lost me.  First, you had a problem with blank cell behavior.  Then, after seeing the workbook, you have code that processes all the sections.  Now we're back to processing only one section?

I think your quest to keep your questions simple has backfired.

Since your multiple questions exist in some context that I don't understand, I'm going to walk away from this thread.  Maybe one of the other experts will be able to run with one of the versions of my code or create their own and give you the solution you seek.  I'm off to bed.

Ok thanks for your help anyways

Thanks to aikimark.