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
 
Sort by date Sort by votes
Apologies but I still get the same error message when I get to the .Apply

the code now reads

Code: 
MyLastRow = Range("e65536").End(xlUp).Row
    
    With ActiveWorkbook.Worksheets("Lookups")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("e1: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
    
    Columns("E:E").Select
    Selection.Copy
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
My fault - missed one in the SetRange statement:
Code: 
    With ActiveWorkbook.Worksheets("Lookups")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("e1: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
Lack of coffee on my part again:

Code: 
    With ActiveWorkbook.Worksheets("Lookups")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("e1:F" & MyLastRow _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With .Sort
    .SetRange ActiveWorkbook.Worksheets("Lookups").Range("E1:F" & MyLastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End With
 
Upvote 0
What's the value of MyLastRow when you get the error?
 
Upvote 0
Ah - just noticed that your sort key is two columns: you can't do that. Which column are you trying to sort on?
 
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,538
Messages
6,179,412
Members
452,912
Latest member
alicemil

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