help with VBA Type Mismatch

[rcirone]

My data is on the (Cal) tab from A2:L300. Were I need it too be is on the (Dashboard) tab from U5:AF. Manager name list is on (U2) and when I press the button I get Type Mismatch please help

Sub ManagerLastWktoDashBd()

Dim c As Range
Dim lr As Long
Dim mgrCol As Range
Dim aMgr As String

aMgr = Range("U2")

With Sheets("Cal")

lr = Range("A" & Rows.Count).End(xlUp).Row

Set mgrCol = Range("A1:A" & lr)

Application.ScreenUpdating = False
 For Each c In mgrCol
   If c = aMgr And c.Offset(, 1) = c.Offset(, 6) Then
  
      c.Offset(, -2).Resize(1, 6).Copy
      Sheets("Dashboard").Range("u" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
      Sheets("Dashboard").Range("ah" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
   End If
 Next
  
End With
Application.ScreenUpdating = True
End Sub

 

[ukmikeb]

Hi

You've defined aMgr as a String when it should be Range(?).

Or maybe you should be looking at the value in c.

hth

 

[Teeroy]

Inside your With block you haven't referenced the ranges to the With object, they should start with a (.), so the parent object defaults to the Activesheet. Try changing to (untested, so there may be typos):

Sub ManagerLastWktoDashBd()

Dim c As Range
Dim lr As Long
Dim mgrCol As Range
Dim aMgr As String


With Sheets("Cal")

aMgr = .Range("U2") 'I'm guessing this is on "Cal" as well

lr = .Range("A" & Rows.Count).End(xlUp).Row

Set mgrCol = .Range("A1:A" & lr)

Application.ScreenUpdating = False
 For Each c In mgrCol
   If c = aMgr And c.Offset(, 1) = c.Offset(, 6) Then
  
      c.Offset(, -2).Resize(1, 6).Copy
      Sheets("Dashboard").Range("u" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
      Sheets("Dashboard").Range("ah" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
   End If
 Next
  
End With
Application.ScreenUpdating = True
End Sub

You've defined aMgr as a String when it should be Range(?).

Ukmikeb, this will assign the value of the Range as a string to aMgr.