Excel/Excel Automatic Update From One workbook to another workbook in excel
QUESTION: I have one Excel Question,
i have one excel file in location C:UsersChandraDesktopMain File.Xlsx and this is my main file. All information include in this file for eg: Main File.Xlsx, Sheet Name "Detail"
And i have one summery File C:UsersChandraDesktopSummery File.Xlsx. Summery Sheet
what i want when i update something "Main File, Detail Sheet" it need to be update automatic in "Summery File, Summery Sheet". but i need only A, B, C, F, N column value from "Main file, Detail Sheet" need be update in "Summery File, Summery Sheet A, B, C, D, E.
i dont know my question is clear or not
Please Help Me, I hope someone has this answer.
Thank in advance.
ANSWER: The easiest method would be to link the cells - so on the summary file, summary sheet you would have a formula that looked something like
=[Summery File.xlsx]'Summery Sheet'!A2
You can easily create these links by having both files open. Type the = sign and then click onto the other workbook, worksheet and cell - this will give you the formula but with Absolute References (the dollar signs) - remove these and you will then have a formula you can drag down and across the affected rows, though you would need to amend the reference for F/D and N/E as these aren't the same columns as each other!
You would need this formula to be on all cells that needed to update - whenever you open the summary file, you can get the updated data. You COULD use VBA to update the file without formulas, but this is probably overkill.
---------- FOLLOW-UP ----------
QUESTION: I know that way, Formula i know, i could use Vlookup or match, or direct link with cell (=), but i dont wannna drag or do anything in my summery file. once a month i open summery file, i wanna get summery from MAIN File without do noting.
I Hope you have vba code also.
ANSWER: something along these lines will work
Dim CurBook As Workbook, MyBook As Workbook
Set CurBook = ActiveWorkbook
Set MyBook = Workbooks.Open("c:\Users\Chandra\Desktop\Main File.Xlsx")
CurBook.Sheets("Sheet1").Range("a:a").Value = .Sheets("Sheet1").Range("a:a").Value
CurBook.Sheets("Sheet1").Range("b:b").Value = .Sheets("Sheet1").Range("b:b").Value
CurBook.Sheets("Sheet1").Range("c:c").Value = .Sheets("Sheet1").Range("c:c").Value
CurBook.Sheets("Sheet1").Range("f:f").Value = .Sheets("Sheet1").Range("d:d").Value
CurBook.Sheets("Sheet1").Range("n:n").Value = .Sheets("Sheet1").Range("e:e").Value
At the moment this is set as a standalone macro - to run it when you open the summary sheet,
you would need to have it running on the OPEN event of the workbook
---------- FOLLOW-UP ----------
QUESTION: Thank For this Help Mr AIDAN,
I tried this code, it doing something,and open my main file also.
but not updating value in summery, here is my rough file, can u please check it once. what wrong with my file
file name is different here. i change file name also.
but nothing happen.
thank you. hoping quick response
I have set it to run from the Active Workbook, which means it needs to be the summary file that is active when you run it. I will take a look at the rough file when I get home (cannot access file share sites from current location) and update this reply at that point.
When I go to the site to download the file it will only let me do it via an Exe file - which I'm not prepared to do as these are not files I can trust. If it helps, my direct email is firstname.lastname@example.org - you can send the file direct to me that way?