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

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


"K" wrote:

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

On 2 Jun, 17:18, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"K" wrote:
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- Hide quoted text -

- 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