CODEWITHSUNDEEP
excelvba
dcole204
dcole204

Reputation: 5

Declare workbook as variable

I am trying to declare a workbook location as a variable so I do not have to change the name in 100 locations. The block of code is copied multiple times only the destination changes

Sub copyToDatabase_2()
'
' Copy from seperator to DB
'
    Dim location_1 As Workbooks
    Dim location_2 As Workbooks


    location_1 = "Table Separator (M2).xlsm"
    location_2 = "TCE-525 (M2) DAVID.xlsm"

    Windows(location_1).Activate
    Range("A5:E16").Select
    Selection.Copy
    Windows(location_2).Activate
    Range("R3").Select
    ActiveSheet.Paste

    Windows(location_1).Activate
    Range("A19:E30").Select
    Selection.Copy
    Windows(location_2).Activate
    Range("R35").Select
    ActiveSheet.Paste

End Sub

error received is

Compile error: Invalid use of property.

Upvotes: 0

Views: 54

Answers (1)

Nickolay
Nickolay

Reputation: 32063

You're trying to assign a string to a Workbooks variable:

Dim location_1 As Workbooks
' ...

location_1 = "Table Separator (M2).xlsm"

A quick fix would be define the variables as String, but you ought to apply the suggestions in How to avoid using Select in Excel VBA, as @BigBen noted.

Upvotes: 1

Related Questions