Macro to create new worksheet if condition is met

[djmurray]

Hi Guys,

Im New to MrExcel but look forward to learning and helping from all the gurus!

I'm currently working on a large excel doc that I need to filter out information.

I need to run a macro where by if any of the columns contains "Checked" it moves the entire row to a new worksheet.

Thanks!

 

[circledchicken]

Hi and welcome to the forum,

Can you provide a few more details, and maybe a small sample of data:

• What is the range of data - where does it start and end?
• Is this a one time exercise or will you need to do this repeatedly and therefore the last row/column needs to be obtained dynamically?
• Can 'Checked' appear on any of the columns or is it within a particular column?

 

[djmurray]

Hi and welcome to the forum,

Can you provide a few more details, and maybe a small sample of data:

• What is the range of data - where does it start and end?
• Is this a one time exercise or will you need to do this repeatedly and therefore the last row/column needs to be obtained dynamically?
• Can 'Checked' appear on any of the columns or is it within a particular column?

Hi Circledchickne, thanks for your reply.

The start and end depends mostly on how many users are signed each week.

The data will be copied and pasted into my sheet, i will execute the script and save the sheet for the week.

Checked will only appear in a specific column. An example is below

____________________________________________________________________________________________________

Username:	Is active	Email Address	Signup for Newsletter	Contact
MrExcel	Yes	test@test.com	Checked	1234567

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[circledchicken]

Perhaps try this something like this:

Sub example()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
    
    Set ws1 = Worksheets("Sheet1") ' data sheet
    Set ws2 = Worksheets("Sheet2") ' destination sheet
    With ws1
        .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            ' filter for 'Checked' in column D
            .AutoFilter _
                Field:=4, Criteria1:="Checked", _
                Operator:=xlFilterValues
            On Error Resume Next
                ' copy filtered rows to ws2 and delete from ws1
                With .Offset(1, 0).Resize(.Rows.Count - 1) _
                    .SpecialCells(xlCellTypeVisible)
                        .Copy _
                            ws2.Cells(ws2.Rows.Count, "A") _
                                     .End(xlUp).Offset(1, 0)
                        .Delete xlShiftUp
                End With
            On Error GoTo 0
        End With
        .AutoFilterMode = False
    End With


End Sub

• This assumes you're sheets are called Sheet1 and Sheet2 respectively
• The data is assumed to be continuous, with the column headers in Sheet1 going from A1:E1
• Sheet2 is also assumed to have the same headers from A1:E1
• Change the sheet names/ranges as appropriate
• To learn more about working with AutoFilter in VBA see:
  • VBA AutoFilters. How to use AutoFilters in Excel VBA Macros
  • Excel AutoFilter With Criteria in a Range | Contextures Blog

 

[djmurray]

Perhaps try this something like this:

Sub example()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
    
    Set ws1 = Worksheets("Sheet1") ' data sheet
    Set ws2 = Worksheets("Sheet2") ' destination sheet
    With ws1
        .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            ' filter for 'Checked' in column D
            .AutoFilter _
                Field:=4, Criteria1:="Checked", _
                Operator:=xlFilterValues
            On Error Resume Next
                ' copy filtered rows to ws2 and delete from ws1
                With .Offset(1, 0).Resize(.Rows.Count - 1) _
                    .SpecialCells(xlCellTypeVisible)
                        .Copy _
                            ws2.Cells(ws2.Rows.Count, "A") _
                                     .End(xlUp).Offset(1, 0)
                        .Delete xlShiftUp
                End With
            On Error GoTo 0
        End With
        .AutoFilterMode = False
    End With


End Sub

• This assumes you're sheets are called Sheet1 and Sheet2 respectively
• The data is assumed to be continuous, with the column headers in Sheet1 going from A1:E1
• Sheet2 is also assumed to have the same headers from A1:E1
• Change the sheet names/ranges as appropriate
• To learn more about working with AutoFilter in VBA see:
  • VBA AutoFilters. How to use AutoFilters in Excel VBA Macros
  • Excel AutoFilter With Criteria in a Range | Contextures Blog

Thanks very much circledchicken , I have made the changes to suit my sheet and all is working very well. Thank you very much for your help!

 

[circledchicken]

You're welcome! - thanks for the feedback.

 

[Artist701]

I'm glad I found this post.. My situation is similar in meeting a condition.

so i have two excel sheets... (Horse Race based on points received)

• 1 sheet shows names, id of the person, their horse name, date they raced and Points they received for that race.
• sheet 2 will need to generate a new row of a person that participated as well as points they received that date and the total points.
(BUT only if they participated, or else row should Not generate at all)

Now sheet 2 will display all the racers and their dates they raced and points they received those days..
These should be stacked on top of each other, racer after racer displaying their results.

Example of what I want is here:
http://www.westcoastbarrelracing.com/Points_8-30.pdf

I'm hoping sheet 1 could be the data sheet where I just punch in the number of points they received and date they participated.

i hope this makes sense.

Thanx a mil.