Databases
StackOverflow2013
Postsdbo
POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
    primarykey
    data
    text
    <p>I'm trying to copy a range of rows where the rows chosen are based on the value in one cell.I want to do this for all rows containing the same value in a cell, then move on to the next value an append to the bottom of the first list.</p> <p>Below is my attempt at explaining what I wish to achieve - hopefully the above will help explain more my dilemma. I have looked around for this but not quite found what I want. I thought it would be simple and probably is.</p> <p>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 <code>workingdata</code>. Not all the data will go into the <code>workingdata</code> worksheet.</p> <p>The contract numbers are c1234, c1235, c2345 etc.</p> <p>What i am after achieving is copying and sorting, so copy all the rows of data where contract number is c1234, in <code>workingdata</code>, then directly below it copy all rows where contract is c1235 and so on.</p> <p>I thought I could select the range P:P and sort but to no avail.</p> <pre><code>Sheets("Data Dump").Select Columns("P:P").Select If Selection.Value = "C1234" Then Selection.EntireRow.copy </code></pre> <p>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.</p> <p>Here's my latest effort - I know there are errors</p> <pre><code>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 </code></pre> <p>latest effort but does not sort data or get rid of unwanted, I have to do a manual filter and sort which sorts of defeats the object of the macro</p> <pre><code>Sub copy() ' ' copy Macro ' Dim rngContracts As Range: Set rngContracts = Sheets("DataDump").Range("P:P") Dim wsData As Worksheet Dim wsFound As Boolean: wsFound = False Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name = "Working Data" Then Set wsData = ws wsFound = True Exit For End If Next ws If wsFound = False Then Application.CutCopyMode = False ActiveSheet.Range("A1").EntireRow.copy Set wsData = Sheets.Add(After:=Sheets(Sheets.Count)) wsData.Name = "Working Data" wsData.Range("A1").EntireRow.PasteSpecial xlPasteAll, Transpose:=False End If Dim iCell As Range For Each iCell In rngContracts If iCell.EntireRow.Hidden = False Then Application.CutCopyMode = False iCell.EntireRow.copy wsData.Range("P" &amp; Rows.Count).End(xlUp).Offset(1, 0).EntireRow.PasteSpecial xlPasteAll, Transpose:=False End If Next iCell Application.CutCopyMode = False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub </code></pre> <p>Latest attaempt - copies the data I need but does not sort:</p> <pre><code>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" &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If If cell.Value = "CA000220" Then cell.EntireRow.copy Sheets("working data").Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If If cell.Value = "CA000393" Then cell.EntireRow.copy Sheets("working data").Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If If cell.Value = "CA000429" Then cell.EntireRow.copy Sheets("working data").Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If Application.CutCopyMode = False Next End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSteveW1968
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COIf I understand you correctly part of your problem is how to sort your table. For this it is not enough to select the column P that you want to sort by; you need to also select the 17 other columns that should get sorted! Could you describe more clearly what you want your code to do, apart from sorting? Is there some criterion for not copying some rows, since you say "Not all the data will go into the `workingdata` worksheet?"?
      singulars
      1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
      1. USJonas Heidelberg
    2. COUsing column p to sort the data, ie get rid of contracts i dont want, but for the ones I do want, group them together and copy to a new sheet. But I need to copy the whole row for the contracts I do want.
      singulars
      1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
      1. USSteveW1968
    3. COHave you worked with "Watches" in VBA before? They can help you understand what your code is actually doing. For example, the If comparison that you do (which lacks an End If by the way) compares an array (since you select a full column) with one value. You would want to add a For loop to your code, going through the cells of column P one by one...
      singulars
      1. POExcel 2007, Copying rows from one sheet to another based on a value in 1 column
      1. USJonas Heidelberg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload