I'd like to use the same macro as here: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
but I need to copy data to the first empty cell, not to A1.
Please see my current code below:
Sub fromreport()
Const fromFile = "c:\Users\asobczyk\Desktop\Report.xls"
Dim srcBook As Workbook
Set srcBook = Application.Workbooks.Open(fromFile, _
UpdateLinks:=False)
Application.ScreenUpdating = False
srcBook.Sheets("Test Invoice Report").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "report"
srcBook.Close False
Dim src As Worksheet
Dim tgt As Worksheet
Dim filterRange As Range
Dim copyRange As Range
Dim lastRow As Long
Set src = ThisWorkbook.Sheets("report")
Set tgt = ThisWorkbook.Sheets("One")
src.AutoFilterMode = False
lastRow = src.Range("A" & src.rows.Count).End(xlUp).Row
Set filterRange = src.Range("A8:J" & lastRow)
Set copyRange = src.Range("B9:J" & lastRow)
filterRange.AutoFilter field:=1, Criteria1:="EN > One"
copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B3")
Application.DisplayAlerts = False
Worksheets("report").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub
Best Regards,