VB Easy Question
Posted by RoB on August 16, 2001 10:48 AM
Ok, forgive me, I'm still learning. I found this custom header code on the board. What I want to do is specify a cell on a certain sheet as the header for ALL the sheets. I have the code, but have a question. I know that I need to define the Range, but how do I define a range and worksheet?
Would it be:
WorkSheet("Sheet 1").Range("A1") ??
This is the Code for reference:
Sub Cust_Header()
'Written by Barrie Davidson
Dim xls As Worksheet
For Each xls In ActiveWorkbook.Worksheets
wks.Activate
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Worksheet("Sheet 1").Range("A1").Value
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Next xls
End Sub
Posted by neo on August 16, 2001 10:53 AM
try sheets("sheet1").range("a1") or
sheet1.range("a1") where sheet1's tab would be named "sheet1"
neo
Posted by Rob on August 16, 2001 11:10 AM
Hmmm...This doesnt work , help!
I tried the code, but it didnt work, so I opened a blank sheet to make the range simply "A1", but it still didn't work. I linked this code to a button, and when I press it, I get an error "Object Required". Anyone Know whats going on?? Thanks...Heres the code:
Sub Cust_Header()
Dim xls As Worksheet
For Each xls In ActiveWorkbook.Worksheets
wks.Activate
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Sheets("Overview Analysis").Range("B1").Value
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Next xls
End Sub
Posted by Dax on August 16, 2001 12:02 PM
Re: Hmmm...This doesnt work , help!
Your problem is in the line:-
wks.Activate
Change it to xls.Activate so that you are actually referring to the worksheet in the loop.
Hope this helps,
Dax.
Posted by neo on August 16, 2001 12:13 PM
Re: Hmmm...This doesnt work , help!
it wasn't the syntax I gave you, it was the way the code was put together. try this:
Sub Cust_Header()
Sheet1.Activate
Dim xls As Worksheet
For Each xls In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.CenterHeader = Sheet1.Range("a1").Text
End With
On Error GoTo error1
ActiveSheet.Next.Select
Next xls
Exit Sub
error1: Exit Sub
End Sub
neo
Posted by Rob on August 16, 2001 12:25 PM
Thanks Dax
Posted by neo on August 16, 2001 2:06 PM
good shot, Dax!
i suspected that, but changed it incorrectly causing an error in other places. way to go...
neo
