Excel

[chandra amgain]

I have one Excel Question,
i have one excel file in location C:\Users\Chandra\Desktop\Main 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:\Users\Chandra\Desktop\Summery 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.

 

[brenner]

do the files have to stay separate? it would be easier if they were two tabs within the same workbook and then you could always export the summary sheet at a point in time. assuming you don't want to join the workbooks, then you can either create formulas to link the workbooks or use a macro

 

[kumar lama]

thank for quick response
Actually i want VBA macro, i have some idea about lookup formula, sometime i have to drag or edit it. which i don't want but i will keep file in same location. what i want is when i open summery file, it need to be automatic update from my main file if i edit something in main file.
thank you again

 

[brenner]

okay. are the items in the detail file numbered like in column A?

 

[brenner]

assuming you have a numbering in column A. here's the code. you would put this code in the summary file. and if you want you can place it in the workbook open code. also, make sure to save the summary file as a .xlsm.

Sub newlines()
Set wb1 = ActiveWorkbook
Set wb2 = Application.Workbooks.Open("C:\Users\Chandra\Desktop\detail.xlsx")

LastValue = wb1.Sheets("Summary Sheet").Cells(1, 1).End(xlDown)
LastImportedRowDetailFile = wb2.Sheets("Detail").Columns(1).Find(LastValue)
LastRowDetailFile = wb2.Sheets("Detail").Cells(1, 1).End(xlDown)


For i = LastImportedRowDetailFile + 1 To LastRowDetailFile
wb1.Sheets("Summary Sheet").Cells(1048576, 1).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 1)
wb1.Sheets("Summary Sheet").Cells(1048576, 2).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 2)
wb1.Sheets("Summary Sheet").Cells(1048576, 3).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 3)
wb1.Sheets("Summary Sheet").Cells(1048576, 4).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 6)
wb1.Sheets("Summary Sheet").Cells(1048576, 5).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 14)
Next i

wb2.Close

End Sub

 

[kumar lama]

Mr. brenner Thanks For giving me this code
and it's working something, but nothing happen in my summery file ERROR ON For i = LastImportedRowDetailFile + 1 To LastRowDetailFile
but i think i have to make this question more clear. i have 2 workbook, the One Workbook Name is MAIN FILE.Xlsx, and there are three sheet, One Sheet name is "DETAIL". i am editing Detail Sheet every time, ye this is right i have serial number also.
and i have other Workbook which has name Summery File.Xlsx & Sheet Name is Called "Summery"
What i am looking For is i dont want open summery file every time but when i open Summery file it need to get summery from MAIN FILE.Xlsx, Detail Sheet to Summery Sheet.
thank you

 

[brenner]

I replaced the names with your specific wording. From here, you would open the summary file and then run the macro. macro either from the developer tab or if you place the code within the workbook module, it will automatically run on open

LastValue = wb1.Sheets("Summery").Cells(1, 1).End(xlDown)
LastImportedRowDetailFile = wb2.Sheets("DETAIL").Columns(1).Find(LastValue)
LastRowDetailFile = wb2.Sheets("DETAIL").Cells(1, 1).End(xlDown)


For i = LastImportedRowDetailFile + 1 To LastRowDetailFile
wb1.Sheets("Summery").Cells(1048576, 1).End(xlUp).Offset(1, 0) = wb2.Sheets("DETAIL").Cells(i, 1)
wb1.Sheets("Summery").Cells(1048576, 2).End(xlUp).Offset(1, 0) = wb2.Sheets("DETAIL").Cells(i, 2)
wb1.Sheets("Summery").Cells(1048576, 3).End(xlUp).Offset(1, 0) = wb2.Sheets("DETAIL").Cells(i, 3)
wb1.Sheets("Summery").Cells(1048576, 4).End(xlUp).Offset(1, 0) = wb2.Sheets("DETAIL").Cells(i, 6)
wb1.Sheets("Summery").Cells(1048576, 5).End(xlUp).Offset(1, 0) = wb2.Sheets("DETAIL").Cells(i, 14)
Next i

 

[kumar lama]

Mr. brenner
I tried this code, It's doing nothing in my Summery File, it's just blank, it will Open My Detail File and run something but showing just blank
what happening?
What I Did (ALT+F11> ALT+I>M
and Paste Code
Sub newlines()
Set wb1 = ActiveWorkbook
Set wb2 = Application.Workbooks.Open("C:\Users\Samsung\Desktop\Macro Try\detail.xlsx")

LastValue = wb1.Sheets("Summary Sheet").Cells(1, 1).End(xlDown)
LastImportedRowDetailFile = wb2.Sheets("Detail").Columns(1).Find(LastValue)
LastRowDetailFile = wb2.Sheets("Detail").Cells(1, 1).End(xlDown)


For i = LastImportedRowDetailFile + 1 To LastRowSummeryFile
wb1.Sheets("Summary Sheet").Cells(1048576, 1).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 1)
wb1.Sheets("Summary Sheet").Cells(1048576, 2).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 2)
wb1.Sheets("Summary Sheet").Cells(1048576, 3).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 3)
wb1.Sheets("Summary Sheet").Cells(1048576, 4).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 6)
wb1.Sheets("Summary Sheet").Cells(1048576, 5).End(xlUp).Offset(1, 0) = wb2.Sheets("Detail").Cells(i, 14)
Next i

wb2.Close

End Sub

Completely Not Worked: I Think i made something wrong (C:\Users\Samsung\Desktop\Macro Try\detail.xlsx) My file Location

 

[brenner]

if it opens the detail file then the file path is okay. you have changed the summary sheet name back to "Summary Sheet" from what you said that it was "Summery". are you using at least excel 2007? perhaps you can post a screenshot of the file or sample data

 

[kumar lama]

Here is my screen shot link
please view
Postimage.org / gallery - Detail, Summery