Copy & paste the filtered rows

[adamsm]

Hi anyone,

The following code copies filterred data rows from the active workbook sheet and pastes them in the destination workbook if both workbooks are open.

Sub Copy()
    Dim Rng As Range
    Set Rng = Worksheets("Mem").Columns("D")
    Set Rng = Rng.Resize(65535, 1).Offset(1, 0)
    Set Rng = Rng.Resize(, 3).SpecialCells(xlCellTypeVisible)
    Rng.Copy Workbooks("Book2.xlsx").Worksheets("Mem").Range("D17")
End Sub

How could I make the code to open the Book2.xlsx; paste the copied data and close the workbook with the VBA code without having manually to do that.

Any help on this would be kindly appreciated.

 

[adamsm]

Here is the modification I've made. This modification opens the destination workbook and closes it. But does not copy the data into it.

What may be the reason for this?

Any help on this would be kindly appreciated.

Thanks in advance.

Sub Copy()
Dim wb As Workbook
' Open the workbook
Set wb = Workbooks.Open("D:\Database\Book2.xlsx")
    Dim Rng As Range
    Dim MemosWks As Worksheet
    Set MemosWks = wb.Worksheets("Mem")
    Set Rng = wb.Worksheets("Mem").Columns("D")
    Set Rng = Rng.Resize(65535, 1).Offset(1, 0)
    Set Rng = Rng.Resize(, 13).SpecialCells(xlCellTypeVisible)
    Rng.Copy Workbooks("Book2.xlsx").Worksheets("Mem").Range("D1")
    ' Save and close the workbook
 wb.Close SaveChanges:=True
End Sub

 

[ukmikeb]

Hi

Because you haven't referenced your source area!

hth

 

[adamsm]

Hi, Thanks for the reply. Hiow should I reference my source area?

Any help on this would be kindly appreciated.