Pivot Vba...Why VBA Stuck on just Selection.copy ..Compile Error-"Expected Function Or Variable"

Chirag R Raval · Oct 3, 2017

Dear Experts,

Subject :- " Why VBA Stuck on just Selection.copy ..Compile Error-"Expected Function Or Variable"

I have Picot Table Code as below...but error on "Selection" "Compile Error"
""Expected Function or Variable " on following line

"ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
selection.Copy

I also uplod & attached my database file on which this code should be run...for your reference....

whole oiginal code is here...

Code:

Sub Shirting_book_Summary()
Dim PCache As PivotCache
Dim LastRow As Long
Dim pt As PivotTable
  'If "Pivot" worksheet already exists, delete it
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot").Delete
On Error GoTo 0
Application.DisplayAlerts = True
 
Worksheets("Sheet1").Activate
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=range("A1").CurrentRegion.Address)
    Worksheets.add
    ActiveSheet.Name = "Pivot"
    ActiveWindow.DisplayGridlines = False
Set pt = ActiveSheet.PivotTables.add(PivotCache:=PCache, TableDestination:=range("A1"), TableName:="PivotTable1")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("AR")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "UNT"), "Sum of CONF" & Chr(10) & "UNT", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "QTY"), "Sum of CONF" & Chr(10) & "QTY", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("VALUE"), "Sum of VALUE", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields("AR").LayoutForm = xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").LayoutForm = _
        xlTabular
       
      ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
     
    selection.Copy
    Sheets.add
    ActiveSheet.Name = "Summary"
 
    selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        End With
    Columns("E:G").Select
    Application.CutCopyMode = False
    selection.NumberFormat = "##,###"
    Cells.Select
    Cells.EntireColumn.AutoFit
    range("A1").Select
    ActiveCell.FormulaR1C1 = "AR"
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Rows("1:1").Select
    selection.Replace What:="SUM OF ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    range("E1").Select
    ActiveCell.FormulaR1C1 = "CONF" & Chr(10) & "UNT"
    range("F1").Select
    ActiveCell.FormulaR1C1 = "CONF" & Chr(10) & "QTY"
    range("G1").Select
    ActiveCell.FormulaR1C1 = "VALUE"
    Rows("2:2").EntireRow.Delete
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("A:G").Select
    With selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("D:D").Select
    With selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("E:G").Select
    With selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    range("A1").Activate
range(ActiveCell, ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell)).Select
    selection.Borders(xlDiagonalDown).LineStyle = xlNone
    selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Cells.Select
    With selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    range("A1").Select
'    Sheets("Sheet1").Select
'    ActiveWindow.SelectedSheets.Delete
   
    End With
End Sub

What VBA Want??...Why VBA stuck on just on minor Selection word...?

hope your co-operations & help will always be appreciated..

Regards,
Chirag Raval

Luke M · Oct 3, 2017

It's a little odd that the compiler didn't auto-capitalize the word Selection, as it should be a key word. Do you accidentally have a variable defined somewhere as "selection"?

Chirag R Raval · Oct 3, 2017

Dear Sir @Luke M ,

Thanks for response...

for this colde only...i not declare anywhere "selection" as variable...

Actually i run this code through Personel.xlsb...there are many macros...

so i must check whole my personel.xlsb for this? that where i declare
"selection" as variable"?? how can i do this...please guide...

hope your little help..

Regards,
Chirag Raval

chirayu · Oct 3, 2017

I got this error when I tried to run your code

chirayu · Oct 3, 2017

You also need to remove the End With argument at the end of the macro. Should fix it. along with the one shown above

Chirag R Raval · Oct 3, 2017

Dear sir @chirayu ,

Thanks...thats will be another future error matter...(How can outcome from your face error?)

But i feel wonder that why you not face my described error first?

i search in my whole personel.xlsb project for "selection as"

i got this in one module as below..

Code:

Private Sub SaveAttachments()
Dim objOL As Outlook.Application
Dim objMsg As Outlook.MailItem 'Object
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.selection
Dim i As Long
Dim lngCount As Long
Dim strFile As String
Dim strFolderpath As String
Dim strDeletedFiles As String

can this module Interrupt my current code??

Please focus..

Regards,
Chirag Raval

Chirag R Raval · Oct 3, 2017

Der Sir @chirayu ..

i remove "End With" but "Compile Error-"Expected Function Or Variable"
as it is..

Regards,
CHirag Raval

chirayu · Oct 3, 2017

I've removed both end with arguments. this is the code that works

Code:

Sub Shirting_book_Summary()
Dim PCache As PivotCache
Dim LastRow As Long
Dim pt As PivotTable
  'If "Pivot" worksheet already exists, delete it
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot").Delete
On Error GoTo 0
Application.DisplayAlerts = True
 
Worksheets("Sheet1").Activate
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
    Worksheets.Add
    ActiveSheet.Name = "Pivot"
    ActiveWindow.DisplayGridlines = False
Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("A1"), TableName:="PivotTable1")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("AR")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "UNT"), "Sum of CONF" & Chr(10) & "UNT", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "QTY"), "Sum of CONF" & Chr(10) & "QTY", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("VALUE"), "Sum of VALUE", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields("AR").LayoutForm = xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").LayoutForm = _
        xlTabular
       
      ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
     
    Selection.Copy
    Sheets.Add
    ActiveSheet.Name = "Summary"
 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Columns("E:G").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "##,###"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "AR"
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Rows("1:1").Select
    Selection.Replace What:="SUM OF ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "CONF" & Chr(10) & "UNT"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "CONF" & Chr(10) & "QTY"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "VALUE"
    Rows("2:2").EntireRow.Delete
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("A:G").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("E:G").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Activate
Range(ActiveCell, ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
'    Sheets("Sheet1").Select
'    ActiveWindow.SelectedSheets.Delete
 
End Sub

Chirag R Raval · Oct 3, 2017

Dear Sir @chirayu .

Thanks for that ....i copy paste it...& may be "with" "End with" matter resolved" ... but it still stuck on "Selection.Copy"
"Compile Error-"Expected Function Or Variable"
as it is..

Can you do something for this?
hope help ..

Regards,
Chirag Raval

chirayu · Oct 3, 2017

I can only assume that selection has been defined somewhere as a Public variable in your personal xlsb. Only way to check if to go through each macro & ensure it is not defined somewhere. Would look like

"Selection =" or "Public Selection As"

Chirag R Raval · Oct 3, 2017

Dear Sir @chirayu

Please refer my post no (# 6) above..
can that module Interrupt my current code??

Regards,
Chirag Raval

Debaser · Oct 3, 2017

It is, I think, more likely that you have a module or subroutine named selection. If you had a variable named selection, you wouldn't get that error.

Chirag R Raval · Oct 3, 2017

Dear Sirs @Debaser & @chirayu

as my post no (#6) , i have 1 macro containing that types of
matter where "Dim objSelection As Outlook.selection".

i already change "Dim objSelection As Outlook.selection"
as "Dim objSelect As Outlook.selection" & make also change it "Nothing" parameter..

but till face this error...

Regards,
Chirag Raval

Debaser · Oct 3, 2017

As I said, you probably have a module or subroutine named 'selection'. Having a variable named 'selection' is unlikely to give you an "expected function or variable" error.

Chihiro · Oct 3, 2017

You can always use method other than select...

To copy PivotTable Data & Label range (excluding filer page items).

Code:

ActiveSheet.PivotTables("PivotTable1").TableRange1.Copy

At any rate, as others have indicated, search through all module and locate if there are any inappropriate use of "Selection".

Chirag R Raval · Oct 3, 2017

Dea All,

thanks for your effort for outcome me from this..

my office is closed now..

i will be back tomorow...

again thanbks & will revert bak..

Regards,

Chirag Raval

Chirag R Raval · Oct 3, 2017

Dear all Experts,

Till now I learned that
(1) Macro or sub name or variable or expressions
Must not contain words that used by Vba..

(2) Always try to overcome from selection anything
You can direct refer things for processing..

I will be back after try my best ..in my personal. Xlsb
Try my best to search & also try as per valuable guidance
From respected experts like Mr. Chihiro's tips..

Thanks to you all..

Regards,

Chirag Raval

Chirag R Raval · Oct 4, 2017

Dear Sir,

Yes....Got it....there...are..i have 1 Macro Sub name have word "Selection" change it...& this macro working now....

Many thanks to you all experts for your support to resolve this thread...

Many thanks to this site for support in codes...

Regards,
Chirag Raval

Pivot Vba...Why VBA Stuck on just Selection.copy ..Compile Error-"Expected Function Or Variable"

Chirag R Raval

Member

Attachments

Luke M

Excel Ninja

Chirag R Raval

Member

chirayu

Well-Known Member

chirayu

Well-Known Member

Chirag R Raval

Member

Chirag R Raval

Member

chirayu

Well-Known Member

Chirag R Raval

Member

chirayu

Well-Known Member

Chirag R Raval

Member

Debaser

Well-Known Member

Chirag R Raval

Member

Debaser

Well-Known Member

Chihiro

Excel Ninja

Chirag R Raval

Member

Chirag R Raval

Member

Chirag R Raval

Member