Newbie questions

Hi everyone,
My company is in the process of converting over from Quattro Pro to Excel,
so I have a few newbie questions.

1. How do I set a default footer that is printed with every page of a
spreadsheet? I want to have the path and filename, tab name, and date at
the bottom of each page of a printout. I know how to set them for a
particular spreadsheet, but how do I set it up so it is automatically
included with every new spreadsheet?

2. How do I set the default page setup so that the scaling is always "print
to 1 page wide by 1 page tall", and margins are all 0.5inches all around?

3. Is there a way to view all the formulas in the cells, rather than the
results of the formulas?

4. Is there a way to print a spreadsheet as a list of the cell contents?
This is like printing an index of cell addresses and what they contain. And
yes, this is different from question 3 above.

Thank you,
Rob

#1&#2.

You can create a template file that has the page layouts you like.

Start a new workbook. Do all the pagesetups you like (and anything else you
like) for each worksheet in that workbook.

Save it as a template file named book.xlt in your XLStart folder.

Each time you click on the new icon, that new workbook will inherit all the
settings from that template file.

If you want new worksheets added to existing workbooks to have these same
settings, set up another workbook template file (one sheet only???) and name it
sheet.xlt. Save it in the same XLStart folder.

#3. You can use tools|options|view tab|check/uncheck Formulas
ctrl-` (control-backquote -- it's the key to the left of 1/! on my USA
keyboard is the shortcut key for this.

#4. You can use a macro to cycle through each cell in the used area and copy
the value/address/formulas to a new worksheet. This is pretty unusual. Are you
sure you really need it.

Option Explicit
Sub testme()

Dim ActWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim oCol As Long
Dim myCell As Range

Set ActWks = ActiveSheet
Set NewWks = Workbooks.Add(1).Worksheets(1)

oRow = 1
oCol = 1
With ActWks
For Each myCell In .UsedRange.Cells
If IsEmpty(myCell) Then
'skip it
Else
With NewWks.Cells(oRow, "A")
.Value = myCell.Address(0, 0)
.Offset(0, 1).Value = "'" & myCell.Formula
.Offset(0, 2).Value = "'" & myCell.Text
End With
If oRow = .Rows.Count Then
oRow = 1
oCol = oCol + 3
Else
oRow = oRow + 1
End If
End If

Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Rob wrote:
>
> Hi everyone,
> My company is in the process of converting over from Quattro Pro to Excel,
> so I have a few newbie questions.
>
> 1. How do I set a default footer that is printed with every page of a
> spreadsheet? I want to have the path and filename, tab name, and date at
> the bottom of each page of a printout. I know how to set them for a
> particular spreadsheet, but how do I set it up so it is automatically
> included with every new spreadsheet?
>
> 2. How do I set the default page setup so that the scaling is always "print
> to 1 page wide by 1 page tall", and margins are all 0.5inches all around?
>
> 3. Is there a way to view all the formulas in the cells, rather than the
> results of the formulas?
>
> 4. Is there a way to print a spreadsheet as a list of the cell contents?
> This is like printing an index of cell addresses and what they contain. And
> yes, this is different from question 3 above.
>
> Thank you,
> Rob

--

Dave Peterson

Hi Dave,

Thank you for your answers. That helps a lot.
Regarding #4, we use this as part of our documentation during spreadsheet
validations. With Quattro Pro it was very simple --- just check a box under
Print|Page setup|Options, and it would print a list of all cells used in a
sheet and their contents if they contained text or numbers, or the formulas
including cell addresses if they contained formulas. It made it really easy
to document the validation of spreadsheets if they are used to report
results to an external customer.

Thanks again,
Rob

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> #1&#2.
>
> You can create a template file that has the page layouts you like.
>
> Start a new workbook. Do all the pagesetups you like (and anything else
> you
> like) for each worksheet in that workbook.
>
> Save it as a template file named book.xlt in your XLStart folder.
>
> Each time you click on the new icon, that new workbook will inherit all
> the
> settings from that template file.
>
> If you want new worksheets added to existing workbooks to have these same
> settings, set up another workbook template file (one sheet only???) and
> name it
> sheet.xlt. Save it in the same XLStart folder.
>
> #3. You can use tools|options|view tab|check/uncheck Formulas
> ctrl-` (control-backquote -- it's the key to the left of 1/! on my USA
> keyboard is the shortcut key for this.
>
> #4. You can use a macro to cycle through each cell in the used area and
> copy
> the value/address/formulas to a new worksheet. This is pretty unusual.
> Are you
> sure you really need it.
>
> Option Explicit
> Sub testme()
>
> Dim ActWks As Worksheet
> Dim NewWks As Worksheet
> Dim oRow As Long
> Dim oCol As Long
> Dim myCell As Range
>
> Set ActWks = ActiveSheet
> Set NewWks = Workbooks.Add(1).Worksheets(1)
>
> oRow = 1
> oCol = 1
> With ActWks
> For Each myCell In .UsedRange.Cells
> If IsEmpty(myCell) Then
> 'skip it
> Else
> With NewWks.Cells(oRow, "A")
> .Value = myCell.Address(0, 0)
> .Offset(0, 1).Value = "'" & myCell.Formula
> .Offset(0, 2).Value = "'" & myCell.Text
> End With
> If oRow = .Rows.Count Then
> oRow = 1
> oCol = oCol + 3
> Else
> oRow = oRow + 1
> End If
> End If
>
> Next myCell
> End With
>
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Rob wrote:
>>
>> Hi everyone,
>> My company is in the process of converting over from Quattro Pro to
>> Excel,
>> so I have a few newbie questions.
>>
>> 1. How do I set a default footer that is printed with every page of a
>> spreadsheet? I want to have the path and filename, tab name, and date at
>> the bottom of each page of a printout. I know how to set them for a
>> particular spreadsheet, but how do I set it up so it is automatically
>> included with every new spreadsheet?
>>
>> 2. How do I set the default page setup so that the scaling is always
>> "print
>> to 1 page wide by 1 page tall", and margins are all 0.5inches all around?
>>
>> 3. Is there a way to view all the formulas in the cells, rather than the
>> results of the formulas?
>>
>> 4. Is there a way to print a spreadsheet as a list of the cell contents?
>> This is like printing an index of cell addresses and what they contain.
>> And
>> yes, this is different from question 3 above.
>>
>> Thank you,
>> Rob
>
> --
>
> Dave Peterson