Dashboard VBA Help

[dclarke3]

Hi,

I'm creating a "Dashboard" to display metrics. I have a number of "Tracker" files that I need to extract data from and summarize in my Dashboard.

All day I've been searching the internet trying different ways and adapting them to my situation but none of them have worked.

The tracker documents are live workbooks that are updated constantly. At the end of the month I want to press a button to start a Macro that looks at a tracker workbook, copies a range of data, and pastes it into the Dashboard workbook. I intend to have one button per Tracker.
The Dashboard will sit on a shared server drive and the Trackers will be on the same drive but in different folders. The Trackers are read only files (not sure if that matters).
I would prefer if the Macro didn’t have to open the Tracker workbooks but so far it has been more difficult to get the closed workbook method to work.
Is this possible? Could someone please write me some code to copy and paste into my workbook! I would paste what I have done already but none of it has worked so I thought it may be best to start from scratch.
Please help!
Thanks

 

[dclarke3]

So I managed to get something to work.

Sub CAPA()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "QF85 issue 3 CAPA Tracker V4.xlsx"
Const SheetName$ = "CAPA_2014"
Const NumRows& = 100
Const NumColumns& = 1
FilePath = "W:\QA Documents\CAPA List\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("B1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

I have 2 issues though. 1) I can only collect data from the 1st column and any subsequent columns because of the constants part. If I change the constant to 2 then I get columns 1&2. What if I want 1 and 3? Is there a way to reference to columns I want by letter? Or even a select range?
2) It just sticks the data in the 1st column of the active worksheet. How can I specify which column to paste the start of the data and which worksheet to put it in? For example I want multiple macros collecting data from different sources and pasting into the same worksheet. I don't want all the data to be pasted on top of each other. Which lines to I need to change? And how?