macro to change Custom Header info

[Phil Wagner]

I have a 12 worksheet - spreadsheet that has an existing Custom Header in 9
worksheets (tabs).

I'd like to have a macro that takes the user to one of the worksheet's
Custom Header (Left Section), then allows the user to change ONLY the 1st
and 3rd lines in the header.

The 1st line currently says: Station/Facility: SMA 42-408.
I want to be able to change ONLY the SMA 42-408 part of the line.

The 3rd line says: Month/Year: July, 2008
I'd like to be able to change ONLY the July, 2008 part of the line.


This is what the Custom Header Left Section currently looks like:

Station/Facility: SMA 42-408
Parameter: BAM PM10 (UG/M3)
Month/Year: July, 2008

Further, I'd like the macro to change this edited header info (from the 1st
and 3rd lines) in 9 of 12 worksheets in the spreadsheet.

The worksheets I'd like the header info edited are:

Conc(mg)
Qtot(m3)
WS(KTS)
WD(DEG)
ROOM(C)
IN RH(%)
OT RH(%)
AT(C)
ERRORS

Is this possible?

Thanks!

 

[Bob Phillips]

Dim Var1 As String
Dim Var2 As String

Var1 = "SMA 42-408"
Var2 = "July, 2008"
Worksheets (Array("Conc (mg)", "Qtot (m3)", "WS (KTS)", _
"WD (DEG)", "ROOM (C)", "IN RH(%)", _
"OT RH(%)", "AT (C)", "Errors"))
With Worksheets("Conc (mg)").PageSetup

.LeftHeader = "Station/Facility: " & Var1 & Chr(10) & _
"Parameter: BAM PM10(UG / M3)" & Chr(10) & _
"Month/Year: " & Var2
End With

 

[Phil Wagner]

Bob - I ran the macro and found a couple problems:

1. I got a Compile error: Invalid use of property with the 1st Worksheets
highlighted.

I edited the worksheet titles eliminating spaces not used in the
worksheet names hoping that would fix it , but it didn't.

2. Upon further investigation I found the 2nd line in each header was
different. I'd like to keep the 2nd line in each worksheets header intact.

3. The 3rd line of the headers have Month/Year: &[File] and not Month/Year:
BAM PM10(UG/M3) as I indicated originally.

See my edits below... Your macro follows the macro I edited.

So, given these final constraints, can you finish the macro?

Thanks!


Dim Var1 As String
Dim Var2 As String

Var1 = "SMA 42-408"
Var2 = "&[File]"
Worksheets (Array("Conc(mg)", "Qtot(m3)", "WS(KTS)", _
"WD(DEG)", "ROOM(C)", "IN RH(%)", _
"OT RH(%)", "AT(C)", "ERRORS"))
With Worksheets("Conc(mg)").PageSetup

.LeftHeader = "Station/Facility: " & Var1 & Chr(10) & _
"Parameter: BAM PM10(UG/M3)" & Chr(10) & _
"Month/Year: " & Var2
End With




----- Original Message -----
From: "Bob Phillips" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Monday, August 11, 2008 12:42 AM
Subject: Re: macro to change Custom Header info

Dim Var1 As String
Dim Var2 As String

Var1 = "SMA 42-408"
Var2 = "July, 2008"
Worksheets (Array("Conc (mg)", "Qtot (m3)", "WS (KTS)", _
"WD (DEG)", "ROOM (C)", "IN RH(%)", _
"OT RH(%)", "AT (C)", "Errors"))
With Worksheets("Conc (mg)").PageSetup

.LeftHeader = "Station/Facility: " & Var1 & Chr(10) & _
"Parameter: BAM PM10(UG / M3)" & Chr(10) & _
"Month/Year: " & Var2
End With


--
__________________________________
HTH

Bob

I have a 12 worksheet - spreadsheet that has an existing Custom Header in
9 worksheets (tabs).

I'd like to have a macro that takes the user to one of the worksheet's
Custom Header (Left Section), then allows the user to change ONLY the 1st
and 3rd lines in the header.

The 1st line currently says: Station/Facility: SMA 42-408.
I want to be able to change ONLY the SMA 42-408 part of the line.

The 3rd line says: Month/Year: July, 2008
I'd like to be able to change ONLY the July, 2008 part of the line.


This is what the Custom Header Left Section currently looks like:

Station/Facility: SMA 42-408
Parameter: BAM PM10 (UG/M3)
Month/Year: July, 2008

Further, I'd like the macro to change this edited header info (from the
1st and 3rd lines) in 9 of 12 worksheets in the spreadsheet.

The worksheets I'd like the header info edited are:

Conc(mg)
Qtot(m3)
WS(KTS)
WD(DEG)
ROOM(C)
IN RH(%)
OT RH(%)
AT(C)
ERRORS

Is this possible?

Thanks!

--
---
Phil

Dim Var1 As String
Dim Var2 As String

Var1 = "SMA 42-408"
Var2 = "July, 2008"
Worksheets (Array("Conc (mg)", "Qtot (m3)", "WS (KTS)", _
"WD (DEG)", "ROOM (C)", "IN RH(%)", _
"OT RH(%)", "AT (C)", "Errors"))
With Worksheets("Conc (mg)").PageSetup

.LeftHeader = "Station/Facility: " & Var1 & Chr(10) & _
"Parameter: BAM PM10(UG / M3)" & Chr(10) & _
"Month/Year: " & Var2
End With