Copy row from one worksheet to another if marked by X

[ghoshl1]

Hi Excelers,

I'm trying to look through multiple rows and if marked by 'X' in a column - copy that row to another worksheet...

ideas?


Help???

[arlu1201]

Sure. Couple of questions.

What is your last column having data?

Which column will contain the "X"?

[ghoshl1]

Hi Arlu..

The "X" is in column J... the data is stored in colunm B through E...

[arlu1201]

So only columns B to E should be copied to the other sheet? The rest ignored?

[ghoshl1]

No I'm sorry - it is B through E; but ignore everything else

[arlu1201]

Try this code

Please Login or Register  to view this content.

Change the parts marked in bold as per your file.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[ghoshl1]

Hi Arlette,

It sort of worked... I have columns on the second worksheet so I need to add the rows in the first empty row... this code added columns b through e but into A through d and moved out the column headers to the right...

Is there a check to add it below?

thanks,

[arlu1201]

What data should be entered into A and D? You mentioned that your dataset is from column B to E.

[ghoshl1]

Sorry..

the data is coming from columns B through E; but on the worksheet it is being copied onto it would go into Column B underneath already entered information... so it is adding to the bottom of what is already in column B

ie.

Data A Data B
1 2

New data New data

[arlu1201]

Yes, it will add the data below the existing data. Where do you want it to be entered? Do you want the existing data in the 2nd sheet to be cleared and then fresh data input?

[ghoshl1]

Hmm both - I first would need it entered in column B also... but the headers should not be cleared... they would always be in rows 1 through 7... so the first row to check would be 8... so I would then add to the bottom of this until I ran something else that would remove the entries (starting at row 8 from the worksheet being copied to). Then I would start adding the entries again...

[arlu1201]

So if i understand it clearly, the headers are from rows 1 to 7. You may have data from row 8 onwards which the macro needs to clear and then add new data?

[ghoshl1]

I actually need the data entry and the data clearing to be two separate macros. I'm adding data from 3 different worksheets onto the one - so each one adds the data below the last. That will create one worksheet with the 'x's from the other sheets.

If they like what they see - they print. If they don't they need to remove the data that was transferred and start over.

The data "entry" row is 7 and goes as much as it needs to down. The formatted columns (to not be changed) are in rows 1 through 7 (columns all the way through R).

[arlu1201]

Whats the name of the Summary file? I can give you a code to add the data from all 3 worksheets.

[ghoshl1]

So the three worksheets I'm copying from are:

"Core Library"
"CNS Library"
"ONC Library"

and the sheet I'm moving the data onto is "SoA Arm_1"

[arlu1201]

Try these 2 codes

Code1 - to consolidate the data. Considering that your data begins in row 7, i have written the bold line that way. You can change the 7 to whatever row your data begins in.

Please Login or Register  to view this content.

Code 2: To clear the sheets.

Please Login or Register  to view this content.

[ghoshl1]

trying now...

[ghoshl1]

This looks like it is working - thanks!!