Copy rows based on multiple IFs

**aldenes** · 01-12-2020, 02:22 PM

I need help with the following problem. I am not sure how the paid subscription works, but I have $50 points credit to pay for a specialist's help.
I am looking to build a sub-routine as part of an automatic scheduling for a medical office. Take a look at the attached workbook.
Sheet1 comes with pre-populated data; the table structure is identical, the table header in row 1 is anonymized. Please keep source table structure the same.
The VBA needs to analyze data in sheet1 row by row starting with row 2 and skip rows that are hidden. In needs to do that in a loop for a specific amount of times as specified in Sheet2 B4 variable that is entered by the user.

The following needs to occur in analyzing Sheet1:
For any unique last name (in D), IF L column contains code D7140 OR D7210 AND IF Column M is between 1 to 8, OR between 9 to 16, or between 17 to 24 or between 25 to 32, THEN COPY row only once to sheet 2 as done manually (shown in red)

Repeat this loop as many times until the number of rows specified in sheet 2 B4 (variable entered by user) has been copied to Sheet2

**mrice** · 01-12-2020, 02:46 PM

Try this

Sub Test()
Dim N As Long

For N = 3 To Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
    If Sheets("Sheet1").Rows.RowHeight > 0 Then
        If Sheets("Sheet1").Cells(N, 12) = "D7140" Or Sheets("Sheet1").Cells(N, 12) = "D7210" Then
            If (Sheets("Sheet1").Cells(N, 13) >= 1 And Sheets("Sheet1").Cells(N, 13) <= 8) Or (Sheets("Sheet1").Cells(N, 13) >= 9 And Sheets("Sheet1").Cells(N, 13) <= 16) Or (Sheets("Sheet1").Cells(N, 13) >= 17 And Sheets("Sheet1").Cells(N, 13) <= 24) Or (Sheets("Sheet1").Cells(N, 13) >= 25 And Sheets("Sheet1").Cells(N, 13) <= 32) Then
                If WorksheetFunction.CountIf(Sheets("Sheet2").Columns(4), Sheets("Sheet1").Cells(N, 4)) < Sheets("Sheet2").Cells(4, 2) Then
                    Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(N, 4)
                    Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(0, 6) = Sheets("Sheet1").Cells(N, 12)
                    Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(0, 7) = Sheets("Sheet1").Cells(N, 13)
                End If
            End If
        End If
    End If
Next N
End Sub

Copy rows based on multiple IFs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy rows based on multiple IFs

Re: Copy rows based on multiple IFs

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Multiple Rows Based on Cell Value

Copy rows from multiple sheets based on condition

How to copy rows based on multiple criteria from one worksheet to another ?

Copy rows based off multiple cells

Copy multiple cells from two different rows to other rows based on a pull down list

[SOLVED] Insert Multiple Rows Based Off Number in Cell and Copy Data From Above New Rows

Copy Multiple Rows Based on Values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions