# [SOLVED] Formatting excel spreadsheet: need a VBA script



## kapo (Aug 22, 2007)

What I have so far is a spreadsheet of 1200 pages and the first row at the top of each page is blank. I ran a macro to insert the same text in the blank row at the the top of the first page with some formatting for the rest of the page. What I can't figure out is how to loop it so that the text at the top row along with formatting are applied to the other 1200 or so pages.


here is the macro to insert text into the blank row and formatting the first page:

```
Sub format()
' Keyboard Shortcut: Ctrl+z
'
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    Windows("Copy_of_rest.xls").Activate
    Selection.Copy
    Windows("test.xls").Activate
    ActiveSheet.Paste
    ActiveCell.Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
.
.
.
same for top, bottom and right
    ActiveCell.Range("A1:K37").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
.
.
.
same for right, top and bottom
        ActiveCell.Select
End Sub
```


----------



## Glaswegian (Sep 16, 2005)

*Re: Formatting excel spreadsheet: need a VBA script*

Hi

Some questions for you...

Are you copying any data or is it just formatting?
There are two workbooks mentioned in your code - which one should the code run on?
What was being copied and pasted?

The actual looping is straightforward.


----------



## kapo (Aug 22, 2007)

*Re: Formatting excel spreadsheet: need a VBA script*

Thanks for your response. To answer your questions:
No data is being copied, just text and formatting
The code should apply for sheet test.xls
Text is being copied and pasted from the first row in columns A to K which give descriptions of each columns contents.


----------



## Glaswegian (Sep 16, 2005)

*Re: Formatting excel spreadsheet: need a VBA script*

Hi again

This will work, although bear in mind that XLEdgeLeft will not show up very well. The macro recorder is good at showing you the objects and methods required, but it does produce a great deal of superfluous code. The loop is simply a For Each...Next - in this case, For Each Sheet in the workbook, do this etc...

Note that I've simply called the first sheet "Sheet1" - change that as required and remember to change it in both instances. This simply ensures that Sheet1 is not touched by the code.

```
Sub format()
' Keyboard Shortcut: Ctrl+z
'
Dim Wbk As Workbook
Dim Sht As Worksheet
Dim myRng As Range

Set Wbk = Workbooks("test.xls")

Set myRng = Wbk.Sheets("Sheet1").Range("A1:K1")

For Each Sht In Wbk.Worksheets
    If Sht.Name <> "Sheet1" Then
        myRng.Copy Destination:=Sht.Range("A1")
            With Sht.Range("A1:K1").Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                With Sht.Range("A1:K37").Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                End With
            End With
    End If
Next Sht
End Sub
```
Post back with any queries or changes you may need.


----------



## kapo (Aug 22, 2007)

*Re: Formatting excel spreadsheet: need a VBA script*

I tried this but it formats only the left and right edges.

The way I went about it was to first paste the text in range A1:K1 along with the formatting of that row. Then I ran the script but it only formatted the left and right edges of the entire document.


----------



## Glaswegian (Sep 16, 2005)

*Re: Formatting excel spreadsheet: need a VBA script*

I thought that - what parts of each cell do you want to format?


----------



## kapo (Aug 22, 2007)

*Re: Formatting excel spreadsheet: need a VBA script*

I want to format the first row of every page from columns A to K, the top, bottom, left and right edges of that row are formatted the same way as the left and right edges are right now then I want to paste characters in each cell from A to K.


----------



## Glaswegian (Sep 16, 2005)

*Re: Formatting excel spreadsheet: need a VBA script*

Hi again

Try this

```
Sub format()
' Keyboard Shortcut: Ctrl+z
'
Dim Wbk As Workbook
Dim Sht As Worksheet
Dim myRng As Range

Set Wbk = Workbooks("test.xls")

Set myRng = Wbk.Sheets("Sheet1").Range("A1:K1")

For Each Sht In Wbk.Worksheets
    If Sht.Name <> "Sheet1" Then
        myRng.Copy Destination:=Sht.Range("A1")
            With Sht.Range("A1:K1").Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
    End If
Next Sht

End Sub
```


----------



## kapo (Aug 22, 2007)

*Re: Formatting excel spreadsheet: need a VBA script*

I tried this but I get an error: runtime error 9, subscript out of range.


----------



## Glaswegian (Sep 16, 2005)

*Re: Formatting excel spreadsheet: need a VBA script*

Did you change the sheet names to match those of your own workbook?


----------



## kapo (Aug 22, 2007)

*Re: Formatting excel spreadsheet: need a VBA script*

It's OK now. I figured it out.


----------

