Home/ Questions/Q 6128
Next
In Process
Computing Staff
  • 2
Asked: 2021-11-18T07:08:05+00:00 In:

VBA Sort Data, Then Copy Over The New Sheet

  • 2

So I have the simple spreadsheet named “Result”.

                      A            B
1                  009584         Over
2                  006583         Good
3                  005842         Good
4                  165823         Over

So I want to sort out the “good” status ref # and then copy it over to new sheet named “Sheet1”. What should be the appropriate way to do it? FYI, it is more than 250K rows, so it is why I am asking for VBA code. I know it I could use pivot table to sort and then copy & paste to the new sheet. However, I would like to learn VBA code for it to automate thing. Thank you in advance.

Share

    1 Answer

    1. First off, it looks to me like you are overwriting the last value in Sheet1 Column A every time you run the code.

      This line returns the row of the last cell with data in Column A:

      nxtRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

      This code pastes the first found row into that row and then increments the nxtRow variable:

            FoundNonCov.EntireRow.Copy
        Sheets("Sheet1").Range("A" & nxtRow).PasteSpecial
        nxtRow = nxtRow + 1

      Seems to me like you are overwriting whatever is in the last cell of Column A. Perhaps you should increment the row counter before you do the paste.

      As far as the 150 count difference, I have no way of knowing why that difference exists since I don’t have a copy of your data.

      You could trying using COUNTIF to see how many instances of “Good” you have in the Range(“B2:B” & lastQueryRw) and see if the same number of rows were copied.

      Keep in mind that the VBA .Find method is going to use whatever options are set in the Find/Replace wizard within Excel (not VBA) unless you specifically set them all within VBA. For example, if the wizard is set to match “Match entire cell contents”, your VBA won’t find “Good ” (that’s Good with a space after it.) Perhaps that is why you are getting a difference in the count.

      If you are going to be writing VBA code, perhaps you should spend a little time reading this How To. It’s pretty basic, but it’s good place to start. You can go much deeper by searching the internet for the things that you want to do and including VBA in the search string.

      http://www.computing.net/howtos/sho…

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0