Hello,
I'm using below code to auto update data from one sheet to another within same workbook :
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, ColArr, i As Long, ws As Worksheet
ColArr = Array("P", "Q", "U", "O", "N", "R")
Set ws = Sheets("owssvr")
If Not Intersect(Target, Range("C2")) Is Nothing Then
Application.EnableEvents = False
x = Application.Match(Target, ws.Range("C:C"), 0)
If Not IsError(x) Then
Range("C3").Resize(5) = Application.WorksheetFunction.Transpose(Array(ws.Range("D" & x), ws.Range("H" & x), ws.Range("A" & x), ws.Range("X" & x), ws.Range("I" & x))) 'Getting Error on this line
For i = LBound(ColArr) To UBound(ColArr)
If Not IsError(ws.Range(ColArr(i) & x)) Then Range("F" & i + 2) = ws.Range(ColArr(i) & x)
Next i
Range("B9").CurrentRegion.Delete
With ws
With .Cells(1).CurrentRegion
ws.ListObjects("Table_owssvr").Range.AutoFilter Field:=3, Criteria1:=Target
ws.ListObjects("Table_owssvr").Range.AutoFilter Field:=41, Criteria1:="Positive"
If .Columns(3).SpecialCells(xlCellTypeVisible).Cells.Count - 1 Then
Union(.Columns("J"), .Columns("L"), .Columns("S"), .Columns("V"), .Columns("Z"), .Columns("AP"), .Columns("AQ:AR")).Copy Range("B9")
ws.ListObjects("Table_owssvr").Range.AutoFilter
Else
MsgBox "No Records Found for ""Positive"""
End If
Application.EnableEvents = True
End If
End Sub
"C2" is the cell in which I enter the Project No. and the data is automatically updated.
But for some of the project No. the data is not getting updated, even though we have the data for that projects. Say for example, if I enter project no. 32, the data gets updated, if I'm entering 925, 938 project no. in "C2" cell, I'm getting "Run time error 13 type mismatch" error message.
Please suggest. Thanks !
Bookmarks