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
The bit I am having problems with is
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
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
Code:
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
Code:
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