Results 1 to 5 of 5

Copy/paste from one worksheet to another based on checkbox

Threaded View

  1. #1
    Registered User
    Join Date
    10-03-2016
    Location
    Århus, Denmark
    MS-Off Ver
    2010
    Posts
    6

    Copy/paste from one worksheet to another based on checkbox

    Hi All.

    I've been struggling with an issue for a while now - hope there are some bright heads here, that can help me.

    I would like the following function:

    1. I have 11 checkboxes on worksheet 1. Each checkbox corresponds to a column in a table on worksheet 2 (so 11 columns in total).
    2. If I push a botton on worksheet 1, the program looks for the "state" (checked/unchecked) of the checkboxes;
    3. If the first checkbox is checked, it should look for the value in column L on worksheet 2 - but only from row 26. If it finds a cellvalue of 4 OR 5, it should copy all cells from column B to AC of that line (if more cells have a value 4 or 5 in line L it should copy them all.
    4. The copyed data PLUS formatting should then be pasted on worksheet 1 from cell B23 in the last available row - BUT ONLY if the line/cells isn't already copied to worksheet (I would like a list of unique entries)
    5. If the second checkbox is checked, it goes through the same routine as above, but this time it looks for 4 or 5 i column M on worksheet 2
    6. And so on with the remaining 9 checkboxes on worksheet 1 and columns in workseet 2 (checkbox 3 looks in column N, checkbox 4 in column O, checkbox 5 in column P....checkbox 11 looks in column V)

    Hope it all makes sense!

    Here is what I have so far - if there is a more "smooth"/optimized way to do this, feel free to comment on that:

    Private Sub CommandButton1_Click()
    
    With Sheets("Worksheet 1").Rows("23:10000").ClearContents
    End With
    
    
    'This part is for checkbox 1 (worksheet 1) and column L (worksheet 2)
        If Worksheets("Worksheet 1").Cells(5, 1).Value = True Then
    
                Worksheets("Worksheet 2").Activate
                b = Worksheets("Worksheet 2").Cells(Rows.Count, 2).End(xlUp).Row
            
                For j = 26 To b
                    
                    
                    If Worksheets("Worksheet 2").Cells(j, 12).Value = 4 Or 5 Then
    
    'This copies the entire row. I would like it to just copy from column B to AC
                        Worksheets("Worksheet 2").Rows(j).Copy
                        Worksheets("Worksheet 1").Activate
                        c = Worksheets("Worksheet 1").Cells(Rows.Count, 2).End(xlUp).Row
                        Worksheets("Worksheet 1").Cells(c + 1, 2).Select
                        ActiveSheet.Paste
                            
                    End If
                 Next
        End If
    
    
    'Here the same code as above will go, but for checkbox 2
    
    'Here the same code as above will go, but for checkbox 3
    '...
    'Here the same code as above will go, but for checkbox 11
    
    Worksheets("Worksheet 1").Activate
    Application.CutCopyMode = False
    
    End Sub
    Workbook is attached. Hope some of you can help - if not with all just parts of the code then
    Attached Files Attached Files
    Last edited by Jakob83; 05-31-2018 at 04:12 PM. Reason: misspelling

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Select and Copy/Paste Columns Based on Checkbox
    By krayon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2016, 05:48 PM
  2. [SOLVED] Copy/paste data using checkbox-clear when unchckd & multiple selections paste in next cell
    By Staceymcw in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-02-2015, 12:09 AM
  3. Copy Paste in another worksheet based on cell value vba
    By Penny110 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-05-2015, 09:47 AM
  4. Copy Paste row from one worksheet to another based on DATE
    By niketmohan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2011, 08:29 AM
  5. Copy row based on date and then paste into a new worksheet based on section number
    By calmlaunch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2011, 11:40 PM
  6. Replies: 3
    Last Post: 11-12-2010, 04:31 AM
  7. trying to copy/paste row based on checkbox caption and cell content
    By Zygoid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2007, 06:09 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