+ Reply to Thread
Results 1 to 19 of 19

Problem with generating new list

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Problem with generating new list

    Hi there. Im trying to keep track of completed tasks. I made a workbook where all tasks are listed in sheet1 (Ark1) and I want rows with completed tasks (identified by marked checkboxes) sequently moved to sheet2 (ark2).

    I used this recepie to get med started: https://www.extendoffice.com/documen...ell-value.html. I must admit that I dont understand all lines, so it's somewhat difficult to modify it to suit my needs. Here is my code:
    Please Login or Register  to view this content.

    Here is a screenshot of my first Sheet1 (Ark1) so you can get an idea what I'm trying to do.
    Sheet1 (ark1)
    sheet1.PNG
    Sheet2 (ark2) is just a copy of the first 3 rows


    However. Using this code gets me into all kind of strange situations, and I cant figure out how to solve it:

    First: I imbedded checkboxes into cells. While the code copies the checkbox from the row correctly, It also copy in checkboxes in the row above the row of interest.
    Second: If I have several checkboxes marked at the same time, the code only moves some rows. If I however just run the code over and over, all the rows of interes are included
    Third When trying to delete rows in sheet2 it does not let med delete the checkboxes in sheet2. I allso figured out that excel had layered several hundered checkboxses into one cell



    BTW I run this code to link my checxboxes:
    Please Login or Register  to view this content.
    Not beeing a world champion coder, I would really appreciate some help figuring out how to solve these problems

    Thanks
    Last edited by Peltz; 04-24-2017 at 05:33 AM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Hi Peltz

    Don't know what your workbooks look like, however, this will get you started. have a look at sample workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 04-24-2017 at 07:10 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Great

    However I need to start the checklist at row 5 and delete rows (including the checkbox) after they are checked. Tried entering a simple Rows(nRow).EntireRow.Delete in the code, which just screwed up the code. I attached a picture of my workbook in the original post.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Give this a try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Hi thanks. Unfortunatly it doesn't work. The code only deletes the checkboxes and doesn't copy any of the data.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Hi Peltz

    Works perfectly with me...See attached workbook.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Yes, Your last attachement worked?! Its exactly what I need. Thank you so much!

    I used your first attachement and changed the code, but still does not work. Must have done something wrong. Would love to send you the spreadsheet for you to test, but reading this https://www.mrexcel.com/forum/about-...cel-forum.html I did'nt think I could post spread sheet attachments, so quite surprised to see you managing to attach. How did you do that.

    https://www.mrexcel.com/forum/about-...cel-forum.html

    Of course I could leave It with that, telling you how gratefull I'am with your help. However, not knowing what went wrong with the first Workbook. Is really naging me, and would love If you could run the code In excacly the same worksheet you sendt me.

    Again thank you so much for your efforts and time.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Code works fine in the first workbook as well...I just changed the row start to 5 in the new workbook...remember the first workbooks row started in Row 1

  9. #9
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Yes, I can see it starts at row 5, and the last workbook works as a charm as a template. This is really strange. I just copy-pasted your code at post #4, and just can't figure out whats wrong with the first workbook after doing this... ok thats life, need to go on. How you managed to attach the workbook? I would be gratefull you would run the code in my workbook to see If you get the same behaviour.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Originally Posted by Pete_UK
    To post a workbook, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

  11. #11
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Thanks again for your time. A bit back and forth here.

    1: Could you run the code in the attachment to see if it runs as it should?

    2: Your last code pastes the rows starting from row 3. I cant figure out why It does'nt start at row 5... or 1 for that matter. Input?
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Have a look...this workbooks rows start at 1...the code you were using is for your requested row 5
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Yes, I understand that, and think we have a misunderstanding, and that was my fault. The last code I posted starts picking rows at row 5 but pastes the rows starting from row 3. In other words, the source row is ok, but the pasted row is not.... I'll just attach what im actually working on so you can see what I mean. I feel really bad taking som much of your time
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Sorry for the spam. I'm starting to tear my hair out! I tried to use your last documents as my template and changing the row to 5 in the code (see attachment): Some observations:
    - When fireing the code from VBA it moves the rows to row 1 and 2. However If I check more rows and fire the code again it overwrites the first entries in sheet2. (in adition it starts populating fram row 1, not row 5.
    - When fireing the code uing the commandbutton all kind of strange things happen. It does not move anything to sheet2, it just moves some rows and checkboxes in sheet1... YES I see that the checkbox is suppose to fire the same macro.

    I dont understand this at all...
    Attached Files Attached Files

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    All works according to me....
    What row do you want it to be copied to on Sheets("Ark2")..according to your file it must be row5 cause row 4 is blank and shaded black.
    So the this line
    Please Login or Register  to view this content.
    must change to
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 04-25-2017 at 08:50 AM. Reason: Added sample workbook

  16. #16
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Thank you again for your time. If I run
    Please Login or Register  to view this content.
    in your attached worksheet. In other words: If I just run the code in the document, the code copies rows 5 and 7 from the first sheet into rows 3-4 in the second sheet. If I do another entry into into sheet one, at row 5 and 6 (and mark the two checkboxes) it copies to rows 4-5 in sheet 2. Next enty and copy from rows 5 and 6 copies to rows 5-6 in sheet 2... and som on, allways owervriting the last rows from previous operation in sheet two. Same thing happens if I try to copy, say 5 rows from sheet 1 to sheet two, overwriting the last rows in sheet 2.

    If I change the line to
    Please Login or Register  to view this content.
    in your attacehed worksheet, thw code copies rows 5 and 7 in the first sheet to rows 4-5. If I do another entry into into sheet one, at rows 5 and 6 (and mark the two checkboxes) it copies to rows 6-7 in the second sheet and so on, which is what I wanted. However the list in sheet 2 is suppose to start at row 5, not row 4

    By the way, if I use this
    Please Login or Register  to view this content.
    , rows 5 and 7 from the first sheet are copied into row 5-6 in the second sheet, just as they are supposed to do. However, If i repeat the procedure rows 5 and 7 are copied into 8-9 in the second sheet, leaving a space at row 7 and so on.

    I know this is alot of words, but you know the workbook by now and I think you'll understand

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Hi Peltz

    See Post # 15....
    Ark2 has a hidden row 4 which is shaded black and blank.....Put any character into that A4 and use
    this
    Please Login or Register  to view this content.
    Your rows will now be pasted to row 5 and then row 6 and then row 7 etc

  18. #18
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Problem with generating new list

    Bingo! I didnt understand that I had to put a character into row 4! It works like a charm. The reason for the hidden and shaded row was that I coul'dnt figure out how to exclude the borders when using your or my older code to copy the rows. So my silly walkaround was to shade and collapse the colunm in order to show a black line.

    But again. Thanks a bunch. It's people like you who make this problably one of internets greatest forums!

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Problem with generating new list

    Thanks....anytime
    Please mark thread as solved and add to rep points....Click star to left of pst

+ 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. VLOOKUP Problem!! Not generating names
    By taj1504 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2016, 09:21 AM
  2. [SOLVED] Generating a list with random units per sale from a list with the totals
    By Juliana33 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-05-2015, 03:45 AM
  3. [SOLVED] VBA macro problem regarding folder generating
    By istvanvincze90 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-20-2015, 09:39 AM
  4. Formula generating problem
    By macimacileona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-01-2015, 06:22 PM
  5. Problem while generating vloop in excel using vb 6.0
    By anavarathan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2014, 03:00 PM
  6. macro for automatically generating a list from a list
    By SIFT-MS88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2010, 01:44 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