Copy Workbook name then Activate workbook with copyed name later with vba

Question

I Have WName = ThisWorkbook.Name and then I want to set Windows("WName").Activate at another point in my code but is not working.

I am aware this might not make sense but it does with all of the sutf that is going on

below is a small test sub (I am not using this just trying to get it to work)

Thanks

 Sub test()
 'Copy Active Workbook Name
 WName = ThisWorkbook.Name

 'Activate a different Workbook
 Windows("MyWorkbookAAA").Activate

 'Activate Original workbook using WName
 Windows("WName").Activate  'Have also tried Windows(WName).Activate

 range("D2:D25").Select
 Selection.Copy

 Windows("MyWorkbookBBB").Activate
 range("C22").Select
 ActiveSheet.Paste

End Sub

enderland · Answer 1 · 2013-05-31T20:14:04.457

1

If you want to keep similar syntax by keeping a reference to your string value WName.

Workbooks(WName).Activate

This activates that workbook.

Also can do

ThisWorkbook.Activate

if it's the workbook running your code.

(there are easier ways to do the specific operation you are ONLY doing one thing like copying, but this explains how to do what you are trying to do)

edited May 31 '13 at 20:14

answered May 31 '13 at 19:51

enderland

13,825
17
98
152

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

Try this code:

Avoid using Select/Activate in your code . Refer this link

 ThisWorkbook.ActiveSheet.Range("D2:D25").Copy Workbooks("MyWorkbookBBB").ActiveSheet.Range("C22")

enter image description here

edited May 23 '17 at 11:53

Community

1
1

answered May 31 '13 at 20:10

Santosh

12,175
4
41
72

Thanks for the information and code,I had gotten the code from recording a macro, I have that kind off code in a number of places, I will need to replace it. – xyz Jun 01 '13 at 14:29

Copy Workbook name then Activate workbook with copyed name later with vba

2 Answers2