VBA to filter, copy and paste in separate workbook

[Iron_Man]

Hi all,

I've adapted some code from an old thread on this forum and was hoping for some help to tweak the logic.

I have a file "CT Raw Data.xlsx" where my raw data is stored on a worksheet called "Raw Data", from cell A2 to column O (infinite number of lines).

In column A, I have a value which helps me decide which sheet of separate file "CT.xlsx" the data should be copied in (ie if a line has a value in column A of "Location 1", it should be copied in sheet "Location 1" of CT, etc). Noting that this macro is to be run on a daily basis and not all locations are always present (ie we could have Location 1, 2 and 3 on one day and just 1 and 2 on the next day").

The sheets on CT.xlsx all have the same format and data should be copied in the first blank row after B4. If I happen to click the button twice, am ok to have the values copied twice.

VBA Code:
Sub FilterMini()
Sheets(2).Activate
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrow2 = Workbooks("CT.xlsx").Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1
With ActiveSheet.Range("B2:O" & Lastrow)
.AutoFilter Field:=1, Criteria1:="Location A", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Workbooks("CT.xlsx").Worksheets("Location A").Range("B5:O" & Lastrow2)

End With
ActiveSheet.AutoFilterMode = False
End Sub


1. For a reason that I ignore, if I happen to run the code twice the data that just got pasted disappears or copies an incorrect title value from the destination file onto the destination file.

2. Any suggestion on how to iterate through different filters and copy to more sheets in the CT.xlsx workbook without writing the same code 6 times?

Do you have any thoughts on how to achieve this?

Thanks in advance,
IM