Excel VBA Run-time error '13' Type mismatch

[rynxzard]

I created a macro for a file but i am stuck on the line that was colored red
who can help me with this error.



Dim listSheetRange As Range
'Sheet to copy data From
Dim listSheet As Worksheet
'Sheet to copy data To
Dim actionSheet As Worksheet
Set listSheetRange = Worksheets("TRACKER").UsedRange
Set listSheet = Worksheets("TRACKER")
Set actionSheet = Worksheets("Daily HOLD")
'Clear the To Sheet
actionSheet.UsedRange.Clear
'Row 1 of From Sheet contains the data to match
'Copy Header Row i.e Row 2 of From Sheet
listSheet.Rows(1).Copy Destination:=actionSheet.Rows(1)
If UCase(listSheet.Columns(6)) = "HOLD" Then
listSheet.Columns(2).Copy Destination:=actionSheet.Columns(2)
listSheet.Columns(3).Copy Destination:=actionSheet.Columns(3)
listSheet.Columns(4).Copy Destination:=actionSheet.Columns(4)
listSheet.Columns(5).Copy Destination:=actionSheet.Columns(5)
listSheet.Columns(6).Copy Destination:=actionSheet.Columns(6)
listSheet.Columns(7).Copy Destination:=actionSheet.Columns(7)
listSheet.Columns(8).Copy Destination:=actionSheet.Columns(8)
listSheet.Columns(9).Copy Destination:=actionSheet.Columns(9)
listSheet.Columns(10).Copy Destination:=actionSheet.Columns(10)
End If

'hide any unwanted columns
actionSheet.Columns(1).Hidden = 1
actionSheet.Activate

 

[JLGWhiz]

It don't like trying to pick one value out of an entire column. Maybe a statement like this would work better.

If Application.CountIf(UCase(listSheet.Columns(6)), "HOLD") > 0 Then

This will allow VBA to look at the column to see if that value is there somewhere. If it is then then the other statements will execute, if not it will by pass the other statements.

 

[rynxzard]

hello JLGWhiz still did not work..same error appears can, do you have another way to solve it?

thank you!

 

[JLGWhiz]

hello JLGWhiz still did not work..same error appears can, do you have another way to solve it?

thank you!

Remove the UCase function.

If Application.CountIf(listSheet.Columns(6), "HOLD") > 0 Then