CODEWITHSUNDEEP
vbaexcel
Matt Rowles
Matt Rowles

Reputation: 8040

Copy all filtered data from one worksheet into new workbook

I am having trouble finding a surefire to copy only visible cells from one worksheet into a new workbook. My initial workbook is filtered. Something along the lines of:

Sub RangeToNew()

    Dim newBook as Workbook
    Set newBook = Workbooks.Add

    ThisWorkbook.Worksheets("worksheet").SpecialCells(xlCellTypeVisible).Copy _
        Before:=newBook.Worksheets(1)

End Sub

This doesn't work.

Upvotes: 1

Views: 16614

Answers (1)

Joseph
Joseph

Reputation: 5160

Looks like you need to set the SpecialCells range to a Range object first, then do your copy. Try this:

Sub rangeToNew_Try2()
    Dim newBook As Excel.Workbook
    Dim rng As Excel.Range

    Set newBook = Workbooks.Add

    Set rng = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible)

    rng.Copy newBook.Worksheets("Sheet1").Range("A1")
End Sub

Upvotes: 3

Related Questions