VBA copy only visible cells and paste as values

[johnbird1988]

Hello,

I am try to copy only visible cells from and excel filtered table and paste them as values into another workbook. I would like to do this without using the clipboard but can not get the code to work.

I am trying:

Set wb = Workbooks.Open(SourceFolder & "\" & xlo)

   
    For Each sht In Sheets
    
 If sht.Name = "Report" Then
 
 srow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
 scol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

wb.Sheets("Report").AutoFilterMode = False

wb.Sheets("Report").Range(Cells(1, 1), Cells(srow, scol)).AutoFilter Field:=15, Criteria1:="=BIM" _
        , Operator:=xlOr, Criteria2:="=Report"

Windows(decomp).Activate

wb.Sheets("Report").Range(Cells(1, 1), Cells(srow, scol)).SpecialCells(xlCellTypeVisible).Copy ThisWorkbook.Sheets(12).Range("A1")

wb.Close False
End If
    Next sht

The code opens the file and filters the data set but does not copy and paste the data.

Any help or advise would be grateful,

Cheers

john

 

[JasL]

I made a slight change:

wb.Sheets("Report").Range(Cells(1, 1), Cells(srow, scol)).SpecialCells(xlCellTypeVisible).Copy '1) Copy Data
Set Copywb = Workbooks.Add '2) Select destination workbook (I created a new wkbk for ease, you would need to ref relevant wkbk)
Range("A1").PasteSpecial xlPasteAll '3) Paste the copied data.

ok?