PLEASE CAN ANYBODY CORRECT THIS MACRO

[K]

Hi all, I got excel 2007 and I am trying to Save As Range ("A2:F23")
as Web Page file format. I have done it but I want sheet to be
Activesheet and file name to be Range("H2") for which I done the macro
below but its giving error. Can any friend can help me in this i'll be
very thankful to him.

Sub SAV()
Dim SH As Sheets
Dim NA As Range
SH = ActiveSheet
NA = Range("H2").Value
Range("A2:F23").Select
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\Documents and Settings\kamranr\My Documents\NA.htm", SH, _
"$A$2:$F$23", xlHtmlStatic, , "")
.Publish (True)
.AutoRepublish = False
End With
Range("A2").Select
End Sub

 

[Jim Thomlinson]

Give this a try (untested)

Sub SAV()
Dim SH As Sheets
Dim NA As Range
set SH = ActiveSheet
set NA = Range("H2")
Range("A2:F23").Select
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\Documents and Settings\kamranr\My Documents\" & NA.value &
".htm", _
SH, "$A$2:$F$23", xlHtmlStatic, , "")
.Publish (True)
.AutoRepublish = False
End With
Range("A2").Select
End Sub

 

[K]

Give this a try (untested)

Sub SAV()
Dim SH As Sheets
Dim NA As Range
set SH = ActiveSheet
set NA = Range("H2")
    Range("A2:F23").Select
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Documents and Settings\kamranr\My Documents\" & NA.value &
".htm", _
         SH, "$A$2:$F$23", xlHtmlStatic, , "")
        .Publish (True)
        .AutoRepublish = False
    End With
    Range("A2").Select
End Sub

--
HTH...

Jim Thomlinson






- Show quoted text -

Thanks for replying jim. in macro below i have solved my problem the
only thing left that how can i change Sheet1 to Activesheet. I did
try doing different thing but i am getting error message. Any
suggestions

Sub SAV()

Range("A2:F23").Select
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\Documents and Settings\KAMRAN\My Documents\" &
Range("K1").Value & " - " & Range("H2").Value & ".htm", _
"Sheet1", "$A$2:$F$23", xlHtmlStatic, , "")
.Publish (True)
.AutoRepublish = False
End With
Range("A2").Select
End Sub