publish object error 1004

[aspiringnerd]

I'm getting an error when I change the sheet reference from this:

Sub ExportHtml()
    Dim ws As Worksheet, lr As Long, rng As Range
    Set ws = Sheets("Sheets1")
    lr = ws.Range("a" & ws.Rows.Count).End(xlUp).Row
    Set rng = ws.Range("a1:h" & lr)
    
    file1 = "C:\Desktop\" + Cells(ActiveCell.Row, "E").Value + ".html"

    ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=file1, _
    Sheet:=rng.Worksheet.Name, _
    Source:=rng.Address, _
    HtmlType:=xlHtmlStatic).Publish

End Sub

to this:

Sub ExportHtml()
    Dim ws As Worksheet, lr As Long, rng As Range
    Set ws = Sheets("Sheets2")
    lr = ws.Range("a" & ws.Rows.Count).End(xlUp).Row
    Set rng = ws.Range("a1:h" & lr)
    
    file1 = "C:\Desktop\" + Cells(ActiveCell.Row, "E").Value + ".html"

    ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=file1, _
    Sheet:=rng.Worksheet.Name, _
    Source:=rng.Address, _
    HtmlType:=xlHtmlStatic).Publish

End Sub

Sheet2 is hidden, I call this macro with a button on sheet1, it's only when I change sheet1 to sheet2 where I get error 1004, I'm not sure how to fix this one.

 

[Michael M]

Have you tried it with sheet 2 visible ??
I don't have Excel at the moment so can't try your code.

 

[Skyybot]

Where does your code reside? ThisWorkbook, Sheet1,Sheet2 or Module?

 

[aspiringnerd]

Where does your code reside? ThisWorkbook, Sheet1,Sheet2 or Module?

I've tried it with sheet2 visible with no luck, the code lives in a module.

When I debug, it highlights:

    ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=file1, _
    Sheet:=rng.Worksheet.Name, _
    Source:=rng.Address, _
    HtmlType:=xlHtmlStatic).Publish

 

[Skyybot]

Try changing the reference. You can't have an ActiveCell on a Hidden Sheet.

file1 = "C:\Desktop\" + Cells(ActiveCell.Row, "E").Value + ".html"

 

[aspiringnerd]

Try changing the reference. You can't have an ActiveCell on a Hidden Sheet.

file1 = "C:\Desktop\" + Cells(ActiveCell.Row, "E").Value + ".html"

This fixed it for me, thanks.

 

[Skyybot]

This fixed it for me, thanks.

Np. Glad to help.