subscript out of range

[p4nny]

Hi,

The following code seems to work to fine, however at the end it says "subscript out of range" - please could you help? Much appreciated.


Sub exportsheets()

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




On Error GoTo Errorcatch


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

Range("F5:G5", "T3:U3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



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

ActiveWorkbook.Close


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

Application.ScreenUpdating = True



Errorcatch:
MsgBox Err.Description

 

[SteveO59L]

are all of your worksheet names correct?