How do I access a specific workbook that I have open, without using its name?

Question

I'm recording this macro that transfers data between a few different documents. One of the workbooks, "Transfer Template", stays constant. But the other will change. Here is the code that I am using. (I know it's slow and a lot of it is irrelevant, but I just need to make it work).

What I'm assuming is that I have to replace 'Windows("RFQ_14446.xlsm") with ActiveWorkbook or something similar.

Sub Initial_Transfer_Macro()
'
' Initial_Transfer_Macro Macro
'

'
Windows("RFQ_14446.xlsm").Activate
Range("J51").Select
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B1").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B2").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B3").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B5").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B6").Select
ActiveSheet.Paste
Windows("RFQ_14446.xlsm").Activate
Range("K6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("D12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("K18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("I13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("I26").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("I27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("I5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "36"
Range("I5").Select
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("RFQ_14446.xlsm").Activate
Range("I26").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Transfer Template.xlsm").Activate
Range("C20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
Cells.Replace What:=" Rev. ", Replacement:="-", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Cells.Replace What:="RFQ ", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

On a side note, you should [avoid using `select` commands](http://stackoverflow.com/q/10714251/4002530) — tospig, Jun 30 '15 at 21:09
"(I know it's slow and a lot of it is irrelevant, but I just need to make it work)" — SierraOscar, Jun 30 '15 at 21:16

SierraOscar · Accepted Answer · 2015-06-30T21:34:26.037

3

Easiest way is to set references to each workbook at the start of your code:

Sub SO()

Dim thisWorkbook  As Excel.Workbook
Dim otherWorkbook As Excel.Workbook

Set thisWorkbook = ActiveWorkbook
Set otherWorkbook = Workbooks("Transfer Template.xlsm")

'// ... Rest of code here

End Sub

Once this is done you can refer to that variable instead, for example:

Debug.Print thisWorkbook.Sheets.Count

or

otherWorkbook.Sheets(1).Range("A1").Value = thisWorkbook.Sheets(2).Range("B1").Value

Just crude examples but should give you the base of the logic...

Another thing worth noting is that if the code is being run from the workbook that you want to refer to, then simply using ThisWorkbook will suffice:

Sub Example()

Workbooks("Transfer Template.xlsm").Activate
MsgBox ActiveWorkbook.Name
MsgBox ThisWorkbook.Name

End Sub

edited Jun 30 '15 at 21:34

answered Jun 30 '15 at 21:05

SierraOscar

17,507
6
40
68

@S O after I move onto a different workbook, say RFQ_14447, won't I have to go in and manually change the code to reference the new workbook I'm using? – bdkong Jun 30 '15 at 21:12
Sorry, got it the wrong way around - thought that was the static one. Edited code above ^^^ does this answer your question? – SierraOscar Jun 30 '15 at 21:15
So this will prompt me to enter the name of the workbook into the message box? – bdkong Jun 30 '15 at 21:19
No, that's just an example of how the `ThisWorkbook` object works. If you want to enter the name of a workbook use something like `workbookName$ = InputBox("name of workbook")` – SierraOscar Jun 30 '15 at 21:35

score 0 · Answer 2 · answered Jun 30 '15 at 21:10

If you don't know the workbook name but it is the only other one open at the same time (in the same instance of Excel), you can loop through them like this:

Sub TransferTemplate()
    Dim wbTemplate As Workbook: Set wbTemplate = ActiveWorkbook
    Dim wbDestination As Workbook

    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name <> wbTemplate.Name Then
            Set wbDestination = wb
        End If
    Next wb

    'Example copy
    wbTemplate.Worksheets(1).Range("B1").Value = wbDestination.Worksheets(1).Range("J51").Value
End Sub

How do I access a specific workbook that I have open, without using its name?

2 Answers2