Autoploting (Master to other sheets)
Closed
ahriaz
-
Nov 1, 2016 at 04:23 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2016 at 11:35 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2016 at 11:35 AM
Related:
- Autoploting (Master to other sheets)
- Master royale - Download - Strategy
- Typing master pro - Download - Education
- Pokemon fire red cheats rare candy and master ball - Guide
- Yu-gi-oh master duel download pc - Download - Strategy
- Samsung master reset code - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 1, 2016 at 11:41 AM
Nov 1, 2016 at 11:41 AM
Hi ahriaz,
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:
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.
Best regards,
Trowa
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.
Best regards,
Trowa
ahriaz
Posts
1
Registration date
Tuesday November 1, 2016
Status
Member
Last seen
November 2, 2016
Nov 2, 2016 at 03:18 AM
Nov 2, 2016 at 03:18 AM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 8, 2016 at 11:35 AM
Nov 8, 2016 at 11:35 AM
Hi Ahriaz,
Good to see you liked the idea.
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:
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.
Best regards,
Trowa
Good to see you liked the idea.
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.
Best regards,
Trowa
