Copy WHOLE Row if citeria matches

Hi Everyone

Really basic user here but please can someone help me with the attached spreadsheet. I need to be able to copy the information of the WHOLE row automatically if the criteria in 'N' shows 'SOLD'. I dont want to have to run a Macros (pressing Alt + F8 everytime) if I can help it. Or if a macros is running will this automatically update when new information is updated?

The spreadsheet will be continually updated therefore I need the Macros to be set to an extremely large row range, or if someone can advise which part of the Macros needs to be updated for this.

Thanks

Hayley

what do you mean by copying the whole row? can you be more specific? you can filter the table that shows 'SOLD' and then copy everything from there. Is this what you meant? Sorry I don't quite get it.

wayusei - for some reason it is now saying my project is locked when trying to create the macros?!?!

rename - I dont want to have to manually filter the information on sheet1. I want the macros to automatically copy the whole row if 'N' shows sold. for example it would pull the customers name, address etc

I Got that same error when I opened the file you provided. I thought it was something you intended. To work with your data I copied it into a new workbook.

wayusei I did the above and copied the above but got a suntax error

Your file vba is locked is because ur file is shared file and excel does not allow macro to run in shared file. you need to unshare the file before runing any macros.

yes I had. Whoops. The problem is this moves the information over not copies it

Sorry my bad, I thought you wanted the information moved. Just remove these codelines

Please Login or Register  to view this content.

That did it. Wow thank you so much!!!!!!! I never would of known how to create that! Very grateful!

wayusei - if I wanted to add more sheets so sheet 4 does the same filter of the info on sheet 3 (all the same format) how would I amend the code?

Change these lines

Please Login or Register  to view this content.

In the original code they're named "Hoja1" and "Hoja2" because I'm using Excel in spanish, but changing that two names should work for any sheets. Of course, if you want to use this code for several sheets you have to copy and edit the code.

Yeah I did this and amended the above section however this came up with the syntax error again

Did you renamed the macro?

I added a new module and just amended the above info

I think i have sorted it. You cant get this to work across different files can you?

Ok. I edited the macro on the lines and changed the macro name. Again this is named Hoja3 and Hoja4 because I'm using excel in spanish.

Please Login or Register  to view this content.

Not right now, but it'd make a nice project for tomorrow if you don't mind. Please provide me the path for each file, and the names used by each worksheet (Data and Output Worksheet, and Data and Output Path and Filename)

Z:\Sales leads\call centre 2013.xlsx 'sheet 1'
to be input into
Z:\Filters\Commisions.xlsm
Sheet2

If you can do this it would be AMAZING

Here you go.

Please Login or Register  to view this content.

Remember the name of the Workbooks, Worksheets and Path to Output Workbook.

Data> Worksheet = "Sheet 1"
Workbook = irrelevant
Path = irrelevant

Output> Worksheet = "Sheet2" (without spaces)
Path & Workbook = "Z:\Filters\Commisions.xlsm"

Try it and reply.

Hi

Thank you for this. I tried it this morning and got the attached error
Any thoughts?

MMM maybe a hidden worksheet? Sometimes this causes trouble running macros. The code seems to be working fine on my PC. :/

None of the worksheets are hidden. Are you able to attach the sheet you have that it works on?

Sure, here you go

Yeah it works on yours. Thats amazing how you have created that! This will save so much copy & pasting for me and the risk of error. Thank you so much

Hoja3 doesnt seem to pull through all of the rows documented on the sales leads 2013

There is no code working on "Hoja 3". You could easily edit the code just by changing the variables, macroname, and destination name.

Oh this is my fault. Think I was lost in all the coding

I needed hoja3 to pull through the information from file path Z:\Sales leads\CALL CENTRE 2013.XLSX
hoja1 to pull though Z:\Sales leads\CALL CENTRE RESULTS.XLSX

wayusei are you able to help please?

I don't understand what is what you need.