Type mismatch... VBA

[p4nny]

Hi

When I run the following code I get the 'Type Mismatch' error:

The code works ok for the first export (in terms of saving in location), however will not work thereafter.


Sub exportsheets()

Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String


Application.ScreenUpdating = False


strPath = "C:\Users\pandoan\Desktop\test\"
Set rngTM = Sheets("Flow TM's").Range("A1")


Do
Sheets(Array("HC pivot TM data", rngTM.Value)).Copy
Set wbNew = ActiveWorkbook
With wbNew
.Sheets("HC pivot TM data").Visible = False
Application.Goto .Sheets(1).Range("B13"), True

wbNew.SaveAs strPath & rngTM.Value & Format(Date, "ddmmmyyyy") & ".xlsx"


End With
Set rngTM = rngTM.Offset(1, 0)
Loop Until rngTM.Value


Application.ScreenUpdating = True

End Sub

 

[Domenic]

When you click on Debug, which line of code is highlighted? Does it help if you replace...

Loop Until rngTM.Value

with

Loop Until Len(rngTM.Value) = 0

???