Compile selected data from one workbook into another workbook with selected data only

J

JimRon

New Member
Joined
Oct 22, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I am mostly new to the world programming but understand a few concepts of it. I have a time saving time idea but not sure exactly how to code it. I have a worksheet that is automatically created daily. This report has 55 columns and 3,500 to 15,000 rows of data. It takes me a good while to go with the sheet and pull just the data I need manually. What I would like to do is be able to pull the desired data from the report file and place in another file with the filename of compiled data report and have the current system date as part of the name. There are a total of 25 columns and eight different types of key products that I only need to look at. The data is unsorted meaning I must go line by line to copy and paste only data I need and a specific format that requires me to jump around in the report to get the data collected. The data that I need to collect is done in this format I used is column 1, 2, 8, 9, 14, 18, 20, 21, 22, 23, 50, 30, 28, 29, 31, 32, 36, 41, 44, 45, 47, 46, and 48. What I wanting to do is if the value in the daily report’s column 8 equals to a product that I need then collect from the report sheet then only collect the data from that row in the desired format. Otherwise, move to the next row until four blank rows are found. I copied what I did so far, but to be honest, I got myself lost. I would very grateful for any and all help.

VBA Code: 
Private Sub GetData()

Dim RepairOrderNumber As String
Dim SerialNumber As String
Dim TestPointPartNumber As String
Dim Product As String
Dim Status As String
Dim ReceivedDate As String
Dim RepairedDate As String
Dim ShippedDate As String
Dim ClosedDate As String
Dim DefectLocation As String
Dim ConsumedTestPointPartNumber As String
Dim ConsumedItemName As String
Dim RepairAction As String
Dim RepairCompletedBy As String
Dim AirportLocationCode As String
Dim County As String
Dim InboundTrackingNumber As String
Dim OutboundTrackingNumber As String
Dim Bouncer As String
Dim CycleTime As String
Dim HoldParts As String
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Compiled Data")

' Searches for the next blank row for data entry

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1





' Loop until blank row

Dim xrg As Range

On Error Resume Next

Set xrg = Application.InputBox _

(Prompt:="First cell select. . .", Title:="Line Product Report", Type:=8)

xrg.Cells(1, 1).Select



' Set do loop to stop when two consecutive empty are reached.

Application.ScreenUpdating = False

Do Until IsEmpty(ActiveRow) And IsEmpty(ActiveRow.Offset(1, 0))



' Values to be collected from data source file "Data Dump"



RepairOrderNumber = .Cells(iRow, 1) = RepairOrderNumber.Value

SerialNumber = .Cells(iRow, 4) = SerialNumber.Value

TestPointPartNumber = .Cells(iRow, 9) = TestPointPartNumber.Value

Product = .Cells(iRow, 8) = Product.Value

Status = .Cells(iRow, 14) = Status.Value

ReceivedDate = .Cells(iRow, 28) = ReceivedDate.Value

RepairedDate = .Cells(iRow, 29) = RepairedDate.Value

ShippedDate = .Cells(iRow, 31) = ShippedDate.Value

ClosedDate = .Cells(iRow, 32) = ClosedDate.Value

DefectLocation = .Cells(iRow, 20) = DefectLocation.Value

ConsumedTestPointPartNumber = .Cells(iRow, 21) = ConsumedTestPointPartNumber.Value

ConsumedItemName = .Cells(iRow, 23) = ConsumedItemName.Value

RepairAction = .Cells(iRow, 50) = RepairAction.Value

RepairCompletedBy = .Cells(iRow, 30) = RepairCompletedBy.Value

AirportLocationCode = .Cells(iRow, 36) = AirportLocationCode.Value

County = .Cells(iRow, 41) = County.Value

InboundTrackingNumber = .Cells(iRow, 44) = InboundTrackingNumber.Value

OutboundTrackingNumber = .Cells(iRow, 45) = OutboundTrackingNumber.Value

Bouncer = .Cells(iRow, 47) = Bouncer.Value

CycleTime = .Cells(iRow, 46) = CycleTime.Value

HoldParts = .Cells(iRow, 48) = HoldParts.Value



' Pulled data placed in sheet

If Product = "Product1" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product2" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product3" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product4" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product5" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product6" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product7" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

ElseIf Product = "Product8" Then

.Cells(iRow, 1).Value = RepairOrderNumber

.Cells(iRow, 2).Value = SerialNumber

.Cells(iRow, 3).Value = TestPointPartNumber

.Cells(iRow, 4).Value = Product

.Cells(iRow, 4).Value = Status

.Cells(iRow, 5).Value = ReceivedDate

.Cells(iRow, 6).Value = RepairedDate

.Cells(iRow, 7).Value = ShippedDate

.Cells(iRow, 8).Value = ClosedDate

.Cells(iRow, 9).Value = DefectLocation

.Cells(iRow, 10).Value = ConsumedTestPointPartNumber

.Cells(iRow, 11).Value = ConsumedItemName

.Cells(iRow, 12).Value = RepairAction

.Cells(iRow, 13).Value = RepairCompletedBy

.Cells(iRow, 14).Value = AirportLocationCode

.Cells(iRow, 15).Value = County

.Cells(iRow, 16).Value = InboundTrackingNumber

.Cells(iRow, 17).Value = OutboundTrackingNumber

.Cells(iRow, 18).Value = Bouncer

.Cells(iRow, 19).Value = CycleTime

.Cells(iRow, 20).Value = HoldParts

Else

End If



End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You must log in or register to reply here.

Similar threads

E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
479
Micron
M
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
400
brianv
B
A
  • Question Question
Cannot enter text in textbox on form
Replies
3
Views
272
Anthony47
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
386
Peter_SSs
Peter_SSs
A
  • Question Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
799
aabrazil1
A

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top