Error while executing a procedure.

Hi all,

I was working one a VBA procedure all day long and everything was fine, but suddenly every time I try to execute a procedure I got following error message:

"Compile error: Expected Function or Variable".

and Selection is beeing marked (see code)

I have no idea, what is the reason for this error massage.
I will appreciate any help.

My code is following (please be aware that I am a very beginner in VBA):

Sub gotuj()[/COLOR]
Dim X As Integer
X = 2

ChDir folder
Workbooks.Open Filename:=folder & source

While Cells(X, 2).Value <> ""
X = X + 1
Wend
X = X - 1

Range("B3:K" & X).Select
selection.Copy

 Windows(template).Activate
 Sheets("Data").Activate
    Range("C9").Select
    ActiveSheet.Paste
Range("B9").Activate

Application.DisplayAlerts = False

Windows(source).Activate
ActiveWindow.Close

 Windows(template).Activate
 Sheets("Data").Activate

Dim D As Integer
D = 9

While Cells(D, 3).Value <> ""
D = D + 1
Wend
D = D - 1

   Range("B9").Select
    selection.Copy
    Range("B10:B" & D).Select
    selection.PasteSpecial Paste:=xlPasteFormulas
     Range("B10:B" & D).Select
     selection.Copy
      selection.PasteSpecial Paste:=xlPasteValues
       Application.CutCopyMode = False
       
Range("M9").Select
 selection.Copy
    Range("M10:M" & D).Select
    selection.PasteSpecial Paste:=xlPasteFormulas
      Range("M10:M" & D).Select
      selection.Copy
      selection.PasteSpecial Paste:=xlPasteValues
       Application.CutCopyMode = False
       
    Windows(template).Activate
Sheets("Data").Activate
Range("C8").Select

Dim PTCache As PivotCache
Dim PT As PivotTable

    Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Range("C8").CurrentRegion.Address)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:=Sheets("Table").Range("B2"), _
         TableName:="T1")
         
         With PT
         
        .PivotFields("Group coordinator").Orientation = xlRowField
        .PivotFields("Will breach").Orientation = xlRowField
        .PivotFields("Status").Orientation = xlDataField
        End With
        
    Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Range("C8").CurrentRegion.Address)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:=Sheets("Table").Range("F2"), _
         TableName:="T2")
         
         With PT
         
        .PivotFields("Will breach").Orientation = xlRowField
        .PivotFields("Status").Orientation = xlDataField
        End With
        
     Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Range("C8").CurrentRegion.Address)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:=Sheets("Table").Range("F12"), _
         TableName:="T3")
         
         With PT
         
        .PivotFields("Status").Orientation = xlRowField
        .PivotFields("Status").Orientation = xlDataField
        End With
        
             Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Range("C8").CurrentRegion.Address)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:=Sheets("Table").Range("I2"), _
         TableName:="T4")
         
         With PT
         
        .PivotFields("Name").Orientation = xlRowField
        .PivotFields("Status").Orientation = xlDataField
        End With
        
           Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
         SourceData:=Range("C8").CurrentRegion.Address)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:=Sheets("Table").Range("m2"), _
         TableName:="T5")
         
         With PT
         
        .PivotFields("Group coordinator").Orientation = xlRowField
        .PivotFields("Status").Orientation = xlDataField
        End With
        
Sheets("Table").Activate
Range("A1:AA10000").Select
     selection.Copy
      selection.PasteSpecial Paste:=xlPasteValues
      
          Cells.Select
          Application.CutCopyMode = False
    Cells.EntireColumn.AutoFit
        Rows("2:2").Select
    selection.Delete

End Sub

Hello 06035,

The error is most likely occurring in the beginning of your code with ChDir statement. Your path variable Folder hasn't been assigned a path.

Sincerely,
Leith Ross

Thanks for your replays.

Why are you using this [iColor] here?

Sorry my mistake, I wanted to color something in my code in this post, but then I have changed my mind and have forgotten to delete it. Of course I do not have this in my Excel VBA code.

so where do you define it?

Again my fault I have forgotten to give you this information.

I have defined variables in ThisWorkbook - Workbook - Open as follows:

Private Sub Workbook_Open()

template = Worksheets("Info").Cells(1, 12).Value
folder = Worksheets("Info").Cells(2, 12).Value
source = Worksheets("Info").Cells(3, 12).Value
End sub

And I have also in my module under General - Declarations

Public template As String
Public folder As String
Public source As String
Public where As String
Public name As String
Option Explicit

Hope I have made my mind clearer now.

Regards,
Patryk

maybe you should zip & attach the workbook.

Please find attached file. Let me know if the source file will be useful.

Regards,
Patryk