Copy Visible Cells Only

[dmbpt]

Hi guys,

I have a simple operation which is driving me nuts! I have the following code (below) to copy my active worksheet and paste it at the end of my excel file in order to keep an archive of the report.

The code works, however, this is an 8000 row report, with an auto filter - and when i run the code, the hidden rows are copied as well. ie: i have 300 visible rows, however the copy gives me all 8000. i'd like to keep all the formatting / objects as well.

thanks in advance!

Sub CopySheet()
Dim sheetname As String
sheetname = UCase(Left(Sheet18.Cells(11, 17), 3)) & Sheet18.Cells(13, 17)
For Each ws In ThisWorkbook.Worksheets
If InStr(ws.Name, sheetname) > 0 Then
chk = True
cnt = cnt + 1
End If
Next
If chk Then
sheetname = sheetname & " (" & cnt & ")"
End If
Worksheets("REPORT").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sheetname
ActiveSheet.UsedRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Kenneth Hobson]

Activesheet.UsedRange.SpecialCells(xlCellTypeVisible).Select

 

[dmbpt]

Hi Kenneth,

This doesn't solve the problem. I still have hidden rows in the middle

I don't know what's happening

 

[MARK858]

Try (totally untested)...

Sub CopySheet()
    Dim sheetname As String
    sheetname = UCase(Left(Sheet18.Cells(11, 17), 3)) & Sheet18.Cells(13, 17)
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, sheetname) > 0 Then
            chk = True
            cnt = cnt + 1
        End If
    Next
    
    If chk Then sheetname = sheetname & " (" & cnt & ")"

    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sheetname
    End With
    
    Worksheets("REPORT").UsedRange.SpecialCells(12).Copy
    Sheets(sheetname).Cells(1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

 

[Kenneth Hobson]

I used the same concept in your other thread. http://www.mrexcel.com/forum/excel-...applications-save-only-visible-cells-pdf.html