0

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,

Community
  • 1
  • 1
Adrian
  • 725
  • 4
  • 18
  • From what I can see, you are not copying to A1, you are copying to B3? If you want to find out what the last row in `tgt` is, you can do exactly what you did for `src` can't you? Also, I think your `Application.ScreenUpdating` at the end of your sub probably needs to be set to `True`? – Zac Aug 07 '17 at 10:19
  • hi Zac, I have modified the code a little bit and didn't update the text, sorry for that. so I put `copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B" & src.rows.Count).End(xlUp).Row` but it doesn't work - run time error '1004' Copy method of range class failed – Adrian Aug 07 '17 at 11:03
  • I also put `copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B3:J" & lastRow)` but it still copy the data from B3 to the last row, not from the first empty cell. – Adrian Aug 07 '17 at 11:23
  • @Adrian, are there gaps in your data that need to be filled? If so your last row won't find those, it will simply find the cell after the last cell that contains data. – Luuklag Aug 07 '17 at 11:32
  • @Zac no there are no gaps in my table, I just copy the filtered data from "report" worksheet to "One" worksheet. The problem is that the macro always copy data from B3 instead of first empty cell. – Adrian Aug 07 '17 at 11:45
  • I think that I found the correct code which is `copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B" & rows.Count).End(xlUp).Offset(1, 0)` but I have another issue: run-time error - 1004 we can't paste because the copy area and paste area aren't the same size, do you know how can I fix this? – Adrian Aug 07 '17 at 12:00

0 Answers0