Dear all,

I have the following code, I just can't get my head around this, I'm more use too writing code in Access which i so much easier than excel

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Range("A1").Value = "Field01" Then ws.Range("A1").EntireRow.Delete

With ws
.Columns("A:K").AutoFit

With .Columns("D:D")

.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ws.Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
ws.Range("D1").Select
ws.Range(Selection, Selection.End(xlDown)).Select
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
ws.Application.CutCopyMode = False

With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Selection.NumberFormat = "0.00"
ws.Range("A1").Select
Selection.ClearContents

With .Rows("1:1")

.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End With

Next ws

End Sub

The bit I am having problems with is

ws.Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
ws.Range("D1").Select
ws.Range(Selection, Selection.End(xlDown)).Select
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
ws.Application.CutCopyMode = False

With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Selection.NumberFormat = "0.00"
ws.Range("A1").Select
Selection.ClearContents

I'm just not sure how to incorporate it into my code, so that it makes the changes to each worksheet as it lops through.

Thanks is advance for your assistance
Geoff

Hi, Geoff,

the code looped without problems when I used it on an workbook in Excel97. Some parts could be shortened depending on the contents of your workbook - this seems to be copied from the macro recorder (and some of those items aren´t needed).

Ciao,
Holger