CODEWITHSUNDEEP
vbaexcel
bdkong
bdkong

Reputation: 191

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

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

Upvotes: 2

Views: 125

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

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

Upvotes: 3

Automate This
Automate This

Reputation: 31364

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

Upvotes: 0

Related Questions