Resize property when transferring data over

[ItalianPlatinum]

I am running into an issue applying the resize property. I recently started learning about it and I have tried so many variations on the below but its not working. Could someone let me know what I am doing wrong? It for some reason is coming with the last 9 records from A to L all as #N/A. Previously I was copying the data and pasting but I am working with a lot of data and testing performance improvements by moving to an approach more like the below.

'after execution copy from source and paste into relative sheet applying formulas
With WsSec
lastRow = .Cells(WsSec.rows.count, "A").End(xlUp).row 'find the maximum row
End With

On Error Resume Next
    If Not Worksheets(SheetName).Name = WsCus.Range("FILTER").Offset(i, -1).Value Then Worksheets.Add.Name = WsCus.Range("FILTER").Offset(i, -1).Value
On Error GoTo 0

With Worksheets(SheetName)
        .Range("A1:L" & lastRow).Resize(lastRow).Value = WsSec.Range("A10:L" & lastRow).Value

 

[Cubist]

It is because you're only resizing the rows and not the columns. When omitting the second parameter, its default value is 1. There are 12 columns from A to L.

 .Range("A1").Resize(lastRow-10+1,12).Value = WsSec.Range("A10:L" & lastRow).Value

Also lastRow is not the number of rows you're pasting. You're pasting from row 10 to last row so the number of rows needed to resize is lastRow - 10 +1.

 

[ItalianPlatinum]

So then in theory something like this should work?

.Range("A1:L" & lastRow).Resize(lastRow - 10).Value = WsSec.Range("A10:L" & lastRow).Value

 

[Cubist]

If you're going to call the whole range, you don't need to resize.

 

[ItalianPlatinum]

I only wish to bring over all used data from A10 to L onto that sheet starting on A1

 

[Cubist]

Did you try the code in #2? Do you understand it?

 

[ItalianPlatinum]

Not yet but I will. I tried #3 and worked but your point about not needing resize has me thinking do I need it? I don’t want to cause performance issues because I loop through that code for 25+ sheets

 

[Akuini]

Another option:

With WsSec.Range("A10:L" & lastRow)
    Worksheets(SheetName).Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[ItalianPlatinum]

Both those results work as does the one I sent in post #3. Timing almost seems the same. is one more efficient than the other or it doesn't really matter? What is proper coding method?

 

[Cubist]

You're resizing more than what you need it to be. If there's data already there, you'll overwrite it. I would recommend something like Akuini's, more dynamic.