Hello All,
I have a large inventory sheet. My macro currently sorts all of the data by many traits and converts necessary cells to number format and numeric values. I need it to copy and paste only certain data into another sheet. The code below works well when the "string" is present in the column. In my example, I want to copy all data that has exactly a "5" in the appropriate column (VehCol) (don't include data such as say "658"). However, since the sheet's data changes month to month, the particular vehicle may not appear on the sheet. If there is no "5" in the column, I receive a "Run-time error '1004': Application-defined or object-defined error." The macro needs to do this for roughly 20 some-what random vehicle numbers. Currently, I have simply copied and pasted the "Vehicle Sort" code below for each different vehicle number, as the vehicle numbers may change in the future. How do I compensate if there is a lack of a vehicle 5 in the data?
Thanks in advance for any feedback.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''Relevant Code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Discover location of "Vehicle" column
'Vehicle
Range("A1:Z100").Select
Cells.Find(What:="Vehicle").Select
VehCol = Selection.Column
VehRow = Selection.Row
'****************Vehicle Sort**************
'Vehicles: 5,8,9,20,24,33,39,43,55,75,76,77,83,84,85,86,509,510,511,752
'Vehicle Five
' Figure out where "Vehicle 5" data starts.
Sheets("Data").Select
Dim FiveRow As Long
Dim FiveStart As Long, FiveEnd As Long
For FiveRow = 3 To 65536
If Cells(FiveRow, VehCol).Value = "5" Then
FiveStart = FiveRow
Exit For
End If
Next FiveRow
' Figure out where the "Vehicle 5" data ends.
For FiveRow = FiveStart To 65536
If Cells(FiveRow, VehCol).Value <> "5" Then
FiveEnd = FiveRow
Exit For
End If
Next FiveRow
FiveEnd = FiveEnd - 1
'Paste data into correct month available cells
Sheets(Month).Select
OpenRow = 2
Do While Cells(OpenRow, "A") <> ""
OpenRow = OpenRow + 1
Loop
Sheets("Data").Select
Cells(FiveStart, "A").Select
Range(Selection, Cells(FiveEnd, "Z")).Copy
Sheets(Month).Select
Range("A" & OpenRow).PasteSpecial
Application.CutCopyMode = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Code End'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
I have a large inventory sheet. My macro currently sorts all of the data by many traits and converts necessary cells to number format and numeric values. I need it to copy and paste only certain data into another sheet. The code below works well when the "string" is present in the column. In my example, I want to copy all data that has exactly a "5" in the appropriate column (VehCol) (don't include data such as say "658"). However, since the sheet's data changes month to month, the particular vehicle may not appear on the sheet. If there is no "5" in the column, I receive a "Run-time error '1004': Application-defined or object-defined error." The macro needs to do this for roughly 20 some-what random vehicle numbers. Currently, I have simply copied and pasted the "Vehicle Sort" code below for each different vehicle number, as the vehicle numbers may change in the future. How do I compensate if there is a lack of a vehicle 5 in the data?
Thanks in advance for any feedback.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''Relevant Code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Discover location of "Vehicle" column
'Vehicle
Range("A1:Z100").Select
Cells.Find(What:="Vehicle").Select
VehCol = Selection.Column
VehRow = Selection.Row
'****************Vehicle Sort**************
'Vehicles: 5,8,9,20,24,33,39,43,55,75,76,77,83,84,85,86,509,510,511,752
'Vehicle Five
' Figure out where "Vehicle 5" data starts.
Sheets("Data").Select
Dim FiveRow As Long
Dim FiveStart As Long, FiveEnd As Long
For FiveRow = 3 To 65536
If Cells(FiveRow, VehCol).Value = "5" Then
FiveStart = FiveRow
Exit For
End If
Next FiveRow
' Figure out where the "Vehicle 5" data ends.
For FiveRow = FiveStart To 65536
If Cells(FiveRow, VehCol).Value <> "5" Then
FiveEnd = FiveRow
Exit For
End If
Next FiveRow
FiveEnd = FiveEnd - 1
'Paste data into correct month available cells
Sheets(Month).Select
OpenRow = 2
Do While Cells(OpenRow, "A") <> ""
OpenRow = OpenRow + 1
Loop
Sheets("Data").Select
Cells(FiveStart, "A").Select
Range(Selection, Cells(FiveEnd, "Z")).Copy
Sheets(Month).Select
Range("A" & OpenRow).PasteSpecial
Application.CutCopyMode = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Code End'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''