copy multiple rows and sort based on cell values

If some body could help with copying a range of rows where the rows chosen are based on the value in one cell, highlight the row and copy it. Do this for all rows containing the same value in a call. Then more onto the next value an append to the bottom of the first list:

Below is my attempt at explaining what I wish to achieve - hopefully the above will help explain more my dilema.

I have looked around for this but not quite found what I want. i thought it would be simple and probably is. I receive a data dump with thousands of rows of data and 18 columns. Based on the value of column P "Contract" I want to copy entire rows into a new single worksheet "workingdata". Not all the data will go into the workingdata worksheet.

The contract numbers are c1234, c1235, c2345 etc.

What i am after achieving is copying and sorting, so copy all the rows of data where contract number is c1234, in workingdata, then directly below it copy all rows where contract is c1235 and so on.

I thought I could select the range P:P andsort but to no avail. Sheets("Data Dump").Select Columns("P:P").Select If Selection.Value = "C1234" Then Selection.EntireRow.copy

I know I should post what i have tried, but it would be a pathetic, for some reason I just can't seem to get my head round this one

stevenwhite1968,

I'm a little confused on what you're actually looking for. It sounds like you have a dataset and in that dataset's column P are contract numbers.

Then do you want option A or B?

Option A:
Extract only items in the dataset with a certain contract number to a separate worksheet ("workingdata")

Option B:
Sort the entire dataset by contract number so that c1234's are on top, c1235's are next, etc.

~tigeravatar

Option A. I am reasonable with VBA, I have got it to automatically delete all blank lines, but this one is stumping me. Your help would be appreciated.

Option A, but its with multiple contract numbers, so I would need to append each contract at the end of the last one, ie. filter out the contracts I dont want and sort the ones I do. hope this helps.

An effort that i know does not work!

Dim oWorksheet As Excel.Worksheet
Dim oRangeSource As Excel.Range
Dim oRangeDest As Excel.Range

Set oWorksheet = Worksheets("DataDump")
Set oRangeSource = oWorksheet.Range("p:p")
Set oRangeDest = Worksheets("workingdata")

If oRangeSource="CA0004000" Then Select.EntireRow

Selection.EntireRow.copy
Sheets("workingdata").Select.Paste
End If

stevenwhite1968,

To pull different contract numbers, you'd have to tell excel which contract numbers to pull. A good way to do this is to use a filter. You can use the filter outside of VBA, just in the worksheet and select what criteria (contract numbers) you want it to show. After the data has been filtered so that only the desired contract numbers are displayed, run the following macro:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

This copies my data that I need but not in order

Sub copytest()
'
' copytest Macro
'
Set MR = Sheets("data Dump").Range("P:P")

For Each cell In MR

If cell.Value = "CA000154" Then
cell.EntireRow.copy
Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

End If

If cell.Value = "CA000220" Then
cell.EntireRow.copy
Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value = "CA000393" Then
cell.EntireRow.copy
Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value = "CA000429" Then
cell.EntireRow.copy
Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Application.CutCopyMode = False

Next

End Sub

stevenwhite1968,

The following is code to sort the contract numbers. Just add it to the end of your current macro. This code assumes that the contract numbers in "working data" start in row 2 (column P). If that is not the case, change the DataStartRow to be the row number that the contract numbers start on.

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Many thanks -