Previous Topic Next Topic

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 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 22, 2017 at 08:58 PM
Hello,

I have a workbook with several worksheets (Master, Pending, Lost, Sold, and Bidding). I would like to be able to have certain line items based on status on the Master sheet and have them automatically transfer to the other sheets (Pending, Lost, Sold, and Bidding).

Can anyone help with this?

https://www.dropbox.com/s/9oquh5o7jvgpmt8/Sales%20and%20Booking%20LIVE%20COPY%20-%20USE%20THIS%20FILE.xlsx?dl=0
Related:

4 responses

Answer 1 / 4
Lghtning4u2 Posts 3 Registration date Saturday January 21, 2017 Status Member Last seen January 22, 2017
Jan 21, 2017 at 04:28 PM
Heres the VBA code I managed to come up with. 

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!
0
Answer 2 / 4
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
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.
0
Lghtning4u2 Posts 3 Registration date Saturday January 21, 2017 Status Member Last seen January 22, 2017
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.
0
Answer 3 / 4
Lghtning4u2 Posts 3 Registration date Saturday January 21, 2017 Status Member Last seen January 22, 2017
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.
0
Answer 4 / 4
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 22, 2017 at 08:58 PM
Hello Lghtning4u2,

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.
0

Similar discussions

Automatically transfer data from one sheet to another
sunny49er -
TrowaD -

22 responses