Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |