Report

Automatically transfer data from one sheet to another [Solved]

Ask a question Lghtningt4u2 1Posts Saturday January 21, 2017Registration date January 21, 2017 Last seen - Last answered on Jan 22, 2017 at 08:58 PM by vcoolio
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
See more 
Helpful
+0
moins plus
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!
Leave a comment
Helpful
+0
moins plus
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.
Lghtning4u2 3Posts Saturday January 21, 2017Registration date January 22, 2017 Last seen - 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.
Reply
Leave a comment
Helpful
+0
moins plus
Hello vcoolio

Would additional sub routine be required to auto populate the data as the data is changed?

Thanks for your help.
Leave a comment
Helpful
+0
moins plus
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.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!