Copy cells data to another worksheet if statement is true

[Arthfael]

Hello all,

I have a small problem that is really annoying me and I don't know how to solve it.
I have a bunch of rows in worksheet1 (starts from row 3 and goes down until 39) and each row has 15 columns.
In cell 15 for each row I have a formula that checks if that specific row is good or not to be sent to the worksheet2 ( =IF(G7+1<L7;"Yes";"No") )
If that formula gives Yes, then I need to copy Cell 1 , Cell 7 (but increment to value with +1) and Cell 13 to worksheet2 and put the in first free row.

Cell 1 is Text format and Cell 7 has a formula in it so I need to copy the data and increment it with +1

I have attached 1 picture for every sheet to it's easier to understand.
Worksheet1.JPGworksheet2.JPG


Thank you

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Arthfael]

Thanks for the reply

Here is an example workbook with the same formulas that I am using in the original workbook and with what I want to do.

VBA code that I managed to create but it's now working 100% :

Please Login or Register  to view this content.

And regarding what I want to do is this:
The macro should loop through rows 3 to 10 and check Cell 15 ("Post Order?" column) to see if it's "YES"
If the cell is "YES" for that specific row, then copy the ID, Count and Orders cell from that row and paste the values in Sheet2 in the first free row BUT incrementing the "Order" cell with +1

[satputenandkumar0]

try below

Please Login or Register  to view this content.

(Late Reply due to net down)

[Arthfael]

It works like a charm. Thank you

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[satputenandkumar0]

Glad to help you

Thanks JOHN H. DAVIS

[JOHN H. DAVIS]

Glad to help you

Thanks JOHN H. DAVIS

For what you just beat me to the punch.

[satputenandkumar0]

@ JOHN H. DAVIS

Sir,
I learned from you another way (Use Range with rows.count)

[alansidman]

Another Option:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Arthfael]

Thank you everyone for the help

[Arthfael]

Another Option:
....

For some reason when I try your code, I get ID / Order / Count in Sheet2 and not ID / Count / Order.

[alansidman]

Yup! Now that you mention it, me, too. I am guessing that the Union function takes the order of the ranges in alpha sequence regardless of how they are arranged in the code.

The good news is that you have two other solutions to work with.

Thanks for pointing that issue out to me.

Alan