Unhide/Hide a sheet which uses VBA Code

[Maya Maile]

Hi All,

I am having a recorded macro on a excel sheet.
I have a button on another sheet to run this macro.
When I run the macro it says the following error.

I want to add two lines. First to unhide the sheet where the macro is running and second hide it automatically.

(Recorded Macro Code)

Sub DeleteDuplicates()
'
' DeleteDuplicates Macro
'

'
    Sheets("ABCTable").Visible = True
    Columns("H:H").Select
    Selection.Copy
    Range("I1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$I$1:$I$1048568").RemoveDuplicates Columns:=1, Header:= _
        xlNo
    Range("I3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A3000").Select
    Selection.End(xlUp).Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sorting
    Sheets("ABCTable").Visible = False
End Sub

Thanks in advance.

 

[kokano90]

Try it

Sub DeleteDuplicates()
'
' DeleteDuplicates Macro
'

If Sheets("ABCTable").Visible = False Then Sheets("ABCTable").Visible = True
With Sheets("ABCTable")
    .Range("H1:H" & .Range("H" & Rows.Count).End(3).Row).Copy
    .Range("I1").Paste
    .Range("I1:I" & .Range("I" & Rows.Count).End(3).Row).RemoveDuplicates Columns:=1, Header:= _
    xlNo
    .Range("I1:I" & .Range("I" & Rows.Count).End(3).Row).Copy
    .Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A3").Sort , xlAscending
End With
Sheets("ABCTable").Visible = False
End Sub

 

[Fluff]

I want to add two lines. First to unhide the sheet where the macro is running and second hide it automatically.

But surely that is what you have done already with these two lines.

Sheets("ABCTable").Visible = True

   Sheets("ABCTable").Visible = False

Is the sheet protected?

 

[Alex Blakenburg]

@kokano90
I had to make a couple of adjustments to get the code to run.
Changed the Paste to I1 to be:-
.Range("I1").PasteSpecial Paste:=xlPasteAll
Changed the sort line to be:
.Range("A3").Sort key1:=.Range("A3"), order1:=xlAscending

The code will run on a hidden sheet, so not sure that the unhide/hide really serves any purpose.