Excel 201VB Issue ActiveSheet.Paste gets Compile Error "Expected Function or variable" - d 2

[NWildblood]

Hi - re-posting from MS Office forum any help gratefully received !

I have a very straightforward ms excel formatting macro which for reasons beyond my simple mind gets a Compile Error "Expected Function or variable".
I have run similar macro without a hitch for a long time, and may just be having an off day, but I'm stumped.

- Have just 2 days ago upgraded to 2010.

- The s/sheet is password protected, however have pasted into new workbook and am experiencing the same problem.)

Thanks in advance !

The code is here.

Sub FFUploadKGH()
'
' FFUploadKGH Macro
'

'
Range("A37706").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Columns("E:E").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "@"
Selection.Copy
Application.CutCopyMode = False
Columns("E:E").Select
Selection.Copy
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Range("F3").Select
Selection.AutoFilter
Range("F1").Select
Selection.Delete Shift:=xlToLeft
Range("E2:E8908").Select
Selection.ClearContents
Range("G4:G581").Select
Range(Selection, Selection.End(xlDown)).Select
Range("G4:G9921").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("D44414").Select
Range(Selection, Selection.End(xlDown)).Select
Range("D47054").Select
Selection.ClearContents
Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("J4:J7626").Select
Selection.Cut
Range("D4").Select
ActiveSheet.Paste
Range("H4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H4:H9291").Select
Selection.Cut
Range("G4").Select
ActiveSheet.Paste
Range("I4:M206").Select
Range(Selection, Selection.End(xlDown)).Select
Range("I4:M9336").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B5755")
Range("B4:B5755").Select
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=10
Range("B3").Select

MsgBox "Now clear duplicates"
End Sub

"No-one got everything done by Friday except Robinson Crusoe...

 

[PHV]

Which line of code is highlighted ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NWildblood]

Hi, sorry thought had put in bold

It's "ActiveSheet.Paste" - see below

Sub FFUploadKGH()
'
' FFUploadKGH Macro
'

'
Range("A37706").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Columns("E:E").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "@"
Selection.Copy
Application.CutCopyMode = False
Columns("E:E").Select
Selection.Copy
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Range("F3").Select
Selection.AutoFilter
Range("F1").Select
Selection.Delete Shift:=xlToLeft
Range("E2:E8908").Select
Selection.ClearContents
Range("G4:G581").Select
Range(Selection, Selection.End(xlDown)).Select
Range("G4:G9921").Select
Selection.Cut
Range("C4").Select
[highlight #3465A4]ActiveSheet.Paste[/highlight]
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("D44414").Select
Range(Selection, Selection.End(xlDown)).Select
Range("D47054").Select
Selection.ClearContents
Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("J4:J7626").Select
Selection.Cut
Range("D4").Select
ActiveSheet.Paste
Range("H4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H4:H9291").Select
Selection.Cut
Range("G4").Select
ActiveSheet.Paste
Range("I4:M206").Select
Range(Selection, Selection.End(xlDown)).Select
Range("I4:M9336").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B5755")
Range("B4:B5755").Select
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=10
Range("B3").Select

MsgBox "Now clear duplicates"
End Sub

"No-one got everything done by Friday except Robinson Crusoe...

 

[SkipVought]

The s/sheet is password protected

In order to run this kind of procedure in a protected sheet, you FIRST must UNPROTECT the sheet, then run the code then protect the sheet.

Of course, it would never have arrived at the statment in question had the sheet actually been protected.

try this...

[s]Range("G4:G9921").Select
Selection.Cut
Range("C4").Select
ActiveSheet.Paste[/s]

Range("G4:G9921").Cut
Range("C4").PasteSpecial xlpasteall

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NWildblood]

Thanks Skip, will check that out on Monday - have a good weekend.

"No-one got everything done by Friday except Robinson Crusoe...

 

[PHV]

As for cleaning your recorded code, what about this ?

Sub FFUploadKGH()
Range(Range("A3:D7706"), Range("A3:D7706").End(xlDown)).ClearContents
With Columns("E:E")
  .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
           SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
           ReplaceFormat:=False
  .NumberFormat = "@"
  .Copy
End With
Application.CutCopyMode = False
Columns("F:F").Insert Shift:=xlToRight
Range("F3").AutoFilter
Range("F1").Delete Shift:=xlToLeft
Range("E2:E8908").ClearContents
Range("G4:G9921").Cut
Range("C4").PasteSpecial xlPasteAll
With Columns("C:C")
  .TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
                 FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
  .NumberFormat = "dd/mm/yyyy;@"
End With
Range("D4:D7054").ClearContents
Range("J4:J7626").Cut
Range("D4").PasteSpecial xlPasteAll
Range("H4:H9291").Cut
Range("G4").PasteSpecial xlPasteAll
Range("I4:M9336").ClearContents
Range("B4").FormulaR1C1 = "=NOW()"
Range("B4").AutoFill Destination:=Range("B4:B5755")
With Columns("B:B")
  .NumberFormat = "m/d/yyyy"
  .Copy
  .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=10
Range("B3").Select

MsgBox "Now clear duplicates"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

also, you should not need to manually clear duplicates, as there is a feature in the Data tab that does that.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NWildblood]

Thanks guys, however am now getting the following error:

Run time error '1004': PasteSpecial method of Range class failed

- highlighted in bold below.

Have read around on this this morning but there doesn't seem to be anything conclusive...

Have checked that workbook and sheets are fully unprotected and
have tried pasting all the relevant data into a new s/sheet, still no avail....


--------------------

Sub FFUploadKGH_1()
Range(Range("A37706"), Range("A37706").End(xlDown)).ClearContents
With Columns("E:E")
.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.NumberFormat = "@"
.Copy
End With
Application.CutCopyMode = False
Columns("F:F").Insert Shift:=xlToRight
Range("F3").AutoFilter
Range("F1").Delete Shift:=xlToLeft
Range("E2:E8908").ClearContents
Range("G4:G9921").Cut
Range("C4").PasteSpecial xlPasteAll
With Columns("C:C")
.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
.NumberFormat = "dd/mm/yyyy;@"
End With
Range("D47054").ClearContents
Range("J4:J7626").Cut
Range("D4").PasteSpecial xlPasteAll
Range("H4:H9291").Cut
Range("G4").PasteSpecial xlPasteAll
Range("I4:M9336").ClearContents
Range("B4").FormulaR1C1 = "=NOW()"
Range("B4").AutoFill Destination:=Range("B4:B5755")
With Columns("B:B")
.NumberFormat = "m/d/yyyy"
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=10
Range("B3").Select

MsgBox "Now clear duplicates"
End Sub



"No-one got everything done by Friday except Robinson Crusoe...

 

[SkipVought]

The only other thing I'd suggest is to qualify the implied reference to a sheet to a specific sheet object. In fact I'd reference ALL your range objects to that sheet object like...

   With MySheetObject
'stuff before
      .Range("E2:E8908").ClearContents
      .Range("G4:G9921").Cut
      .Range("C4").PasteSpecial xlPasteAll
      With .Columns("C:C")
         .NumberFormat = "m/d/yyyy"
'stuff after      
   End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NWildblood]

Thanks for your help guys, resolved !

"No-one got everything done by Friday except Robinson Crusoe...