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

       ActiveCell.EntireRow.Delete
       i = i - 1
       Filas = Filas - 1

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

    Set OutSH = Sheets("OutputDataSheetName")
    Set DataSH = Sheets("DataSheetName")

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 think i have sorted it. You cant get this to work across different files can you?

I added a new module and just amended the above info

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.

Sub aa2()
    Dim OutSH As Worksheet
    Dim DataSH As Worksheet
    Dim Filas As Integer
        
    Set OutSH = Sheets("Hoja4")
    Set DataSH = Sheets("hoja3")
    
    Application.ScreenUpdating = False
    
    Filas = DataSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    OutSH.Select
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    
    DataSH.Select
    Range("N2").Select
    
    For i = 1 To Filas
        If ActiveCell.Value = "SOLD" Then
            ActiveCell.EntireRow.Copy
            OutSH.Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, 0).Select
            DataSH.Select
            
        End If
        
        Cells(i, 14).Select
    Next
    
    Application.ScreenUpdating = True
    Range("N2").Select
    

    
End Sub

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.

Sub aa()
    Dim DataWB As Workbook
    Dim OutWB As Workbook
    Dim OutSH As String
    Dim DataSH As String
    Dim Filas As Integer
    
    DataSH = "Sheet 1"
    OutSH = "Sheet2"
    
    Set DataWB = ActiveWorkbook
        
    Workbooks.Open ("Z:\Filters\Commisions.xlsm")
    Set OutWB = ActiveWorkbook
    
    DataWB.Activate
       
    Application.ScreenUpdating = False
    
    Filas = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    OutWB.Sheets(OutSH).Activate
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    
    DataWB.Sheets(DataSH).Activate
    Range("N2").Select
    
    For i = 1 To Filas
        If ActiveCell.Value = "SOLD" Then
            ActiveCell.EntireRow.Copy
            OutWB.Sheets(OutSH).Activate
            ActiveSheet.Paste
            ActiveCell.Offset(1, 0).Select
            DataWB.Sheets(DataSH).Activate
             
        End If
        
        Cells(i, 14).Select
    Next
    
    Application.ScreenUpdating = True
    Range("N2").Select
    

    
End Sub

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

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

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

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.