I have a master file for all the transactions in one financial year. This file currently has 4 sheets: EGC,SSM,RSL,MF.
The first three are sheets for separate customer. MF is Master File, the sheet which is suppose to automatically add entries from other three chronologically. This way, along with the help of filters (already applied), I am able to find any entry specific to any customer from the whole year record.
Currently i have to manually copy paste all the entries to the MF.
I tried putting in some formula, but didn't work. Later I came across this site and realized there should be some code for this.
Appreciate if someone can help.
Regards.
What I usually do is automatically copy the entire row when the last data is entered and paste it to the first available row on the MF sheet. I don't know how your sheets are made up, but you can try the following if you like the above idea:
Place this code in the first 3 sheets:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("F:F"), Target) Is Nothing Then Exit Sub
Rows(Target.Row).Copy Sheets("MF").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
Once you implemented the code and make a change to column F, the entire row will be copied to the MF sheet. Change "F:F" to the column where you enter the last data of the row.
Hello Trowa,
Thanks a lot for the help. Much appreciated.
The sheets are updated on random times for each single entry (row). For example, if a new contract is made today, it will be entered in a fresh row and with the help of this code MF will be automaticaly updated. However, the next step in the row is of Despatch of the contracted goods. It may come after 2 days during which another contract could be signed or may be 2 ( if i am lucky ; )). After contract n despatch there are few more sections which would be updated at different times later in the year.
Is it possible that the rows in MF, even after its first creation, can be updated automatically whenever more data is entered or edited in the sheets? If it can be done, It wud save me a lot of repetyping.
Thanx again for the code earlier.
Regards,
Ahriaz.
P.s. If there is a way I cud send u the file, let me know. It will give u a better understanding.
Get Outlook for Android
For your followup question, we need to expand the code.
I have got the following scenario in mind:
You enter data in a row in columns A, B, C, D and F.
When you confirm the value in column F, the entire column is copied to the MF sheet.
At a later time you enter data in that same row in columns E and G.
When you confirm the value in column G, the code looks for the unique value in column A, finds that value in the MF sheet to determine the row number, then copies the values from columns E and G to the MF sheet.
Here is the code that does the above:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fValue As Range
If Not Intersect(Columns("F:F"), Target) Is Nothing Then
Rows(Target.Row).Copy Sheets("MF").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ElseIf Not Intersect(Columns("G:G"), Target) Is Nothing Then
Set fValue = Sheets("MF").Columns("A:A").Find(Target.Offset(0, -6))
Sheets("MF").Range("E" & fValue.Row) = Range("E" & Target.Row)
Sheets("MF").Range("G" & fValue.Row) = Range("G" & Target.Row)
End If
End Sub
If you get stuck in adjusting the code, then use a free filesharing site like www.speedyshare or ge.tt etc., to upload your file and then post back the provided download link for me to take a look at your file.
