Posts by vcoolio

    Hello Greenestdays,


    Automating the autofilter should do the task for you and be much quicker than a looping construct. Hence, try the following:-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello VA,


    Are you looking at copy/pasting rows 1-13 from sheet37 (assume this is the sheet code) to all the other sheets starting at A1? If so, try the code amended as follows:-


    You'll need to change Sheet37 to the actual name of your source sheet.


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Fullhouse,


    You're welcome and I'm glad to have been able to assist.

    I'm really happy that you spent the time foraging through information and working this out basically on your own. I laud you for that.


    If you're interested, below is a condensed version of your code (but excluding the date part that you added in):-


    If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.

    Column M is cleared at the end of code execution.

    As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.

    Anyway, once again "well done" and good luck with your project.


    I've attached your sample workbook with the above code implemented just so you can see how it works. There are about 230 rows of data in the sample.


    Cheerio,

    vcoolio.


    Fullhouse.xlsm

    Hello Fullhouse,



    Quote

    My understanding is that if abc.com is in the column E and i want fake company in the column D, then the column offset is -1, but I am not sure what do i put on for row?

    For each instance of abc.com, the offset for the same row but the previous column(D) is written as:

    .Offset(, -1)


    However, based on your last post, it appears that the sample you supplied is not exactly what you are working with so the best option is for you to upload a sample of your workbook which is an exact replica of your actual working workbook. If your data is sensitive then please use dummy data. We'll only need a dozen or so rows of data to test with. Please also include the code that you are presently working with. This will make it much easier for us to resolve this for you and will spare us guessing at what is supposed to actually be happening.


    Cheerio,

    vcoolio.

    Hello Fullhouse,


    You don't need the "AND" operator, so modifying your loop type code as follows may help:-

    You'll note that the last row has been defined so the code will loop each cell in Column A to the last row (the rows are defined by the variable 'i'), find the value 'abc.com' and add the text to Columns B and C. Based on the sample that you have supplied, Sheets("Sheet2") has the sheet code 'Sheet1' which I have used above.


    In this line of your code:-

    Code
    If Worksheets("Sheet1").Cells(2, 1).Value = "abc.com" Then

    I'm not sure if Worksheets("Sheet1") is a typo on your part.


    A loop type code will work fairly quickly on a relatively small data set but if your data set is large, or could grow to be very large, using the AutoFilter will be a far better option. For example:-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Alexiz,


    Should you want to stay with your current method, a worksheet_change event code should do the task for you:-

    With this code, each time you make an "Open" or "Close" selection from the drop downs, the data will be immediately transferred to its relevant sheet.


    To implement this code:-


    - Right click on the "Quotes" sheet tab.

    - Select "View Code" from the menu that appears.

    - In the big white code field that then appears, paste the above code.


    You'll need to first delete all existing codes that you have.


    I've attached your sample with the code implemented and all other codes removed. Test it to see if it's what you were hoping to achieve.


    I hope that this helps.


    Cheerio,

    vcoolio.


    Alexiz.xlsm

    Hello Intranet,


    Based on the information that you have supplied, the following VBA code may help:-


    Place the code into a standard module and assign it to a button.

    I've attached your sample workbook with the code implemented. Just click on the "TEST ME" button to see how it works. Play with the data to see how it is transferred to the relevant 'name' sheet.


    I hope that this helps.


    Cheerio,

    vcoolio.

    Intranet.xlsm

    Hello JH,


    Another option:-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Funfex,


    See if this at least heads you in the right direction:-

    You'll need to place your file path in the variable stgP.

    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello JLW-E,


    See if the following code does the trick for you (untested):-


    I've added a simple criteria column(Z) to determine if an entry is a new registrant. Place a "Y" in a cell and the code will do the rest.

    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello MAID1812,


    Here's another VBA option:-


    I hope that this helps.


    Cheerio,

    vcoolio.