+ Reply to Thread
Results 1 to 9 of 9

Autopopulate macro, needs to searrch for appropriate section to autopopulate

  1. #1
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Autopopulate macro, needs to searrch for appropriate section to autopopulate

    I have a previous macro that autopopulates based off of a data validation list selection. What I would like to happen is when I make a selection in the Training Plan sheet, cell N172, the macro would clear the contents of Training Plan A171:B182. Then go to the templates page, search in the first row for the same text that was selected in Training Plan N172. When it finds it, select the 12 cells below it and the 12 cells to the right of those cells, and copy and pastes it to Training Plan A171:B182.

    For example, if "Another Name" was selected in Training Plan N172, the macro would find "Another Name" in Templates E1 and then copy and paste Templates E2:F13 into Training Plan A171:B182.

    I have used the term copy and paste, however it would be great if it didn't actually utilize the clipboard as occasionally I get errors with macros that do use the clipboard.

    I can not use set ranges to copy and paste from on the Templates sheet. Right now there are 3, but there will be more added as time goes on and I don't want to have to rework the macro every time one is added.

    I will have to expand this out to work based off of Training Plan N172:N183. I am not asking someone to write the whole thing, but I may have questions on how to expand it out.

    Thanks in advance. I hope the tone of my post doesn't sound demanding.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    Maybe?

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    this oughta get you started. as for expanding i'd suggest putting a label next to each of your range N172:N183 and then using a similar find structure to look for that label in column A.

    let me know if you need any help with that.

    Please Login or Register  to view this content.
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito

  4. #4
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe?
    This wouldn't work for me since the macro would need reworked every time I created a new template.



    Quote Originally Posted by simarui View Post
    let me know if you need any help with that.
    It works for me but I am completely unable to expand it. Right now any cell change in Training Plan N172:N183 is manipulating Training Plan A171:B182. Here is the list of cell and the corresponding range it should manipulate.
    N172 manipulates A171:B182
    N173 manipulates A185:B196
    N174 manipulates A201:B212
    N175 manipulates A215:B226
    N176 manipulates A231:B242
    N177 manipulates A245:B256
    N178 manipulates A261:B272
    N179 manipulates A275:B286
    N180 manipulates A291:B302
    N181 manipulates A305:B316
    N182 manipulates A321:B332
    N183 manipulates A335:B346

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    Hi Butcher1

    Will there be more?
    Here is the list of cell and the corresponding range it should manipulate.
    N172 manipulates A171:B182
    N173 manipulates A185:B196
    N174 manipulates A201:B212
    N175 manipulates A215:B226
    N176 manipulates A231:B242
    N177 manipulates A245:B256
    N178 manipulates A261:B272
    N179 manipulates A275:B286
    N180 manipulates A291:B302
    N181 manipulates A305:B316
    N182 manipulates A321:B332
    N183 manipulates A335:B346
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    Hi Butcher1

    See if this works for you...place the Code in Training Plan Module
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    Quote Originally Posted by jaslake View Post
    Hi Butcher1

    See if this works for you...place the Code in Training Plan Module
    Thank you, it worked perfectly!

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    You're welcome...glad I could help. Thanks for the Rep.

  9. #9
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Autopopulate macro, needs to searrch for appropriate section to autopopulate

    sounds like this is solved, but i finally got this to work and wanted to share:

    Please Login or Register  to view this content.
    i think it's a little bit easier to expand in the future - just need to make sure you write Week ## in column O and in column A, and change your intersect range to include the new row (i.e. if not intersect(target, TPlan.Range(N172:N184) is nothing then...)

+ 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] Macro to autopopulate a section of the sheet, using a named range
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-16-2015, 03:15 PM
  2. Macro to autopopulate a form in open webpage
    By beepbeep27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2012, 12:47 PM
  3. Macro to autopopulate references
    By ab11viva in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2009, 12:08 PM
  4. autopopulate using a macro question
    By sous2817 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2007, 04:29 AM
  5. Autopopulate with zero
    By roy.okinawa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2005, 09:20 PM

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