Enter formula in a message box

[HighlandRoss]

Hi

Grateful for anyones help although I'm not sure this is possible!?

I have a number of projects each maintaining their own financial information
on their own workbooks (each uses the same identical template)

I want to create a workbook to summarise information from the (5) projects
financial WBs. Or at least I want to copy a snapshot of each workbook into
one summary workbook every fortnight.

So for instance on my summary sheet columns A1:C50 are Project 1, D1:F50 are
project 2 etc.
Rather than manually copying and pasting or creating links to cells each
time I receive the projects workbooks I wondered if there is an easier
automated way of doing this?!

Because each project WB will have the information in identical cells albeit
in different workbooks, I wondered if I enter the generic cell reference in
the summary sheet and have a message box to enter the workbook name to add to
that formula!?

God I hope that makes sense, bit complicated to explain!! Thanks in
anticipation

 

[john]

If the workbooks are stored in a shared network drive it should be possible
for you to extract the data you need using formula even if workbooks are
closed. This would remove the need for workbooks to be sent to you.

Example shows how you could get data from one workbook & paste back into
your first range in your summary workbook / sheet. You can expand it to
include more workbooks / ranges.

Paste code in standard module of your summary workbook.
Update the Drive, Workbook Name (“.xls†must be included) Sheet name where
the data is to be taken from & the range as required. Hopefully when macro is
run, you will see required data returned.

Hope helpful

Sub GetProjectData()
Dim mydata As String
'workbook location, sheet name & range to copy
'Change Drive, WBName , Sheet Name & Range as required
mydata = "='C:\[WBName.xls]Sheet1'!$A$1:$C$50"

'link to worksheet
'this is your summary workbook
With ThisWorkbook.Worksheets("summary").Range("A1:C50") '<< change as
required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub