Button to find and transfer data from one spread sheet to another biased on a specific variable

PA_VA13

New Member
Joined
Jul 2, 2020
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone!

So I was able to write a script that is attached to a button that would search for a specific variable in one sheet and then pull that data on the identified row to another sheet.

I receive a Run-time error "13": Type mismatch but the code still performs the task anyways.

Do I need to worry about this run-time error since it is still working? When I do the debug it shows that the issue involves my 'If Worksheets' line (in bold red in the below code).

This is the code:

Sub CaseAssignPathSH()

Dim lastrom As Long, erow As Long

'to check the last filled line on sheet neamed merge

lastrow = Worksheets("Merge").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow

If Worksheets("Merge").Cells(i, 20).Value = "SH" Then
Worksheets("Merge").Cells(i, 16).Copy
erow = Worksheets("PATH CASES").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Merge").Paste Destination:=Worksheets("PATH CASES").Cells(erow + 1, 1)
Worksheets("Merge").Cells(i, 17).Copy
Worksheets("Merge").Paste Destination:=Worksheets("PATH CASES").Cells(erow + 1, 2)
Worksheets("Merge").Cells(i, 18).Copy
Worksheets("Merge").Paste Destination:=Worksheets("PATH CASES").Cells(erow + 1, 3)

End If
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Maybe in column cell "T" you have some error like #VALUE #NA, etc

Try this:

VBA Code:
Sub CaseAssignPathSH()
  Dim c As Range
  For Each c In Sheets("Merge").Range("T2", Sheets("Merge").Range("T" & Rows.Count).End(3))
    If Not IsError(c) Then
      If c = "SH" Then c.Offset(, -4).Resize(1, 3).Copy Sheets("PATH CASES").Range("A" & Rows.Count).End(3)(2)
    End If
  Next
End Sub
 

PA_VA13

New Member
Joined
Jul 2, 2020
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Maybe in column cell "T" you have some error like #VALUE #NA, etc

Try this:

VBA Code:
Sub CaseAssignPathSH()
  Dim c As Range
  For Each c In Sheets("Merge").Range("T2", Sheets("Merge").Range("T" & Rows.Count).End(3))
    If Not IsError(c) Then
      If c = "SH" Then c.Offset(, -4).Resize(1, 3).Copy Sheets("PATH CASES").Range("A" & Rows.Count).End(3)(2)
    End If
  Next
End Sub

It worked! Thank yo very much!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,085
Messages
5,545,873
Members
410,711
Latest member
Josh324
Top