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. That should help you avoid using Select and Activate and also how to reference each workbook. — BigBen
– BigBen, Commented Jul 29, 2019 at 22:48

Nickolay · Accepted Answer · 2019-07-29 23:38:32Z

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.

@dcole204 feel free to mark the answer as "accepted" by clicking the check mark to the left, glad your issue is resolved.

Collectives™ on Stack Overflow

Declare workbook as variable

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related