Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter formula in a message box
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter formula in a message box
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 -- jb "HighlandRoss" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"can't enter break mode at this time" message when adding a projectreference | Excel Programming | |||
Tab through & hit enter through a message box with a macro | Excel Programming | |||
received message: Can't enter break mode at this time | Excel Programming | |||
What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
How do I input a warning message to remind the user to enter into. | Excel Discussion (Misc queries) |