Macro to copy data from the other open xmls document to this one?

[tonywatsonhelp]

Hi Everyone,

Hi you can help me.
I have a document my boss uses all day everyday and he keeps asking me to add new deshboards etc.
The thing is it takes me a while to do this which mean this document is out of service.
However because of the way its set up I could copy data from his version to the new version and this would work.
but i don't know what the names of the two documents are going to be?
to make it easier there will only ever be two two excel documents open.
So what i need is this.

A macro that when run,
Goes to the only other open workbook.
goes to sheet "Raw Data1"
Copy's Range B2:Y & Lastrow

Goes to the workbook the macro is stored in and pastes it into sheet "Raw Data2" A2 (I'm using Raw Data2 as a test sheet I'll change it to Raw Data1 once I know it works.)

I'm having difficulty because I don't know what the other workbook name is so any help would be greatly appreciated.

Thanks

Tony

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim WB As Workbook, srcWS As Worksheet, desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Raw Data2")
    For Each WB In Application.Workbooks
        If WB.Name <> ThisWorkbook.Name Then
            Set srcWS = WB.Sheets("Raw Data1")
            With srcWS
                .Range("B2", .Range("Y" & .Rows.Count).End(xlUp)).Copy desWS.Range("A2")
            End With
        End If
    Next WB
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Hi Mumps,
This is great, thank you very much
Tony

 

[mumps]

You are very welcome.