Macro Help please
 

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?

Macro Help please
 

Your relevant code being posted here would be nice.
--
** John C **


"Excel Beginner" wrote:

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?

Macro Help please
 

ActiveSheet.Unprotect
Range("A1:AM54").Select
ActiveWindow.SmallScroll Down:=-39
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"\\150.192.103.254\data\ASF\Web Products\KGRF_MEF.htm", "MEF(GRF)
00Z", _
"$A$1:$AM$55", xlHtmlStatic, "DAILY_PROD_22690", _
"Gray Army Airfield - Mission Execution Forecast")
.Publish (True)
.AutoRepublish = False
End With
ChDir "\\150.192.103.254\data\ASF\Web Products"
ActiveWindow.SmallScroll Down:=-27
ActiveSheet.PageSetup.PrintArea = "$A$1:$AM$54"
ActiveWindow.SmallScroll Down:=-30
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("D4:F4").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub

"Excel Beginner" wrote:

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?

Macro Help please
 

ActiveSheet.Unprotect
Range("A1:AM54").Select
ActiveWindow.SmallScroll Down:=-39
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"\\150.192.103.254\data\ASF\Web Products\KGRF_MEF.htm", "MEF(GRF)
00Z", _
"$A$1:$AM$55", xlHtmlStatic, "DAILY_PROD_22690", _
"Gray Army Airfield - Mission Execution Forecast")
.Publish (True)
.AutoRepublish = False
End With
ChDir "\\150.192.103.254\data\ASF\Web Products"
ActiveWindow.SmallScroll Down:=-27
ActiveSheet.PageSetup.PrintArea = "$A$1:$AM$54"
ActiveWindow.SmallScroll Down:=-30
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("D4:F4").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub
"Excel Beginner" wrote:

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?

Macro Help please
 

Two simple questions first. My VBA isn't all that great, but I do have 2
quick questions.
1 In the coding prior to what you have shown, are there any sheet
selections that could be throwing you off? (probably not, but gotta check)
2 Is the macro residing in your Mon(day) sheet? or is it residing where it
properly should, under ThisWorkbook?
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Excel Beginner" wrote:

ActiveSheet.Unprotect
Range("A1:AM54").Select
ActiveWindow.SmallScroll Down:=-39
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"\\150.192.103.254\data\ASF\Web Products\KGRF_MEF.htm", "MEF(GRF)
00Z", _
"$A$1:$AM$55", xlHtmlStatic, "DAILY_PROD_22690", _
"Gray Army Airfield - Mission Execution Forecast")
.Publish (True)
.AutoRepublish = False
End With
ChDir "\\150.192.103.254\data\ASF\Web Products"
ActiveWindow.SmallScroll Down:=-27
ActiveSheet.PageSetup.PrintArea = "$A$1:$AM$54"
ActiveWindow.SmallScroll Down:=-30
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("D4:F4").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub

"Excel Beginner" wrote:

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?

Macro Help please
 

I don't think any sheet selections are throwing me off. For the second
question the Macro is residing under ThisWorkbook.
One other thing, it wouldn't be a deal breaker if I have to write a seperate
macro for each named worksheet if after copying one of the worksheets and
renaming it that the macro will work for the copied sheet.

"John C" wrote:

Two simple questions first. My VBA isn't all that great, but I do have 2
quick questions.
1 In the coding prior to what you have shown, are there any sheet
selections that could be throwing you off? (probably not, but gotta check)
2 Is the macro residing in your Mon(day) sheet? or is it residing where it
properly should, under ThisWorkbook?
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Excel Beginner" wrote:

ActiveSheet.Unprotect
Range("A1:AM54").Select
ActiveWindow.SmallScroll Down:=-39
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"\\150.192.103.254\data\ASF\Web Products\KGRF_MEF.htm", "MEF(GRF)
00Z", _
"$A$1:$AM$55", xlHtmlStatic, "DAILY_PROD_22690", _
"Gray Army Airfield - Mission Execution Forecast")
.Publish (True)
.AutoRepublish = False
End With
ChDir "\\150.192.103.254\data\ASF\Web Products"
ActiveWindow.SmallScroll Down:=-27
ActiveSheet.PageSetup.PrintArea = "$A$1:$AM$54"
ActiveWindow.SmallScroll Down:=-30
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("D4:F4").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub

"Excel Beginner" wrote:

I have a Macro set up to save a work sheet as a web page and print to a
printer on a multiple sheet workbook. I have the same work sheet several
times in the workbook named different. Ex: Mon, Tue, Wed. I recorded the
Macro on the Mon sheet and am trying to get it to work for all other sheets.
When i run the Macro it only publishes the Mon sheet even though I have the
Tue work sheet active. Is there a way to have the Macro run on the Active
Sheet only?