0

Is it possible to copy and rename a hidden sheet?

I've created the macro as below

Sub Copyandrename()
'
' Copyandrename Macro
'

'
    Sheets("Template").Copy Before:=Sheets(1)
    Sheets("Template (2)").Name = "Issues and Key Takeaways"
    Rows("6:6").Select
    Range("C6").Activate
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Issues and Key Takeaways").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Issues and Key Takeaways").AutoFilter.Sort. _
        SortFields.Add Key:=Range("C6"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Issues and Key Takeaways").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D9").Select
End Sub

However once I hide the 'Template' tab, I'll get the following error.

Run-time error '91':

Object variable or With block variable not set

Any suggestions to fix the issue?

Tommy Li
  • 17
  • 5
  • 3
    What do you mean by "wont work"? Do you get an error? if so, what error and on what line? Let me guess.. it's probably on one of the `.Select` or `.Activate` lines... you cannot select a hidden worksheet, but you can still write code that accesses it... you need to learn how to write code that doesnt use `.Select` and `.Activate` – braX Nov 05 '19 at 04:22
  • 1
    Unhide the template, copy it, then hide it again. – Tim Williams Nov 05 '19 at 04:24
  • @braX The error I received was Run-time error '91': Object variable or with Block variable not set – Tommy Li Nov 05 '19 at 06:17
  • @TimWilliams - not possible, end user wants the template tab hidden after the running the macro – Tommy Li Nov 05 '19 at 06:19
  • 1
    @TommyLi - that's what "then hide it again" means ? https://stackoverflow.com/questions/42909585/copy-hidden-master-sheet-rename-it-and-place-it-at-end-of-workbook – Tim Williams Nov 05 '19 at 06:27

0 Answers0