Copy row to new sheet based on a cell value

March 9, 2011 at 05:54:02
Specs: Windows XP
Hi All,

I am amazed at the willingness of people to help out and I would be so grateful if someone could point me in the right direction. I am keeping my description deliberately generic as it may help others apply this solution to their own (there are many answer out there, but many are too specific to a particular data set for me to adapt them myself)...

I have data with columns A to Q and in rows 1-50. (Row 1 is headers and column A is a label).

I have several worksheets, called WorkSheetX (X = 1, 2, 3 etc.,)

Based on the numeric value (in my case a date - specified as a numeric not a date format) in a specific column I would like to copy the corresponding row (which is a mix of text and numeric) into a new worksheet called "Summary 201X" (where X = 1, 2, 3 etc.,). For simplicity let's assume these work sheets exist and I have already copied Row 1 the header column to them

Here's an abbreviated example of the data in worksheet 1, 2, 3 etc.,:
A B C D .... Q
WorkSheetX Row1 2012 12 txt 4 x
WorkSheetX Row2 2011 12 txt 4 x
WorkSheetX Row3 2011 12 txt 4 x
WorkSheetX Row4 2015 12 txt 4 x

In Worksheet 1, if column A = 2011 that row will be copied to "Summary 2011":
A B C D .... Q
WorkSheet1 Row2 2011 12 txt 4 x
WorkSheet1 Row3 2011 12 txt 4 x

In Worksheet 1, if column A = 2012 that row will be copied to "Summary 2012" will this output:
A B C D .... Q
WorkSheetX Row1 2012 12 txt 4 x

and so on...
Next come the conditions for the other WorkSheets...

In Worksheet 2, if column A = 2011 that row will be copied to "Summary 2011" with a total ouput including WorkSheet 1 of:
A B C D .... Q
WorkSheet1 Row2 2011 12 txt 4 x
WorkSheet1 Row3 2011 12 txt 4 x
WorkSheet2 Row2 2011 12 txt 4 x
WorkSheet2 Row3 2011 12 txt 4 x

In Worksheet 2, if column A = 2012 that row will be copied to "Summary 2012" with a total ouput including WorkSheet 1 of:
A B C D .... Q
WorkSheet 1 Row1 2012 12 txt 4 x
WorkSheet 2 Row1 2012 12 txt 4 x

and so on...

I hope that describes everything suffciently and thank you in advance,

Steve


See More: Copy row to new sheet based on a cell value

Report •


#1
March 9, 2011 at 06:18:20
After much searching I was able to adjust the following macro to select and copy the entire rwo from sheet A to sheet B based on a certain cell value. Next I managed to do this for each of the discrete criteria i.e. date = 2011, 2012 and copy to corresponding sheets.

Next step is to do this automatically for all work sheets, so all of the data is present in each of the date tabs.

    Sub SearchForString()
Application.ScreenUpdating = False
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer

        On Error GoTo Err_Execute

        'Start search in row x        
LSearchRow = x

        'Start copying data to row y in Sheet2 (row counter variable)
        LCopyToRow = y

        While Len(Range("A" & CStr(LSearchRow)).Value) > 0

            'If value in column A = "xxxx", copy entire row to Sheet2
            If Range("A" & CStr(LSearchRow)).Value = "xxxx" Then

                'Select row in Sheet1 to copy
                Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
                Selection.Copy

                'Paste row into Sheet2 in next row
                Sheets("Sheet2").Select
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                ActiveSheet.Paste

                'Move counter to next row
                LCopyToRow = LCopyToRow + 1

                'Go back to Sheet1 to continue searching
                Sheets("Sheet1").Select

            End If

            LSearchRow = LSearchRow + 1

        Wend

        'Position on cell A3
        Application.CutCopyMode = False
        Range("A3").Select

        MsgBox "All matching data has been copied."

        Exit Sub

    Err_Execute:
        MsgBox "An error occurred."

    End Sub


Report •
Related Solutions


Ask Question