Macro to automatically copy and paste rows to different Sheet

Hi,

I have updated the files to reflect the 'expected' result. The worksheet 'ColumnF' should copy up to Column E from the worksheet 'Names.' If you view the file now the worksheet "ColumnF" will show all names with 'Yes' under ColumnF Thanks.

Anyone else that can help with this? Thanks...

The code is in sheet name and is fired-on by changing column F to "Yes"

If you are referring to the above code, it copies, starting from 2 in sheet2, so I do not know which one do you want to change.

Oops,
Change this line

Please Login or Register  to view this content.

Thank you, that resolved the column names issue. But, If I wanted to start copying from the 4th or 5th how should I modify the code? I'm pretty new to VBA, thanks for helping

Please Login or Register  to view this content.

TO

Please Login or Register  to view this content.

Thank you AB33,

I would like to have Sheet1 to automatically execute the macro in modules, whenever the value in columnF get changed. I found a piece of code for this online but it is for a cell range instead of a column,

Please Login or Register  to view this content.

I have changed the code above to reflect the macro on dtest file which is called 'test'

Please Login or Register  to view this content.

But I'm not sure about the syntax to use for setting up the columnf as range... please help. Thanks

Use and amend the code I gave you earlier (post #5)

Thank you, I have modified the code you gave earlier and added to 'sheet1,' and it seems to be updating sheet2 automatically. I have also attached a sub process to this code in sheet1, which pops a message box whenever the ColumnF is modified. I'm curious why this message box appears when I run the macro on Sheet1 from VBA, but it doesn't show the message when I update things on the worksheet. I have pasted the code below. Thanks again AB33!

Please Login or Register  to view this content.

You should not mix, standard module codes with event driven codes. If you firing the event code from the module, that code should not be on sheets, but on standard module, so the sub process should be on standard module and sub change even code on the sheet the change events takes place.

Hi,

Thanks for the reply, so should I be placing the sub process for the message box on standard module? I have added to the bottom of the code on module and left sheet1 with the

Sheet1:

Please Login or Register  to view this content.

Module1:

Please Login or Register  to view this content.

But this still not showing the message box...am I doing something wrong here? Thanks

You have called

Please Login or Register  to view this content.

But not Sub Process()

Or you could include the message within the test code, like

Please Login or Register  to view this content.

Great! That works like a charm.

One more thing, the code on Sheet1 automatically updates Sheet2 if we input 'x' under columnF, but the automatic update fails, when we reverse the 'x' to 'n.'

But it works fine if we run the macro from VBA. Any suggestions? Thanks AB33.

This line again, it says "x"

Please Login or Register  to view this content.

You also need to change the x in to n in sub test

Hi AB33,

If I were to change "x" into "n" then the macro will only copy rows if the values are "n" under ColumnF.

The macro on module1 is already copying rows when x values are placed in ColumnF, but the automatic update code on Sheet1 copies rows to Sheet2 and fail to reflect changes on Sheet1 if values are changed from 'x' to 'n'

When I execute the macro form VBA it seems to be updating Sheet2, reflecting changes made to Sheet1.

Is there a way to modify the code on Sheet1 to reflect changes and apply them to Sheet2? If this is not possible,
can you help me with creating a button on Sheet1, which would run the macro on module1? Thank you.

Please Login or Register  to view this content.

Hi AB33,

I also have another question, I understand the above piece of code define the copy range of the rows from Row1 etc, but what about the pasting range, what If I wanted to paste starting from Row5th per say? Thanks.

I have re-pasted the code on Module1 below. Thanks again for helping me with this

Please Login or Register  to view this content.

This is going to be my last post. I spent few hours on this thread and does not seem the end is in sight.
If you have data in row 4 and nothing below it, the next empty row would be 5, so this line works.

Please Login or Register  to view this content.

But if this is not the case.

Please Login or Register  to view this content.

Thank you AB33, that seems to work. Thank you for helping me with the macro. I do not intend to takeaway your personal time. I will be doing some more modifications to the file and if I need anymore help, I'm sure someone else would be able to help here or I'll seek help elsewhere.

I really appreciate your help.
Thanks and have a nice day.

I'm glad to say I was able to manipulate the codes to my need Problem is resolved! :D