Fix code to transpose data into next available row, instead of replacing existing

[cliniconboarding]

Hey all, new to this forum. I'm working on a series of macros in excel and I've almost got it working perfectly but I'm running into an issue. Essentially I run this same code on a different reference worksheet to pull PCP provider patients to their respective PCP worksheet (creating a PCP worksheet name in the process). Here I'm trying to run it again on a different worksheet to pull in Non-PCP patients to the respective provider they've most recently seen. But when I run it I'm replacing the data I pulled originally instead of starting in the next available row. I know this should be an easy fix but I think I've been staring at it too long.

Help is much appreciated!


'SPLIT PATIENTS FROM "ADD" WORKSHEET TO THEIR RESPECTIVE PROVIDER

Sub parse_NonPCProviders()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer


MsgBox (" Brace yourself for this one. You may need to walk away for a bit. ")


vcol = 4
Set ws = Sheets("ADD")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:H1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1, 0) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
MsgBox (" Non-PCP Provider Data Parse Complete ")


End Sub

 

[Fluff]

Hi & welcome to the board
How about

ws.Range("A" & titlerow & ":A" & LR).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[cliniconboarding]

And there it is, the one liner that solves all of my problems! Subbed into my code and it works perfectly. It brings in the header row again, but that actually work really well. It will identify doctors who they are listed as primary care physician for vs who has no PCP but has seen them recently. Lifesaver, Thank you for your time!

Hi & welcome to the board
How about

ws.Range("A" & titlerow & ":A" & LR).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Fluff]

Glad to help & thanks for the feedback