Auto update data between sheets
Closed
dana
-
May 19, 2016 at 03:39 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 22, 2016 at 08:48 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 22, 2016 at 08:48 AM
Related:
- Auto update data between sheets
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Play store update - Guide
- Ps3 update - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 22, 2016 at 08:48 AM
May 22, 2016 at 08:48 AM
Hello Dana,
I'm assuming that you want to treat each sheet individually meaning that, regardless of which sheet (1, 2 or 3) that you are working on, you would like newly entered data to be immediately transferred to sheet4 (your Summary sheet?) once you enter each row of data.
So, perhaps the following code, placed in the sheet module of each sheet (1,2 & 3), will do as you require:-
The code will also delete the row of data that has been transferred from the active sheet to sheet4.
In the above code, you will need to make Column H the last column into which you enter data otherwise the code will not work. If Column H is not your last data input column, then change the column reference (Range("H:H")) in line 9 of the above code to suit yourself.
Following is the link to my test work book which will show you how the above code works:-
https://www.dropbox.com/s/jkfunqrooisks21/Dana%28Worksheet_Change%20event%20in%20each%20sheet%29.xlsm?dl=0
In any of the sheets 1,2 or 3, enter any value in any cell in Column H then click away (or press enter or down key) and that row of data will be transferred to sheet 4.
To implement the code into your work book:-
- Right click on the sheet1 tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.
Repeat the above steps for sheets 2 & 3.
I hope that this helps.
Cheerio,
vcoolio.
I'm assuming that you want to treat each sheet individually meaning that, regardless of which sheet (1, 2 or 3) that you are working on, you would like newly entered data to be immediately transferred to sheet4 (your Summary sheet?) once you enter each row of data.
So, perhaps the following code, placed in the sheet module of each sheet (1,2 & 3), will do as you require:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then
If Target <> "" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy Sheet4.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If
End If
Sheet4.Columns.AutoFit
Sheet4.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code will also delete the row of data that has been transferred from the active sheet to sheet4.
In the above code, you will need to make Column H the last column into which you enter data otherwise the code will not work. If Column H is not your last data input column, then change the column reference (Range("H:H")) in line 9 of the above code to suit yourself.
Following is the link to my test work book which will show you how the above code works:-
https://www.dropbox.com/s/jkfunqrooisks21/Dana%28Worksheet_Change%20event%20in%20each%20sheet%29.xlsm?dl=0
In any of the sheets 1,2 or 3, enter any value in any cell in Column H then click away (or press enter or down key) and that row of data will be transferred to sheet 4.
To implement the code into your work book:-
- Right click on the sheet1 tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.
Repeat the above steps for sheets 2 & 3.
- Test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
