CellinFooter() Specify Which Sheets are updated
in [General]
|
Prev: Addition function of multiple cells
Next: Use Macro To Change Which Macro Assigned To Command Button
From: Microuch on 25 May 2010 09:26 I have spent much of the morning looking at various forums & threads but had no luck finding exactly what I need. As you can see the code below updates the right headers for all sheets in my workbook with the value in sheet = VBlookup, Cell B1. I have several sheets in the work book that I don't want updated. Is it possible to specify which sheets the code update? Thanks in advance Sub CellInFooter() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PageSetup.RightHeader = Range("VBlookup!B1").Text Next ws End Sub -- Microuch
From: Gord Dibben on 25 May 2010 15:25 Sub CellInFooter() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets _ (Array("Sheet1", "Sheet3", "Sheet5")) ws.PageSetup.RightHeader = Range("VBlookup!B1").Text Next ws End Sub Gord Dibben MS Excel MVP On Tue, 25 May 2010 14:26:09 +0100, Microuch <mark.crouchREMOVETHIS(a)capgemini.com> wrote: > >I have spent much of the morning looking at various forums & threads but >had no luck finding exactly what I need. > >As you can see the code below updates the right headers for all sheets >in my workbook with the value in sheet = VBlookup, Cell B1. I have >several sheets in the work book that I don't want updated. Is it >possible to specify which sheets the code update? > >Thanks in advance > >Sub CellInFooter() >Dim ws As Worksheet >For Each ws In ActiveWorkbook.Worksheets >ws.PageSetup.RightHeader = Range("VBlookup!B1").Text >Next ws >End Sub
From: Microuch on 26 May 2010 05:48 Worked a treat, thanks Gord. Gord Dibben;956056 Wrote: > Sub CellInFooter() > Dim ws As Worksheet > For Each ws In ActiveWorkbook.Worksheets _ > (Array("Sheet1", "Sheet3", "Sheet5")) > ws.PageSetup.RightHeader = Range("VBlookup!B1").Text > Next ws > End Sub > > > Gord Dibben MS Excel MVP > > > On Tue, 25 May 2010 14:26:09 +0100, Microuch > <mark.crouchREMOVETHIS(a)capgemini.com> wrote: > - > > > >I have spent much of the morning looking at various forums & threads > but > >had no luck finding exactly what I need. > > > >As you can see the code below updates the right headers for all > sheets > >in my workbook with the value in sheet = VBlookup, Cell B1. I have > >several sheets in the work book that I don't want updated. Is it > >possible to specify which sheets the code update? > > > >Thanks in advance > > > >Sub CellInFooter() > >Dim ws As Worksheet > >For Each ws In ActiveWorkbook.Worksheets > >ws.PageSetup.RightHeader = Range("VBlookup!B1").Text > >Next ws > >End Sub- -- Microuch
From: Gord Dibben on 26 May 2010 12:13
Thanks for the feedback. Gord On Wed, 26 May 2010 10:48:33 +0100, Microuch <mark.crouchREMOVETHIS(a)capgemini.com> wrote: > >Worked a treat, thanks Gord. > >Gord Dibben;956056 Wrote: >> Sub CellInFooter() >> Dim ws As Worksheet >> For Each ws In ActiveWorkbook.Worksheets _ >> (Array("Sheet1", "Sheet3", "Sheet5")) >> ws.PageSetup.RightHeader = Range("VBlookup!B1").Text >> Next ws >> End Sub >> >> >> Gord Dibben MS Excel MVP >> >> >> On Tue, 25 May 2010 14:26:09 +0100, Microuch >> <mark.crouchREMOVETHIS(a)capgemini.com> wrote: >> - >> > >> >I have spent much of the morning looking at various forums & threads >> but >> >had no luck finding exactly what I need. >> > >> >As you can see the code below updates the right headers for all >> sheets >> >in my workbook with the value in sheet = VBlookup, Cell B1. I have >> >several sheets in the work book that I don't want updated. Is it >> >possible to specify which sheets the code update? >> > >> >Thanks in advance >> > >> >Sub CellInFooter() >> >Dim ws As Worksheet >> >For Each ws In ActiveWorkbook.Worksheets >> >ws.PageSetup.RightHeader = Range("VBlookup!B1").Text >> >Next ws >> >End Sub- |