vba copying unique values of a range to another workbook.

[k_bs]

I'm trying to copy a range of data from my source sheet that is found in column A. there is a header then my data starts in A2 and trying to copy values down to the last row containing data in A. this data contains duplicates and I am trying to copy only the unique values. my destination is column "S". I'd then like to copy over a value from the source sheet (in"F2") and paste it to column "A" in my destination sheet filling the cells from the first and last row as the data in column S occupy. I'd like to repeat this many times with different source sheets and I'd like to continue paste my data to the first available empty cell in column S in the destination sheet ("test"), as well as with A.

I started writing it and this is where I am:
Sub linelistings()

Dim mainWB As Workbook
Dim wsMaster As Worksheet
Dim filePath As Variant
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Dim lastRow As Long
Dim sourceRange As Range
Dim destinationRange As Range
Dim firstEmptyCell As Range
Dim criteriarange As Range

' Set master workbook
Set mainWB = ThisWorkbook

' Set master "Data" worksheet
Set wsMaster = mainWB.Sheets("test")

' Prompt the user to select source file

filePath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", , "Select Workbook to Open")

' Open the selected workbook
Set sourceWB = Workbooks.Open(filePath)

' Set the source worksheet
Set sourceWS = sourceWB.Sheets("CSV OUTPUT")

' Copy range1 from source worksheet
lastRow = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row
Set sourceRange = sourceWS.Range("A2:A" & lastRow)

' Find the first empty cell in wsmaster column S

Set firstEmptyCell = wsMaster.Cells(wsMaster.Rows.Count, "S").End(xlUp).Offset(1, 0)

Set destinationRange = wsMaster.Range("S" & firstEmptyCell.Row)

I then trying to use the advancedfilter but cannot get it to work..
also, for my second set when I get to "F2" or let's say I have further ranges to transfer, do I name them as sourcerange1, 2,3..etc? and destinationranges as well?

thanks

 

[k_bs]

I actually have this:
Sub linelistings()

Dim mainWB As Workbook
Dim wsMaster As Worksheet
Dim filePath As Variant
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Dim lastRow1 As Long, lastrow2 As Long
Dim sourceRange As Range
Dim destinationRange As Range
Dim firstEmptyCell As Range
Dim criteriarange As Range

' Set master workbook
Set mainWB = ThisWorkbook

' Set master "Data" worksheet
Set wsMaster = mainWB.Sheets("test")

' Prompt the user to select source file

filePath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", , "Select Workbook to Open")

' Open the selected workbook
Set sourceWB = Workbooks.Open(filePath)

' Set the source worksheet
Set sourceWS = sourceWB.Sheets("CSV OUTPUT")

lastRow1 = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row
Set sourceRange = sourceWS.Range("A2:A" & lastRow1)

lastrow2 = wsMaster.Cells(wsMaster.Rows.Count, "S").End(xlUp).Row
Set destinationRange = wsMaster.Range("S" & Rows.Count).End(xlUp).Offset(1)

' Apply the Advanced Filter to copy unique values to the destination range
sourceRange.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=destinationRange, unique:=True

Application.CutCopyMode = False 'Clear clipboard


End Sub


it works for the data copied to column S in the destination except the first value in the range gets copied over in a duplicate. I read that AF requires a header row but if I change the source range sourceWS.Range("A1:A" & lastRow1) then it just transfers over the header and I did not like to do that. plus I'm not sure why but when I try to select a new sheet the next dataset gets copied to not the next available empty cell but skips one cell so end of previous data+blank+start of new.
I'm not sure how to do then transferring "F2" cell from my source sheets (it is always in F2 position in all the source sheets) and filling a range in column A starting from the next available cell in A and filling down to the same row number as occupied in "S"