VBA PasteSpecial, Excel Crashes

[Rex2024]

I have some VBA code that filters a set of data, creates a worksheet titled after each filter, and then copies/pastes the data into the correct worksheet.

Last week it was only pasting the values, but this week it started pasting the formulas. I only want to paste the values.

However, when I go to modify the code to pastespecial, it causes excel to completely crash and close. If I just have .Paste it works fine. What am I doing wrong?

Sub filter()
Application>ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht As String

On Error Resume Next

sht = "Sheet1"

last = Sheets(sht).Cells(Rows.Count, "D").End(x1Up).Row
Set rng = Sheets(sht).Range("A1:T" & last)

Sheets(sht).Range("D2:D" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("CA1"), Unique:=True

For Each x In Range ([CA2], Cells(Rows.Count, "CA").End(xlUp))

With rng
.AutoFilter
.AutoFilter Field:=4, Criterial:=x.Value
.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.PasteSpecial Paste:=xlPasteValues

End With
Next x

Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

 

[DanteAmor]

You have some details in your code.
1. Don't use the On Error statement, as it won't let you know what errors you have in your code and will just move on to the next statement.

2. Use the Option Explicit statement at the beginning of all your code, it will force you to declare all your variables, but it will also check your code in case of errors.

3. Do not use reserved words in macro names or variables. You have "filter" in your macro name, you can use for example: filter_data.

4. In the first instruction you have
Application>ScreenUpdating = False
Must be:
Application.ScreenUpdating = False

5. In this line last = Sheets(sht).Cells(Rows.Count, "D").End(x1Up).Row you have the number 1 and it must be the letter "l"
xlUp

6. In this line .AutoFilter Field:=4, Criterial:=x.Value you have the letter "l" and it must be the number "1"
Criteria1

7. To paste it must be: ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

Try this:

Option Explicit

Sub filter_data()
  Application.ScreenUpdating = False
  Dim x As Range
  Dim rng As Range
  Dim last As Long
  Dim sht As String
  
  sht = "Sheet1"
  
  last = Sheets(sht).Cells(Rows.Count, "D").End(xlUp).Row
  Set rng = Sheets(sht).Range("A1:T" & last)
  
  Sheets(sht).Range("D2:D" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("CA1"), Unique:=True
  
  For Each x In Range([CA2], Cells(Rows.Count, "CA").End(xlUp))
    With rng
      .AutoFilter
      .AutoFilter Field:=4, Criteria1:=x.Value
      .SpecialCells(xlCellTypeVisible).Copy
      Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
      ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    End With
  Next x
  
  Sheets(sht).AutoFilterMode = False
  
  With Application
    .CutCopyMode = False
    .ScreenUpdating = True
  End With

End Sub

I recommend the following example where more tips come to start programming:

---

---

 

[Rex2024]

I apologize, work got the better of me but this worked perfectly! Thank you.