Macro to take data from another document

[andydarly]

Hi, all. I'm very rusty with macros at the moment so I hope I can explain this properly.

I want to have a macro that will open a document called "Sales Data -7 to +14" in location W:\CALL CENTRE OPERATIONS\WORKFORCE PLANNING\RESOURCE PLANNING\Reporting\Autorun Reports It then needs to copy all the data in a tab called 'Data' in the range columns A:Y and however far down it goes (it will vary from day to day) and paste into my document in a tab called 'Raw Data' and then close down the file called "Sales Data -7 to +14"

I have tried recording a macro so I can edit it but I do not get reference to the file path of "Sales Data -7 to +14", even though I open it whilst recording the macro so now I am confused

Thanks in advance

Andy

 

[Larry Haydn]

Assuming your file name is Sales Data -7 to +14.xlsx

Option Explicit
Option Compare Text

Sub GetSalesData()
'==============================================
'   Get the Sales Data from another workbook
'==============================================

Dim MyWB As Workbook        ' This is your current workbook
Dim MyWS As Worksheet       ' This is the [Raw Data] worksheet in your current workbook

Dim SourceWB As Workbook    ' This is the external workbook from Autorun Reports
Dim SourceWS As Worksheet   ' This is the worksheet that contains the data you wish to import

Dim LastRow As Long         ' This is a row counter

' This is the file path and file name
Const ThisFile As String = "W:\CALL CENTRE OPERATIONS\WORKFORCE PLANNING\RESOURCE PLANNING\Reporting\Autorun Reports\[COLOR=rgb(209, 72, 65)]Sales Data -7 to +14.xlsx[/COLOR]"

    '=========================================================
    '   First, identify your current workbook and worksheet
    '=========================================================
    Set MyWB = ActiveWorkbook
    Set MyWS = MyWB.Sheets("Raw Data")
   
    '===================================================
    '   Next, open the external work, and identify it
    '===================================================
    Workbooks.Open ThisFile
        '========================
        '   Identify this file
        '========================
        Set SourceWB = ActiveWorkbook
        Set SourceWS = SourceWB.Sheets("Data")

    '===============================================
    '   Clean out your {Raw Data] worksheet first
    '===============================================
    MyWS.Cells.ClearContents
   
    '====================================================================
    '   Find the last row of the [Data] worksheet,
    '   then copy the data from the source worksheet to your worksheet
    '====================================================================
    LastRow = SourceWS.Range("A1048576").End(xlUp).Row
        '==========
        '   Copy
        '==========
        SourceWS.Range("A1:Y" & LastRow).Copy MyWS.Range("A1")
       
    '===============================
    '   Close the source workbook
    '===============================
    SourceWB.Close Savechanges:=False
   
End Sub