VBA to copy autoflitered data to another file

[Anandpersad]

Hi All,

Can someone please help me with the following issue:
VBA will open file Kenneth.xlsx.
In this file the filtered data has to be copied into. This file has several sheets (all sheets have a unique name). Based on the filtered data from “working”file “In Macro V 1.xlsm”, data has to copy to the correct sheet in file Kenneth.xlsx. So, I have two different files (documents).
Below is the macro I have now. All is working fine, until I get to "Sheets("Anand").Select". I am not able to paste the selected data from “In Macro V 1.xlsm” to “sheet Anand” in file “Kenneth.xlsx”
I think I got all the possible error codes, as I tried several solutions. Now, I have no idea how to proceed further.

The macro is as follows:
Sub Copy_Rows()

'To copy the selected rows to the destiny file

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

Workbooks.Open Filename:="C:\Users\Kenneth"

Workbooks("In Macro V 1.xlsm").Activate
Sheets("Bench").Select
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("A:CC").AutoFilter Field:=10, Criteria1:="="
Range("A1:CC1").Select
Dim rng As Range
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A:CC"))
rng.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Workbooks("Kenneth.xlsx").Activate
Workbooks("Kenneth.xlsx").Select
Sheets("Anand").Select
Range("A1").Select
ActiveSheet.Paste
'Selection.Paste
Range("A1").Select

Windows("In Macro V 1.xlsm ").Activate
Range("A1").Select
End Sub

 

[JackDanIce]

Untested, however, try:

Sub Copy_Rows_v1()


    Dim x       As Long
    Dim wbS As Workbook
    Dim wbD As Workbook
    
    Set wbS = Workbooks("In Macro V 1.xlsm")
    Set wbD = Workbooks.Open("C:\Users\Kenneth.xlsx")
    
    Application.ScreenUpdating = False
    
    With wbS.Sheets("Bench")
        On Error Resume Next
        If .AutoFilterMode Then .ShowAllData
        On Error GoTo 0
        
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        With .Range("A1:CC" & x)
            .AutoFilter
            .AutoFilter 10, "="
            .SpecialCells(xlCellTypeVisible).Copy
        End With
    End With
    
    wbD.Sheets("Anand").Range("A1").PasteSpecial xlPasteValues
    wbS.Activate
    
    Application.ScreenUpdating = True
    
    Set wbS = Nothing
    Set wbD = Nothing
    
End Sub

 

[Anandpersad]

Untested, however, try:

Sub Copy_Rows_v1()


    Dim x       As Long
    Dim wbS As Workbook
    Dim wbD As Workbook
    
    Set wbS = Workbooks("In Macro V 1.xlsm")
    Set wbD = Workbooks.Open("C:\Users\Kenneth.xlsx")
    
    Application.ScreenUpdating = False
    
    With wbS.Sheets("Bench")
        On Error Resume Next
        If .AutoFilterMode Then .ShowAllData
        On Error GoTo 0
        
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        With .Range("A1:CC" & x)
            .AutoFilter
            .AutoFilter 10, "="
            .SpecialCells(xlCellTypeVisible).Copy
        End With
    End With
    
    wbD.Sheets("Anand").Range("A1").PasteSpecial xlPasteValues
    wbS.Activate
    
    Application.ScreenUpdating = True
    
    Set wbS = Nothing
    Set wbD = Nothing
    
End Sub

Hi Jack,

Thank you very much for your help. Much appreciated.

Anand

 

[JackDanIce]

You're welcome, hope it works!