Expected identifier vba ошибка

You might want to read up a bit on what classes are and how they work.

    Dim va As Class
    Set va = New Class
    'Assign values to every variable in the object
    va.Country = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 0)
    va.Node_name = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 1)
    va.Active = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 4)
    va.From = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 5)
    va.To = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 6)

As you already observed, classes don’t (exactly) work this way. The problem here is that the compiler currently has no idea about the class you tell it to use. You need to define it first. Think of classes as a blueprint to create objects. They contain all the information that is needed for the object to be created, to do some work, and finally: to get destroyed.
To create a class, you need to add a Class Module to your VBA project. Rename it to INeedADescriptiveName (or whatever seems fitting for its purpose, but I’ll go with that name for this little demonstration). Then you add to the class’ definition until it satisfies your requirements, for example as such:

Option Explicit

Private Type TDataContainer
    Country As String
    Node_Name As String
    Active As Boolean
    From As Date
    To As Date
End Type

Private Data As TDataContainer

Public Property Get Country() As String
    ' Some conditional code might go here
    Country = Data.Country
End Property

Public Property Let Country(ByVal inpVar As String)
    ' Validation code here
    Data.Country = inpVar
End Property

Public Property Get Active() As Boolean
    ' Some conditional code might go here
    Active = Data.Active
End Property

Public Property Let Active(ByVal inpVar As Boolean)
    ' Validation code here
    Data.Active = inpVar
End Property

' repeat the above pattern for all other fields you want to expose

With this information in place, you can then create an instance of that class just like you already tried:

Dim va as INeedADescriptiveName
Set va = New INeedADescriptiveName

Then the compiler knows that the object va has the fields va.Country, va.Active, … And you can go ahead and access those fields as you did:

    va.Country = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 0)
    va.Active = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 4)
    ' ...

Of course, only storing data is not what makes a class useful. It would make it a much more useful construct if you also had procedures to go with the data, to transform it in a meaningful way. As such, an easier approach to your problem (as far as I can tell) would be to only use a user-defined type (UDT):

' Add this to the header of your standard module, i.e. above all Subs/Functions in that module:
Private Type TDataContainer
    Country As String
    Node_Name As String
    Active As Boolean
    From As Date
    To As Date
End Type

The next part goes right where you originally had Dim va As Class:

Dim va As TDataContainer
' Since va is now a mere data structure (UDT), not an object, no New-ing up is required
'Assign values to every variable in the UDT
va.Country = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 0)
va.Node_name = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 1)
va.Active = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 4)
va.From = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 5)
va.To = ActiveSheet.ListObjects("Schema").DataBodyRange.Cells(i, 6)

Hi everybody,

I’m trying to create a macro that takes information from certain sheets in one excel workbook and copies it into the sheets with the same name in another workbook. I think the macro below might work, but whenever I try to run it I get the message «compile error — expected:identifier». I’ve tried looking around, but I can’t find what might be causing this.

However, I tried the same thing with a recorded macro that just copies from cell A1 to cell B2 and it gives the same error so I think it has to do with the file. I saved it as .XLSM so macro’s are enabled.

Any tips would be greatly appreciated! Thanks in advance

Edit:CodeBlock

Sub CF()

Dim SheetName As Range
Dim CopyRange As Range
Dim DestinationRange As Range
Dim PL_BS_Template As String
Dim CF_template As String

PL_BS_Template = ThisWorkbook.Path & "\" & "2021.04_PL&BS_template_2.xlsm"
CF_template = ThisWorkbook.Path & "\" & "2021.04_CF_Conso_template_2.xlsx"


Workbooks.Open CF_template

For Each SheetName In Range("sheets")
    If SheetName.Value <> "" And SheetName.Value <> 0 Then
    
    Set CopyRange = Workbooks(PL_BS_Template).Sheets(SheetName.Value).Range("C4:D500")
    Set DestinationRange = Workbooks(CF_template).Sheets(SheetName.Value).Range("C4:D500")
    
    CopyRange.Copy
    DestinationRange.PasteSpecial xlPasteValues
    
    End If
    
Next

End Sub