Automatically transfer data from one sheet to another
Solved/Closed
Lghtningt4u2
Posts
1
Registration date
Saturday January 21, 2017
Status
Member
Last seen
January 21, 2017
-
Jan 21, 2017 at 11:01 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 22, 2017 at 08:58 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 22, 2017 at 08:58 PM
Related:
- Automatically transfer data from one sheet to another
- Transfer data from one excel worksheet to another automatically - Guide
- Free fire transfer - Guide
- How to automatically transfer data between sheets in Excel - Guide
- Google sheet right to left - Guide
- Windows network commands cheat sheet - Guide
4 responses
Lghtning4u2
Posts
3
Registration date
Saturday January 21, 2017
Status
Member
Last seen
January 22, 2017
Jan 21, 2017 at 04:28 PM
Jan 21, 2017 at 04:28 PM
Heres the VBA code I managed to come up with.
Worked out good!
Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
Sheets("PENDING").Range("A3:R500").ClearContents
For i = 2 To LastRow
If Sheets("Master").Cells(i, "L").Value = "PENDING" Then
Sheets("Master").Cells(i, "L").EntireRow.Copy Destination:=Sheets("PENDING").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
LastRow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
Sheets("LOST").Range("A3:R500").ClearContents
For i = 2 To LastRow
If Sheets("Master").Cells(i, "L").Value = "LOST" Then
Sheets("Master").Cells(i, "L").EntireRow.Copy Destination:=Sheets("LOST").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
LastRow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
Sheets("SOLD").Range("A3:R500").ClearContents
For i = 2 To LastRow
If Sheets("Master").Cells(i, "L").Value = "SOLD" Then
Sheets("Master").Cells(i, "L").EntireRow.Copy Destination:=Sheets("SOLD").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
LastRow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
Sheets("BIDDING").Range("A3:R500").ClearContents
For i = 2 To LastRow
If Sheets("Master").Cells(i, "L").Value = "BIDDING" Then
Sheets("Master").Cells(i, "L").EntireRow.Copy Destination:=Sheets("BIDDING").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub
Worked out good!
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jan 22, 2017 at 04:40 AM
Jan 22, 2017 at 04:40 AM
Hello Lghtning4u2,
Thanks for sharing your resolution.
I was just wondering if you intentionally made the code a Workbook_Open event? Doing it this way would mean that you would have to close/open the work book each time that you wanted to transfer the data to the individual sheets.
Also, how long does the code take to execute?
Thanks again.
Cheerio,
vcoolio.
Thanks for sharing your resolution.
I was just wondering if you intentionally made the code a Workbook_Open event? Doing it this way would mean that you would have to close/open the work book each time that you wanted to transfer the data to the individual sheets.
Also, how long does the code take to execute?
Thanks again.
Cheerio,
vcoolio.
Lghtning4u2
Posts
3
Registration date
Saturday January 21, 2017
Status
Member
Last seen
January 22, 2017
Jan 22, 2017 at 04:53 AM
Jan 22, 2017 at 04:53 AM
The Workbook_Open event was intentional. Do you have another suggestion?
The code is not noticeable when executed.
I'm not a pro but I did stay at a Holiday Inn express!
Thanks for your feedback vcoolio.
The code is not noticeable when executed.
I'm not a pro but I did stay at a Holiday Inn express!
Thanks for your feedback vcoolio.
Lghtning4u2
Posts
3
Registration date
Saturday January 21, 2017
Status
Member
Last seen
January 22, 2017
Jan 22, 2017 at 08:48 PM
Jan 22, 2017 at 08:48 PM
Hello vcoolio
Would additional sub routine be required to auto populate the data as the data is changed?
Thanks for your help.
Would additional sub routine be required to auto populate the data as the data is changed?
Thanks for your help.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jan 22, 2017 at 08:58 PM
Jan 22, 2017 at 08:58 PM
Hello Lghtning4u2,
I was thinking along the lines of a Worksheet_Change event such as:-
This assumes that your criteria are in Column L so you would need to make your last entry in Column L of each row and as you click away (or press enter or down arrow) that row will be automatically transferred to the relevant sheet. You could also add a line to delete the "used" data from the Master sheet as you go if you would like (just remove the apostrophe at the beginning of line 12, the line in green font)
Another option would be to assign a code to a button and once you are done with the data entry, just click on the button to transfer the data to the relevant sheets.
Just some options for your future forays into VBA!
Again, thanks for sharing. Good luck!
Cheerio,
vcoolio.
I was thinking along the lines of a Worksheet_Change event such as:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("L:L")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).(3)(2)
'Target.EntireRow.Delete
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
This assumes that your criteria are in Column L so you would need to make your last entry in Column L of each row and as you click away (or press enter or down arrow) that row will be automatically transferred to the relevant sheet. You could also add a line to delete the "used" data from the Master sheet as you go if you would like (just remove the apostrophe at the beginning of line 12, the line in green font)
Another option would be to assign a code to a button and once you are done with the data entry, just click on the button to transfer the data to the relevant sheets.
Just some options for your future forays into VBA!
Again, thanks for sharing. Good luck!
Cheerio,
vcoolio.
