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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,447
Messages
5,547,974
Members
410,820
Latest member
Prepost
Top