+ Reply to Thread
Results 1 to 26 of 26

Macro to copy content between columns based on name of company

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Macro to copy content between columns based on name of company

    Hi, i have little bit harder case and do not know how to solve this...

    I have some references number in column AE..not every row has references, there are also blank rows between them...and I need for each references, to check column AD, where are company names as CHANG HWA,CONCORD SECURITIES CORP. etc and for one references there can be more rows with same company name...and I need to copy from column N -ordernumber to column AG for each company...meaning to search all rows and copy all references for one company to one row...For example company CHANG HWA - references are in format 00007654353 00009837308 and i dont need zeros, just follows after them and paste it as 7654353 9837308 for example for company CHANG HWA to one row next to the reference from column AE

    I hope its little bit clear what i need to...for every input i will be glad..

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,097

    Arrow

    Hi,

    I hope the top page yellow banner is enough clear

  3. #3
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    I uploaded the attachement with coloured examples what should be done....just ignore the names of columns because I erase some columns and left just necessary there..
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,149

    Re: Macro to copy content between columns based on name of company

    If I understand correctly then this formula based proposal yields the results you want.
    This proposal employs a helper column (H) which may be moved and/or hidden for aesthetic purposes.
    Column H is populated using: =IF(D2<>D1,RIGHT(B2,7),H1&" "&RIGHT(B2,7))
    Column G is then populated using: =IF(D2<>D3,H2,"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    Hello,

    is there any possibility to use this formula into macro? Because I built macro that is deleting some columns, replacing something, copy&paste something and this my thread is another step in the procedure...or is there any other solution,which can be added to my current macro?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    Please Login or Register  to view this content.
    You can use the function alone as a cell formula.
    =JoinIf($B$2:$D$26,3,D2,1," ")
    Where:
    $B$2:$D$26 as data range.
    3 as relative column reference for lookup column within data range.
    D2 as lookup value
    1 as relative column reference for concatenate range within data range.
    " " as a joining string.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    it looks ok Jindon,but small adjustment if is possible -

    for now, it will give references to each row..but in my excel sheet I need it that -

    for example row 13 & 14 CHANG HWA - we can see in column E (Nieuwe referte) that references is only on row 14,row 13 is blank...so I need to get result to column G (refertes) just for the row 14 - 00007351779 00007351806 and leave row 13 in column G blank...is it possible to adjust somehow?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    code change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Mannualy entering the formula in the cell:
    =If(E2<>"",JoinIf($B$2:$D$26,3,D2,1," "),"")

  9. #9
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    Hello again, when I tried run your macro, it worked fine in your file Jindon...but when I copy to my macro and did small adjustments, it do nothing...can you please have a look if the code is correct? There are already adjustments according to correct columns in my file + adjustment of code from your last post..

    Please Login or Register  to view this content.
    Can be problem,that I have opened in the same time two workbooks and both have same sheet -qryFaxTai?

    Thanks for cooperation.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    That's not "my" file, "your" file originally.

    If the data layouts are different from your original file, need to see your true file.

  11. #11
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    Ok I attached correct file
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    Change to
    Please Login or Register  to view this content.
    Edit: Formula changed to leave blank when condition is not met.
    Last edited by jindon; 11-22-2019 at 08:08 AM.

  13. #13
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    I don't know what's wrong else, when I run macro, it gave #NAME? error...is there any mistake in formula?

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    ......................
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    Jindon I dont really know whats wrong...in the example where you added the code it works perfectly, but when I copied to my original macro file, it gives error
    I uploaded file with erro, anyway, macro is not saved in this file and also are needed other files, which are connected with whole macro I builted...but I do not think that it should be problem
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    The error is in the JoinIf function...it gives #Value! error for third and fourth parameter....but it is absolutely same as in your code and your is working...so it seems that problem could be somewhere in my worksheet/workbook?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    It will never work on .xlsx file, it should be .xlsm with the code like the file I attached.

  18. #18
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    ah.....but thats the problem, because main file is every month different, so I cannot have that macro in the file I need, so I have whole macro stored in another excel file and using this macro file to run the macro in xlsx file....Is there any other possibility how to solve this problem? btw. thanks a lot for your help, I appreciate that you have nerves for my problems..

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    Google

    Perosnal.xlsb or Add-In

    As you asked in VBA forum, I wrote the code but if you don't like it, just forget it.

  20. #20
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    I like your code,but I dont get one thing..

    why that your code does not work?

    For me come email with attachement in .xlsx. I created macro in separate file, code do lot of things - deleting columns,rows,sorting,adding something etc...one part was that contain you create code for....

    But in that macro file through Customize Quick Access Toolbar -> Macros and I added this macros here....and whenever I open .xlsx file and run the macro, it works and do all the things and at the end it opens that file with macro.....so code runs fine, but when I added your part, it starts to give errors and I do not know why.....

    Why whole macro works fine in xlsx file and your part did not work? whats the difference and whats the solution for that?

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    No idea how are you calling the sub/function procedure.

    If you are applying to the other workbook, you should change the sheet reference to ActiveSheet
    Please Login or Register  to view this content.
    If you save the workbook with this codes as an add-in, .xlam. and set the reference to it, you can use it all the excel files within your PC.

  22. #22
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    I solved with add-in and works fine...may I ask, if after End function in this code - can I continue somehow with writing other comands for VBA? or needs to be created new macro?

  23. #23
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    Hello Jindon,

    your formula works fine, but if would be possible small adjustment - the references should be without zeros - 0007752536 should be just 7752536..is it possible? or where in the formula can i change it?

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    Do you mean?
    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    11-12-2019
    Location
    Moravia
    MS-Off Ver
    2016
    Posts
    37

    Re: Macro to copy content between columns based on name of company

    It worked well,but last days there pop up some error message- Object variable or With block variable not set...it seems that some variable is not define? But why it worked fine and not now? I dont get it..

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Macro to copy content between columns based on name of company

    No idea.
    I don't think the cause is the function.

    If you upload a workbook that doesn't work, it may help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Automatically copy individual company rows into separate company sheet
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-24-2016, 03:24 AM
  2. Macro to find and add ISIN for a company based on stock codes
    By itshewe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2014, 01:28 AM
  3. Automatically copy cells content based on the content of another cell.
    By chrisbarlow1984 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2014, 11:13 AM
  4. [SOLVED] Macro for copy all info related to one company paste to its own new tab
    By rochakmehta90 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2013, 04:46 PM
  5. [SOLVED] Macro to copy 3 adjacent columns at a time to a separate worksheet & sort by Company name
    By anya1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-20-2013, 06:50 AM
  6. am i able to copy data from one sheet onto another based on a company hierachy!
    By obeo2k2 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-08-2012, 11:21 AM
  7. Replies: 5
    Last Post: 10-01-2010, 05:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1