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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You must log in or register to reply here.

Similar threads

A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
125
Anthony47
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
134
Peter_SSs
Peter_SSs
A
  • Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
230
aabrazil1
A
A
  • Question
Autofilter code based on username
Replies
1
Views
163
aabrazil1
A
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
146
mikefisher
M

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

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