Getting Data From Another Open Workbook

[will2learn]

I need to update the data in the masterfile I am using from various other workbooks. I was wondering if it was possible for a macro in the masterfile to be able to grab data from the second work book and paste it directly into the masterfile?

To complicate matters the second workbook will have a different name eachtime the data is broght from it, but I will only ever have the two workbooks open at the same time.

I've run the macro recorder, but it does not take into account the variation in workbook names. Also I'm trying to avoid typing in the name of the second workbook each time.

 

[Trevor G]

If you have 2 books open to start with then record a macro and use the Ctrl + Tab key it will give the code you need, because it just activates the next window.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro2()<br><SPAN style="color:#007F00">'Use Ctrl + Tab</SPAN><br>    ActiveWindow.ActivateNext<br>   <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Norie]

If you only have 2 workbooks open it's relatively easy to create references to both regardless of name.

You can also create a reference to the workbook the code is in.

If you combine the 2 you can get references to your workbooks.

Something like this, which will only work properly if there are only 2 workbooks open.

Dim wbThis As Workbook
Dim wbOther As Workbook
 
    Set wbThis = ThisWorkbook
    
    If Workbooks(1) Is wbThis Then
        Set wbOther = Workbooks(2)
    Else
        Set wbOther = Workbooks(1)
    End If

You can then use these references to copy data between the workbooks or whatever else you need to do.

 

[will2learn]

I'm still struggling. The code I've created is shown below but it keeps stopping at the line "wbOther.Select".




Dim wbThis As Workbook
Dim wbOther As Workbook

Set wbThis = ThisWorkbook

If Workbooks(1) Is wbThis Then
Set wbOther = Workbooks(2)
Else
Set wbOther = Workbooks(1)
End If

wbOther.Select
Range("A3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

wbThis.Select
Range("A5").Select
ActiveSheet.Paste

wbOther.Select
Range("N3:W3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

wbThis.Select
Range("N5").Select
ActiveSheet.Paste

 

[will2learn]

Solved

Sub XXX()
ActiveWindow.ActivateNext

Range("A3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ActiveWindow.ActivateNext

Range("A5").Select
ActiveSheet.Paste

ActiveWindow.ActivateNext

Range("N3:W3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

ActiveWindow.ActivateNext

Range("N5").Select
ActiveSheet.Paste

End Sub

 

[Norie]

You don't need any of those Selects.

Try this.

I've used ActiveSheet because you have no worksheet references, it would be best to use the actuall worksheet names, eg Worksheets("SHEETNAME").

Option Explicit
 
Sub test()
Dim wbThis As Workbook
Dim wbOther As Workbook
Dim rngSrc As Range
Dim rngDst As Range
 
    Set wbThis = ThisWorkbook
 
    If Workbooks(1) Is wbThis Then
        Set wbOther = Workbooks(2)
    Else
        Set wbOther = Workbooks(1)
    End If

    Set rngSrc = wbOther.ActiveSheet.Range("A3")
 
    Set rngSrc = Range(rngSrc, rngSrc.End(xlDown).Offset(, 8))
 
    Set rngDst = wbThis.ActiveSheet.Range("A5")
 
    rngSrc.Copy rngDst
 
    Set rngSrc = wbOther.ActiveSheet.Range("N3")
 
    Set rngSrc = Range(rngSrc, rngSrc.End(xlDown).Offset(, 9))
 
    Set rngDst = wbThis.ActiveSheet.Range("N5")
 
    rngSrc.Copy rngDst
 
End Sub