I have one workbook with two worksheets in it. What I am trying to do is autofilter through a range of unique values that I extracted and placed in a column in the far off reaches of both sheets. I’m then looking to copy and paste the values in the first three columns of the first visible row of what was filtered. I’m including the code below as I’ve tried a number of ways to approach this, but regardless of what method I use, whether to find the row number of the first row after filtering, using the code below with the
union application, or a combination of the two what seems to happen is that when I run the filter below for “Slate Data” and then copy and paste it into the second sheet (“Placement Outlook”) always results in whatever is in row 2 on “Slate Data” actual to be copy and pasted in the rows in “Placement Outlook”.
The interesting and frustrating part about this is that I will get the desired result when I manually go through the autofilter and separately run the code for copying and pasting below, it’s only when that code interacts with the autofilter code that it continues to only always copy and paste row 2 of the “Slate Data” worksheet into the “Placement Outlook” worksheet.
Any help is greatly appreciated!!
Dim lastrowuic, lastrowarray As Long lastrowuic = Cells(Rows.Count, "C").End(xlUp).Row lastrowarray = Cells(Rows.Count, "BP").End(xlUp).Row Dim uniquesarray As Variant uniquesarray = Range("C2:C" & lastrowuic) Dim sdws As Worksheet Set sdws = ActiveWorkbook.Sheets("Slate Data") Dim pows As Worksheet Set pows = ActiveWorkbook.Sheets("Placement Outlook") Dim firstcell, middlecell, lastcell As Range Set firstcell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1) Set middlecell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2) Set lastcell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3) Set homeport_command_uic = Application.Union(firstcell, middlecell, lastcell) Dim uic As Integer uic = 2 Do While sdws.Cells(uic, 68).Value <> "" sdws.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=sdws.Cells(uic, 68) If lastcell <> "" Then homeport_command_uic.Copy pows.Cells(uic, 2).Offset(1, 0) End If uic = uic 1 Loop End Sub