Compile error: Expected end with

A

AlexMoth

New Member
Joined
Aug 23, 2011
Messages
10
When i try to run the following macro i get the follwoing error message "Compile error: Expected end with" and the "End Sub" is then highlighted. I have written many macros but this is the first time I have received this error message and even when I copy the code from this macro into a new macro I get the same message. Any help greatly appreciated

Sub Platform_Refresh()
'
' Platform_Refresh Macro
'
Dim MyLastRow As Long
Dim MyLastColumn As Long
Dim MyFirstBlankRow As Long
Dim MyLastCell As Range
Dim SiteRow As Range, cell As Object
Dim SiteCol As Range
Dim WSD1 As Worksheet
Dim WSD2 As Worksheet
Dim WSD3 As Worksheet
Dim WSD4 As Worksheet
Dim WSD5 As Worksheet
Dim WSD6 As Worksheet
Dim WSD7 As Worksheet
Dim WSD8 As Worksheet
Dim WSD9 As Worksheet
Dim WSD10 As Worksheet
Dim GroupRange As Range
Dim MyCell As Range
Set WSD1 = ActiveWorkbook.Worksheets("project sharepoint download")
Set WSD2 = ActiveWorkbook.Worksheets("project sharepoint consolidated")
Set WSD3 = ActiveWorkbook.Worksheets("current pfizer study list")
Set WSD4 = ActiveWorkbook.Worksheets("lookups")
Set WSD5 = ActiveWorkbook.Worksheets("Protocol Transitions")

WSD2.Activate

Cells.Select
Selection.ClearContents
Range("A1").Select

WSD1.Activate

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Columns("A:A").Select
Selection.Copy

WSD2.Activate

Range("a1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Columns("B:B").Select
ActiveSheet.Paste

WSD1.Activate

Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("C1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("D1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("E1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("Q:R").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("G1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("S:T").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("J1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("V:W").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("M1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("X:Y").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("P1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("AC:AC").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("S1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("AE:AF").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("U1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("AI:AJ").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("X1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("AL:AM").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

Range("AA1").Select
ActiveSheet.Paste

WSD1.Activate

Columns("AN:AO").Select
Application.CutCopyMode = False
Selection.Copy

WSD2.Activate

MyLastRow = Range("A65536").End(xlUp).Row

Range("AD1").Select
ActiveSheet.Paste

Range("I1").Select
ActiveCell.FormulaR1C1 = "RA Submission Date"

Range("L1").Select
ActiveCell.FormulaR1C1 = "RA Approval Date"

Range("O1").Select
ActiveCell.FormulaR1C1 = "Ethics Submission Date"

Range("R1").Select
ActiveCell.FormulaR1C1 = "Ethics Approval Date"

Range("W1").Select
ActiveCell.FormulaR1C1 = "First Site Initiated Date"

Range("Z1").Select
ActiveCell.FormulaR1C1 = "FSFV Date"

Range("AC1").Select
ActiveCell.FormulaR1C1 = "LSFV Date"

Range("AF1").Select
ActiveCell.FormulaR1C1 = "LSLV Date"

Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("I2").Select
Selection.Copy
Range("I2:I" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("L2").Select
Selection.Copy
Range("L2:L" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("O2").Select
Selection.Copy
Range("O2:O" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("R2").Select
Selection.Copy
Range("R2:R" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("W2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("W2").Select
Selection.Copy
Range("W2:W" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("Z2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("Z2").Select
Selection.Copy
Range("Z2:Z" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AC2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("AC2").Select
Selection.Copy
Range("AC2:AC" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AF2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"

Range("AF2").Select
Selection.Copy
Range("AF2:AF" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AF:AF,AC:AC,Z:Z,W:W,R:R,O:O,L:L,I:I").Select
Range("I1").Activate
Selection.NumberFormat = "d-mmm-yy"

Range("Ag1").Select
ActiveCell.FormulaR1C1 = "Study End Date"

Range("AG2").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+2,DAY(RC[-1]))"

Range("AG2").Select
Selection.Copy
Range("AG2:AG" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Columns("C:C").Select
Selection.NumberFormat = "General"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(or(rc[1]=""protocol feasibility"",RC[1]=""dropped""),0,RC[-1])"
Range("C2").Select
Selection.Copy
Range("C2:C" & MyLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select

Range("A1").Select

WSD4.Activate

Range("e1").Select

WSD2.Activate

Columns("A:A").Select
Selection.Copy

WSD4.Activate

ActiveSheet.Paste
Range("F1").Select

WSD2.Activate

Columns("AG:AG").Select
Application.CutCopyMode = False
Selection.Copy

WSD4.Activate

ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:F").Select
Application.CutCopyMode = False

MyLastRow = Range("e65536").End(xlUp).Row

ActiveWorkbook.Worksheets("Lookups").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lookups").Sort.SortFields.Add Key:=Range("e2:F" & MyLastRow _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lookups").Sort
.SetRange Range("E1:F" & MyLastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Columns("E:E").Select
Selection.Copy

Columns("H:H").Select
ActiveSheet.Paste

Columns("H:H").Select
Application.CutCopyMode = False

MyLastRow = Range("h65536").End(xlUp).Row

ActiveSheet.Range("$H1:$H" & MyLastRow).RemoveDuplicates Columns:=1, Header:=xlYes

Range("I1").Select
ActiveCell.FormulaR1C1 = "current end date"

Range("J1").Select
ActiveCell.FormulaR1C1 = "Transition End Date"

Range("K1").Select
ActiveCell.FormulaR1C1 = "End Date"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-1],C[-4]:C[-3],2,0)),0,(VLOOKUP(RC[-1],C[-4]:C[-3],2,0)))"

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],'Protocol Transitions'!R5C1:R29C2,2,0)),0,(VLOOKUP(RC[-2],'Protocol Transitions'!R5C1:R29C2,2,0)))+IF(ISNA(VLOOKUP(RC[-2],'Protocol Transitions'!R5C3:R29C4,2,0)),0,(VLOOKUP(RC[-2],'Protocol Transitions'!R5C3:R29C4,2,0)))+IF(ISNA(VLOOKUP(RC[-2],'Protocol Transitions'!R5C5:R29C6,2,0)),0,(VLOOKUP(RC[-2],'Protocol Transitions'!R5C5:R29C6,2,0)))+IF(ISNA(VLOOKUP(RC[-2],'Protocol Transitions'!R5C7:R29C8,2,0)),0,(VLOOKUP(RC[-2],'Protocol Transitions'!R5C7:R29C8,2,0)))+IF(ISNA(VLOOKUP(RC[-2],'Protocol Transitions'!R5C9:R29C10,2,0)),0,(VLOOKUP(RC[-2],'Protocol Transitions'!R5C9:R29C10,2,0)))"

Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],MIN(RC[-2]:RC[-1]))"

MyLastRow = Range("h65536").End(xlUp).Row

Range("I2:K2").Select
Selection.Copy

Range("I3:k" & MyLastRow).Select
ActiveSheet.Paste

Range("I2").Select
Application.CutCopyMode = False

WSD1.Activate

Range("a1").Select

'
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Add 'End With' after the .Apply line of the Sort part of the code.
 
Upvote 0
Welcome to the Board!

It looks like you have a WITH statement that is missing the END WITH statement, i.e.
Code: 
With ActiveWorkbook.Worksheets("Lookups").Sort
    .SetRange Range("E1:F" & MyLastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
[B][COLOR=red]End With[/COLOR][/B]
 
Upvote 0
You need to add
Code: 
End With
between these two lines:
Code: 
.Apply

Columns("E:E").Select

PLEASE learn to use code tags.
 
Upvote 0
Where exactly did you put the End With?

It should go after .Apply.
Code: 
With ActiveWorkbook.Worksheets("Lookups").Sort
   .SetRange Range("E1:F" & MyLastRow)
   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
End With
 
Upvote 0
the code now reads

ActiveWorkbook.Worksheets("Lookups").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lookups").Sort.SortFields.Add Key:=Range("e2:F" & MyLastRow _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lookups").Sort
.SetRange Range("E1:F" & MyLastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

but this gets the debug issue with the .Apply
 
Upvote 0
What is the exact error code and message it is returning?
 
Upvote 0
The exact error I get is

"Run-time error '1004':
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank"

i have used this code before to do the same thing and have not got this message
 
Upvote 0
You haven't specified a sheet for the Range. Change that code to:
Code: 
With ActiveWorkbook.Worksheets("Lookups")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("e2:F" & MyLastRow _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("E1:F" & MyLastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
201
MARK858
MARK858
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
168
Alex Blakenburg
A
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
461
Sean15
Sean15
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
351
RoryA
RoryA
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
330
MARK858
MARK858

Forum statistics

Threads
1,224,527
Messages
6,179,351
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top