Posts by 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
    1. 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.

    Hello Anna,


    Column 9 is not included because, in your sample output, you have not included it (Coco).

    If you need it to be included, add 9 after the 8 in the array and change the resize value to 12.


    The array appears random but is in fact quite precise. You have asked that columns K, L and M (10, 11 &12 based on your data setout) in the Input sheet be placed in columns E, F and G in the Output sheet starting at B4. The positions of the columns in the array determines the positions in the Output sheet.


    I don't quite follow your last request so, seeing that your supplied sample is not truly indicative of your actual workbook and its data, please supply a new sample set out exactly the same as your actual workbook including the data within the columns (ensure that it is still dummy data).


    Cheerio,

    vcoolio.

    Hello Anna,


    See if this heads you in the right direction:-


    I've attached your sample workbook with the code implemented. The "Output" sheet is empty. Click on the "TEST ME" button to see how it works.


    I hope that this helps.


    Cheerio,

    vcoolio.


    Anna.xlsm

    Hello DezB,


    In reply to your questions:-


    Code
    1. .Offset(1, -15).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    With the code in post #5, Column P(16) is the criteria column on which the filter is applied. Once applied, the filtered data to be copy/pasted starts on the next row down from the headings row, hence: .Offset(1). However, since the criteria is in Column P and Columns A:P are the required columns to be copy/pasted, we need to resize from Column A (otherwise .Offset(1) will only copy/paste the "Yes" criteria). Hence .Offset(1,-15).Resize(,16). The -15 takes us back to Column A and the resize property (,16 columns) does the rest.


    .End(3) (2) is simply the enumeration for .End(xlUp).Offset(1). They are exactly the same thing, just less typing!


    If row2 in your data set is completely empty, then the code could be written as follows:-


    You'll note the Autofilter is now set to: .AutoFilter 16 (to identify Column P within the CurrentRegion).


    The copy line of code is simply


    Code
    1. .Offset(1).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    which takes the entire (entire rows) filtered data set and copy/pastes it. If you're only interested in Columns A:P and nothing beyond, then you could use this:-


    Code
    1. .Offset(1).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)

    With more columns, just change the resize value. If the precise amount of columns to be copy/pasted isn't an issue then just change

    .Resize(, 16) to .EntireRow


    All in all, the CurrentRegion method is a very versatile way of doing tasks like yours so, if you add more columns, they will be instantly added to the whole procedure. You'll only need to change the reference to the criteria column.

    Take note though, when using the CurrentRegion method, if your data doesn't start in row1 with headings, then the data needs to be bounded by at least one empty row and one empty column.


    Another method is to define the range to be filtered:-


    This is the range you're interested in (it's also fully qualified with the worksheet variable (ws1):

    Code
    1. With ws1.Range("A3", ws1.Range("P" & ws1.Rows.Count).End(xlUp))

    and you'll note that the filter is still set on Column P(16).


    There are more options which can be used to do the same task but between this post and Gijsmo's post #14, your head is probably spinning a little.

    The method used is basically a Coder's preference.


    I hope you've been enlightened somewhat.


    Cheerio,

    vcoolio.

    Hello Railea,


    I've assumed that you'd want a button on each of the two source sheets simply because each source sheet will be worked on separately/individually and when a User has the need to transfer data as a matter arises. Hence, try this:-

    As you can see, there are two codes above (identical other than the sheet references) which will operate separately on the individual source sheets.

    The codes clear the destination sheets with each transfer of data as I've also assumed that you would need to keep all data in the source sheets. This will basically refresh the destination sheets and prevent duplication of data.


    I've attached your sample workbook with the codes implemented and assigned to the buttons on the two source sheets. Have a play with the file and see what you think.


    Take note of what Roy has said about properly using all rows; that's what they're for! ;)


    I hope that this helps.


    Cheerio,

    vcoolio.Railea.xlsm