END IF WITHOUT BLOCK IF VBA Problems

[bloodmilksky]

Hi Guys, I hope you are all well

I have written the below macro with some help. it basically copies the Values from row 8 sheet 1 in to row one of all the active sheets. which works perfectly the problem I am having is "wrapping text" for each active sheet. I have tried adding this on to the end of the Sub and comes back with END IF WITHOUT BLOCK IF.

can anyone help please

Many thanks

Jamie




Sub Treat()
Dim WS As Worksheet
For Each WS In Worksheets
If (WS.Name <> "Sheet1") Then
Sheets("Sheet1").Rows("8:8").Copy Destination:=WS.Range("A1")
WS.Select
Selection.Columns("A:A").ColumnWidth = 2.56
Selection.Columns("B:B").ColumnWidth = 10
Selection.Columns("C:C").ColumnWidth = 9.22
Selection.Columns("D:D").ColumnWidth = 20.78
Selection.Columns("E:E").ColumnWidth = 63
Selection.Columns("F:F").ColumnWidth = 46
Selection.Columns("G:G").ColumnWidth = 46
Selection.Columns("H:H").ColumnWidth = 7.11
Selection.Columns("I:I").ColumnWidth = 11.44
WS.Select
Columns.EntireColumn.AutoFit
Rows.EntireRow.AutoFit
Sheets("Sheet1").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet2").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet3").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet4").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet5").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet6").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet7").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet8").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet9").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet10").Select
Cells.Select
With Selection
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet11").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End If
Next






End Sub

 

[ukmikeb]

Hi

You have several duplicated With Selection statements with only one End With statement!

hth

 

[bloodmilksky]

Is there a way do you think that I could just "wrap text" for the work book instead of doing it sheet by sheet to avoid this error?

 

[Trevor G]

Create an array for the sheet names you want to wrap text and use the array in your code. You can record a macro and then select each sheet name this creates the array do the wrap and you have your answer.

 

[ukmikeb]

Is there a way do you think that I could just "wrap text" for the work book instead of doing it sheet by sheet to avoid this error?

Every "With Selection" statement has to be paired with an "End With" statement.

The error is caused by the fact that you have several single "With Selection" statements that are unpaired.

hth

 

[Lobsterboy1]

Do you want it to copy to all your sheets or just up to Sheet11 ? if its all Sheets this should do it.

Sub Treat()
Dim WS As Worksheet
For Each WS In Worksheets
If (WS.Name <> "Sheet1") Then
Sheets("Sheet1").Rows("8:8").Copy Destination:=WS.Range("A1")
WS.Select
Selection.Columns("A:A").ColumnWidth = 2.56
Selection.Columns("B:B").ColumnWidth = 10
Selection.Columns("C:C").ColumnWidth = 9.22
Selection.Columns("D:D").ColumnWidth = 20.78
Selection.Columns("E:E").ColumnWidth = 63
Selection.Columns("F:F").ColumnWidth = 46
Selection.Columns("G:G").ColumnWidth = 46
Selection.Columns("H:H").ColumnWidth = 7.11
Selection.Columns("I:I").ColumnWidth = 11.44
WS.Select
Columns.EntireColumn.AutoFit
Rows.EntireRow.AutoFit
Sheets.Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


End If


Next


End Sub

 

[Lobsterboy1]

Or if its just Sheet 2 to Sheet 11

Sub Treat()
Dim WS As Worksheet
For Each WS In Worksheets
If (WS.Name <> "Sheet1") Then
Sheets("Sheet1").Rows("8:8").Copy Destination:=WS.Range("A1")
WS.Select
Selection.Columns("A:A").ColumnWidth = 2.56
Selection.Columns("B:B").ColumnWidth = 10
Selection.Columns("C:C").ColumnWidth = 9.22
Selection.Columns("D:D").ColumnWidth = 20.78
Selection.Columns("E:E").ColumnWidth = 63
Selection.Columns("F:F").ColumnWidth = 46
Selection.Columns("G:G").ColumnWidth = 46
Selection.Columns("H:H").ColumnWidth = 7.11
Selection.Columns("I:I").ColumnWidth = 11.44
WS.Select
Columns.EntireColumn.AutoFit
Rows.EntireRow.AutoFit
Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11")).Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


End If


Next


End Sub

 

[bloodmilksky]

yeah it was for the whole workbook and that is awesome thank you as Il have literally been at the point of pulling out my hair for the last half hour