Help with compiler error



I have the following code which is unfortunately giving me a "Compile Error :
Expected End With" and the only End With in the code is highlighted in yellow
when I select debug.

I am not a programmer by any strech of the imagination and any help would be

Sub EOM()
Dim Wks As Worksheet
Dim DestCell As Range
Dim newWks As Worksheet
Dim HeadersAreDone As Boolean
Dim mySelectedSheets As Object
Dim myRngToCopy As Range
Set mySelectedSheets = ActiveWindow.SelectedSheets
If mySelectedSheets.Count <> 3 Then
MsgBox "Please Group exactly 3 sheets before you run this macro!"
Exit Sub
End If
Set newWks = Workbooks.Add(1).Worksheets(1)
HeadersAreDone = False
For Each Wks In mySelectedSheets
With Wks
If HeadersAreDone = True Then
'do nothing
.Rows(1).Copy _
HeadersAreDone = True
Set DestCell = newWks.Range("a2")
End If
With Worksheets("sheet1")
.Unprotect password:=""
Set myRngToCopy = .Range("a2",
myRngToCopy.Columns.Count).Value _
= myRngToCopy.Value
.Protect password:=""
End With
With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
'Next Wks
'delete columns you don't want.
End With
End Sub

Bob Phillips

You have an End With missing. Your logic is

With Wks
With Worksheets("sheet1")
End With

With newWks
End With

as you can see 3 Withs, 2 End With's.

You also have an unterminated For Each Wks. There is an Next WKs, but it is
commented out.



(remove nothere from the email address if mailing direct)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question