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

 

[ItalianPlatinum]

Thanks for the help. I appreciate it guys

 

[Akuini]

I don’t want to cause performance issues because I loop through that code for 25+ sheets

Could you explain in more detail?
If you mean you want to copy an exact same range to 25+ different sheets then it's probably better to load the range values into an array and then send the array value to the 25+ different sheets.

 

[ItalianPlatinum]

Yeah of course for instance the below or to explain quickly I run a program sec2 for 20+ parameters. About 20 times. If my identifier starts with A, B, C, D and etc. then transfer A to sheet A and B to sheet B and so forth. Its always going to be A10:L just L may be row 100 or row 1million.

' Run loop for range, clear, run, copy and paste into its respective sheet
  i = 0
  Do Until WsCus.Range("FILTER").Offset(i, 0) = ""
    FILTER = WsCus.Range("FILTER").Offset(i, 0)
    SheetName = WsCus.Range("FILTER").Offset(i, -1).Value 'Assuming from your screenshot it's in the column left of the filter

'apply filter to start loop and activate sheet
With WsSec
    .Range("VALUE") = FILTER
    .Application.Calculation = xlManual
    .Activate
    .Range("A10").CurrentRegion.Delete
End With

    Call Sec2
  
'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 WsSec.Range("A10:L" & lastRow)
    Worksheets(SheetName).Range("A1").Resize(.rows.count, .Columns.count).Value = .Value
End With

With Worksheets(SheetName)
        '.Range("A1").Resize(lastRow - 10 + 1, 12).Value = WsSec.Range("A10:L" & lastRow).Value
lr1 = .Cells(rows.count, "A").End(xlUp).row
        .Range("M1:Q1") = Array("Rounded 2 digit", "Greater than 1yr", "Greater than 3yr", "For formula", "Same Cost Occurrence")
        .Application.Calculation = xlAutomatic
        .Range("P2:P" & lr1).NumberFormat = "General"
        .Range("M2:M" & lr1).Formula = "=Round(K2, 2)"
        .Range("N2:N" & lr1).Formula = "=if(RUN-E2>365,""YES"",""NO"")"
        .Range("O2:O" & lr1).Formula = "=if(RUN-E2>(365*3),""YES"",""NO"")"
If TFLG = "N" Then
        .Range("P2:P" & lr1).Formula = "=D2&M2"
Else
        .Range("P2:P" & lr1).Formula = "=D2&E2&M2"
End If
        .Range("P2:P" & lr1).NumberFormat = "@"
        .Range("Q2").Formula2 = "=COUNTIF($P$2:$P$" & lr1 & ",$P$2:$P$" & lr1 & ")"
        '.Range("P2").Formula2 = "=COUNTIFS($D$2:$D$" & lr1 & ",$D$2:$D$" & lr1 & ",$M$2:$M$" & lr1 & ",$M$2:$M$" & lr1 & ")"
        .Range("M2:P" & lr1).Value = .Range("M2:P" & lr1).Value
        .Range("1:1").AutoFilter
        .Cells.EntireColumn.AutoFit
        .Activate
End With

ActiveWindow.ScrollRow = 1 'the row you want to scroll to
ActiveWindow.ScrollColumn = 1 'the column you want to scroll to
  
    i = i + 1
Loop

 

[Akuini]

Yeah of course for instance the below or to explain quickly I run a program sec2 for 20+ parameters. About 20 times. If my identifier starts with A, B, C, D and etc. then transfer A to sheet A and B to sheet B and so forth

I think the part that sends values from one range to another will only take a split second, so it won't affect the processing time.
Probably the part that starts with With Worksheets(SheetName) will take the most time.

If you want to know how much time a particular part of the code takes, you can use the Timer function, like this:

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

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

Debug.Print "WsSec.Range... :  " & Format(Timer - t, "0.00") & " seconds"

t = Timer
With Worksheets(SheetName)
        '.Range("A1").Resize(lastRow - 10 + 1, 12).Value = WsSec.Range("A10:L" & lastRow).Value
lr1 = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("M1:Q1") = Array("Rounded 2 digit", "Greater than 1yr", "Greater than 3yr", "For formula", "Same Cost Occurrence")
        .Application.Calculation = xlAutomatic
        .Range("P2:P" & lr1).NumberFormat = "General"
        .Range("M2:M" & lr1).Formula = "=Round(K2, 2)"
        .Range("N2:N" & lr1).Formula = "=if(RUN-E2>365,""YES"",""NO"")"
        .Range("O2:O" & lr1).Formula = "=if(RUN-E2>(365*3),""YES"",""NO"")"
If TERMFLG = "N" Then
        .Range("P2:P" & lr1).Formula = "=D2&M2"
Else
        .Range("P2:P" & lr1).Formula = "=D2&E2&M2"
End If
        .Range("P2:P" & lr1).NumberFormat = "@"
        .Range("Q2").Formula2 = "=COUNTIF($P$2:$P$" & lr1 & ",$P$2:$P$" & lr1 & ")"
        '.Range("P2").Formula2 = "=COUNTIFS($D$2:$D$" & lr1 & ",$D$2:$D$" & lr1 & ",$M$2:$M$" & lr1 & ",$M$2:$M$" & lr1 & ")"
        .Range("M2:P" & lr1).Value = .Range("M2:P" & lr1).Value
        .Range("1:1").AutoFilter
        .Cells.EntireColumn.AutoFit
        .Activate
End With

Debug.Print "Worksheets(SheetName)... :  " & Format(Timer - t, "0.00") & " seconds"

ActiveWindow.ScrollRow = 1 'the row you want to scroll to
ActiveWindow.ScrollColumn = 1 'the column you want to scroll to
   
    i = i + 1
Loop

see the result in the immediate window.

 

[ItalianPlatinum]

Thanks I will give that a try tomorrow I am sure you are right. that Countifs formulas does read a lot of data. I may definitely need a faster alternative that is yet to be found

 

[ItalianPlatinum]

Ok yea so the transfer of data is marginal. hmm so idk how i can get that time down. Because I even cut the sheets in half to see and i am using a smaller group that is 25% the size of my larger group

WsSec.Range... : 0.04 seconds
Worksheets(SheetName)... : 0.25 seconds
WsSec.Range... : 0.00 seconds
Worksheets(SheetName)... : 0.10 seconds
WsSec.Range... : 0.00 seconds
Worksheets(SheetName)... : 0.09 seconds
WsSec.Range... : 0.00 seconds
Worksheets(SheetName)... : 0.10 seconds
WsSec.Range... : 0.26 seconds
Worksheets(SheetName)... : 6.47 seconds
WsSec.Range... : 0.19 seconds
Worksheets(SheetName)... : 3.54 seconds
WsSec.Range... : 0.21 seconds
Worksheets(SheetName)... : 4.05 seconds
WsSec.Range... : 0.17 seconds
Worksheets(SheetName)... : 46.32 seconds
WsSec.Range... : 0.22 seconds
Worksheets(SheetName)... : 61.31 seconds
WsSec.Range... : 0.25 seconds
Worksheets(SheetName)... : 88.29 seconds
WsSec.Range... : 0.19 seconds
Worksheets(SheetName)... : 52.86 seconds
WsSec.Range... : 0.23 seconds
Worksheets(SheetName)... : 47.22 seconds
WsSec.Range... : 0.25 seconds
Worksheets(SheetName)... : 5.49 seconds
WsSec.Range... : 0.17 seconds
Worksheets(SheetName)... : 30.25 seconds

 

[Akuini]

idk how i can get that time down.

I'm not sure how, probably you could start a new thread for that.

 

[ItalianPlatinum]

Yea I got it from a prior thread I posted but now it seems like I need to create a new one to optimize it potentially. I have another outstanding thread that ill repurpose and repost a new one I suppose including both items.