I need to limit the amount of rows copied from sheet to workbook

Question

I have excell vba code. this works 100% but works to good as it copies the whole worksheet and not just the cells wit data in. there will be around 3000 rows. so how can i limit the copy to 5000 rows and to column bz?

Private Sub CommandButton1_Click()
Application.CopyObjectsWithCells = False
 Dim newBook As Excel.Workbook
    Dim rng As Excel.Range

    Set newBook = Workbooks.Add

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

    rng.Copy newBook.Worksheets("Sheet1").Range("A1")
Application.CopyObjectsWithCells = True 'reset
End Sub

Start [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jan 13 '20 at 17:22
Right answer actually - the proposed answer you got finds the last column and row, which is *exactly* what that link shows you how to do :-) — BigBen, Mar 27 '20 at 12:14

BruceWayne · Accepted Answer · 2020-01-13T17:35:21.547

A couple ways to do this, the simplest being to simply set the range:

Set rng = ThisWorkbook.Worksheets("Accounts Full").Range("A1:BZ5000").SpecialCells(xlCellTypeVisible)

Or, you can get the UsedRange (note, your mileage may vary depending on how the data is actually structured):

Set rng = ThisWorkbook.Worksheets("AccountsFull").UsedRange

Or, get more specific with the last column and row (assuming row 1 has headers):

Dim lastCol as Range, lastCel as Range
Set lastCol = ThisWorkbook.Worksheets("x").Cells(1, Columns.Count).End(xlToLeft)
Set lastCel = ThisWorkbook.Worksheets("x").Cells(rows.count,lastCol.Column).End(xlUp)
Set rng = ThisWorkbook.Worksheets("x").Range("A1",lastCel)

I need to limit the amount of rows copied from sheet to workbook

1 Answers1