Need to copy only part of a row if conditions are met and paste into new worksheet.

    I have written this code to copy a row if the condition is met. However, It was decided that not all the data from the Row is needed, how would I only copy Columns A:L for each row that meets the condition and put it into the second worksheet as shown.


  • Go to Best Answer

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    Roy, I attached my workbook. I am sure that there is a lot better way to do this. I am very new to programming Excel so I know it is probably very poorly written. Any help would be greatly appreciated. The workbook is not finished, there are still more things I need to get to do, currently it creates the Hot List based off the raw data, verifies if it was on the list the previous week and is still on the list or has fallen off, and sorts the list. The issue I am having is I do not need all the columns that are in the raw data to go to the hot list sheet. I just need Columns A:L if the criteria is met.

    Do you want to remove any existing data on the Hot List sheet or add to any existing data?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    • Best Answer

    Hello

    I think you need to replace this line of code

    Code
    Worksheets("Raw Data").Rows(i).Copy


    with this one

    Code
    Worksheets("Raw Data").Range("A" & i & ":L" & i).Copy



    Let them know. Hello,

    Mario

    Ultimately, the Hot List will erase itself at each update. Each week when a new report comes out, the old Raw data will move to a new sheet automatically. The when I populate the raw data and hit update on the Hot List sheet the old data on the hot list sheet will disappear and be repopulated with the New Data from the Raw Data Sheet. This I know how to write, I just haven't gotten to that step yet. So far the only thing I am struggling with is copying only Columns A:L for rows that meet the criteria and moving it to the hot list. Any Help would be greatly appreciated.

    Try the attached, click the button on Hot List Sheet. Any existing data will be cleared and new data added.


    Code assigned to the button:


    You will find the code runs much faster than the original, especially if data set is large

    An alternative is to employ Power Query


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Last Takeoff EGTHDM SMOOTH current week]<=10 then 1 else 0),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login