+ Reply to Thread
Results 1 to 24 of 24

Add two more columns, modifying an existing VBA code help.

  1. #1
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Add two more columns, modifying an existing VBA code help.

    Hi all.

    I want some help, to modify this VBA code. I want to copy additional columns Z and AA, the even numbered rows, starting with Z8.
    Thank you in advance.

    Please Login or Register  to view this content.
    Last edited by Anka; 02-07-2013 at 04:22 PM.
    Many thanks to all who have helped me.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Modify VBA code

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

  3. #3
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Modify VBA code

    Hi zbor.

    I do not know if my post title is better now. Accept any suggestion.
    And what do you think about VBA code. You can give any help. No matter how small ..

  4. #4
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    With my poor knowledge, I changed (added another part). Miraculously works, but not correctly. It copies one extra row. After the last row that contains something in column B. Is empty. I do not need it.

    Please Login or Register  to view this content.
    Any help, no matter how small .. is greatly appreciated.

  5. #5
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    If necessary to pay, please tell me the price.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Add two more columns, modifying an existing VBA code help.

    All help is free (volunteers, home, sparetime, etc.). You have to be patient, especially on the weekends. Post sample workbook for testing.
    To Attach a Workbook:
    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit
    Last edited by protonLeah; 02-11-2013 at 09:28 PM.
    Ben Van Johnson

  7. #7
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by protonLeah View Post
    All help is free (volunteers, home, sparetime, etc.). You have to be patient, especially on the weekends. Post sample workbook for testing.
    Ok. I am glad to hear that.
    Here is the sample workbook for testing.
    Attached Files Attached Files

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    Here, try this.

    This would check value in each sheet in B column. Then if something is in corresponding G column it will coppy all needed data.
    That means for each name (For example B11) you must not have blank in G11.

    rather that going to every second row.
    Is this OK?
    Attached Files Attached Files

  9. #9
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post
    Is this OK?
    First of all, I must thank you for the help.
    Yes. Works almost as I wanted. You missed just a small detail.
    I want to link (cells) data from all sheets (B30=Sheet2!B7). The cells receives data online. If only I copy them, when they are updated, copied cells will not have the same values​​.
    It's a little slow. I say this because I do not know it's normal to be, or I did something wrong?
    Mine original workbook can have more than 30 sheets (20 on average).

    Thank you

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    Now it will look all worksheets in workbook (no need to define them) except CONTROL.

    Didn't understand part with links. You have url link on each value?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by zbor; 02-13-2013 at 04:41 PM.

  11. #11
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post
    You have url link on each value?


    Nope.
    Not a hyperlink, just a simple link between copied cells.

    Thanks again for your patience and your efforts.
    Your first VBA works great.

    My English is very poor. Everything is copied must be pasted as links.
    Please see what the original VBA make, if you can.

    Your code paste as valu.
    Original paste as link. Paste link:=True
    Last edited by Anka; 02-13-2013 at 07:22 PM.

  12. #12
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    I attach a photo to see a comparison between VBA's. In this way you can easily understand.
    Attached Images Attached Images

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    OK, got it:

    Please Login or Register  to view this content.
    Don't forget: IT will pull data from each sheet if there is something in G column. That's how I see esxample. Write if this is not a case.
    Attached Files Attached Files

  14. #14
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post
    Write if this is not a case.


    Hi zbor.

    Again a big thank you for your effort.
    The first code (who paste as values​​), works ok in my original sheet.
    I do not know why but this time, the second code you provided does not work ok in my original sheet.
    He copies all rows instead going to every second row.

    I do not know if it helps much .. some detail.
    Normally, the main (principal) data is in column B. If there is something automatically in column G (at least) is something. But this is not a problem because this part is easy to change, I think.
    I have and some hidden sheets. For this I need to define where to look.
    I have tried to change it, but so far .. only failure.
    Last edited by Anka; 02-14-2013 at 11:17 PM.

  15. #15
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    It didn't go to every second row.
    Code check criteria:
    Please Login or Register  to view this content.
    So if ALL your data in B column has corresponding value in G column it will copy all of them.
    If you don't have every 2nd row in G column blank then we need to change this criteria.

  16. #16
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post

    If you don't have every 2nd row in G column blank ..
    Then this may be the problem. G and H are the only columns that have values ​​in all rows.
    Last edited by Anka; 02-16-2013 at 10:18 AM.

  17. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    How about header 4 and 5.
    I mean, I can make solution with every 2nd row.
    But if not, what would be other criteria for data you want to extract?

  18. #18
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post
    How about header 4 and 5.

    But if not
    Excuse me but I did not understand exactly what you mean with header 4 and 5.
    That would be a problem. Other criteria? I have not.
    Maybe you can inspires in the original VBA and add only the other columns. Maybe it's easier? I do not know ..
    Please try.

  19. #19
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    Here it is.
    Puch zbor2 button and then push zbor button.
    There will be one difference (I think ALADIN with value d on sheet 2).

    That will show you difference in approaches.
    Attached Files Attached Files

  20. #20
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Quote Originally Posted by zbor View Post
    There will be one difference (I think ALADIN with value d on sheet 2).
    Hi zbor

    If you see the sheet, what I loaded it in post # 7 is different from the current sheet.
    ALADIN with value d on sheet 2 does not exist, because this sheet does not exist.
    I not warned you because I thought you made ​​these changes to test the VBA.
    Another small detail: When data is copied to CONTROL sheet, cells change (just column B) their format from general to text. I think the original format of the sheets is copied, but here I need general format. If not, I see the formula in instead of the name, actually. But if it is difficult to change, there's no problem. I think I have to record a macro to change the cell format.
    Anyway I did some tests and it seems to be working ok.
    It's even more than I wanted.
    That's it! A lot of work, but ... You solved it, buddy!
    Many thanks, man ! Many thanks !

  21. #21
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    I think I know when cells change their format.
    So. When I add the your last VBA in my original sheet and run the code for the first time everything is ok. But when I run the code a second time the cells in column B in sheet CONTROL, change their format.
    It has something to do with:
    Please Login or Register  to view this content.
    ??

  22. #22
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Okay.
    I solved this "detail".
    I added this.
    Please Login or Register  to view this content.
    Many thanks again, man !
    Thread solved.

  23. #23
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Add two more columns, modifying an existing VBA code help.

    Glad I helped.
    I know there is no extra ALADIN row on that sheet. I just show you with that example what is difference between those two codes.

    Just to mention: Criteria for searching values is that in B colum on each sheet must be "Heading 1".

  24. #24
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Add two more columns, modifying an existing VBA code help.

    Ok. Many thanks !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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