Declare workbook as variable

Question

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.

See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). That should help you avoid using `Select` and `Activate` and also how to reference each workbook. — BigBen, Jul 29 '19 at 22:48

score 1 · Accepted Answer · answered Jul 29 '19 at 23:38

1

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.

answered Jul 29 '19 at 23:38

Nickolay

31,095
13
107
185

Thank you this works perfectly in combo with BigBen – dcole204 Jul 31 '19 at 16:10
1

@dcole204 feel free to mark the answer as "accepted" by clicking the check mark to the left, glad your issue is resolved. – BigBen Jul 31 '19 at 16:14

Declare workbook as variable

1 Answers1