CODEWITHSUNDEEP
vbaexcel
Bluesector
Bluesector

Reputation: 329

Excel VBA - Copy from one sheet into another > copies only one row

I have a problem with my code. The code should copy all rows till the last row based on the sheet "hideMaster" column B, but this macro only copies the first 5 rows.

Sub DeleteFilterAndCopy()

Dim LASSSST As Long
Dim IP As Worksheet

Set IP = ThisWorkbook.Worksheets("Input")

LASSSST = IP.Cells(Rows.Count, "B").End(xlUp).Rows.Count

Sheets("MASTER").Cells.clearcontents
Sheets("hideMASTER").Range("A5:U" & LASSSST).Copy
Sheets("MASTER").Range("A1").PasteSpecial xlPasteValues

[...]

Can somebody find the problem here?

Upvotes: 2

Views: 197

Answers (2)

Chrowno
Chrowno

Reputation: 198

Edit: arcadeprecinct found the error.

Try this instead:

Sub DeleteFilterAndCopy()

Dim LASSSST As Long
Dim IP As Worksheet

Set IP = ThisWorkbook.Worksheets("Input")

Sheets("MASTER").Cells.clearcontents
With ThisWorkbook.Worksheets("hideMASTER")
    .Range("A5", .Cells(Rows.Count,21).End(xlUp)).Copy Destination:= ThisWorkbook.Worksheets("MASTER").Range("A1")
End with

Upvotes: 2

arcadeprecinct
arcadeprecinct

Reputation: 3777

LASSSST = IP.Cells(Rows.Count, "B").End(xlUp).Rows.Count

this is the number of rows in 

IP.Cells(Rows.Count, "B").End(xlUp)

which is 1, since it's only one cell. This caused it to copy "A5:U1"

Use 

IP.Cells(IP.Rows.Count, "B").End(xlUp).Row

Note that I included IP.Rows.Count instead of Rows.Count as Shai Rado suggested, however the number of rows is the same on every sheet so it wouldn't matter. It's still good practice to reference everything though.

Upvotes: 2

Related Questions