Solved

Copy Rows from one tab to another by checking a check box?

Posted on 2012-03-31
21
317 Views
Last Modified: 2012-04-01
Not sure this is possible.  I have an excel sheet (Tab 1) and in column A, I created some check boxes for each row.  Is it possible to check those boxes on some rows but not all and then the  ones that are checked get copied to a new sheet (Tab 2) when I click a button?  On the destination Tab 2, can the lines that are checked all appear in order?  No blank rows in between?  I know how to create the button and attach the code to the button.  I just don't know the actual code to do it.  If it's even possible.  Tryng for a slick way to avoid copying and pasting.
0
Comment
Question by:elwayisgod
  • 14
  • 7
21 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37791344
Something like this?
Sub moveToAnotherTab()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksOut As Worksheet
Dim l As Long
Dim r As Range
Dim rng As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Sheet1")
    Set wksOut = wkb.Worksheets("Sheet2")
    
    'clear output sheet & make header row
    wksOut.Range("1:" & wksOut.Rows.Count).Clear
    wks.Range("1:1").Copy
    wksOut.Range("1:1").PasteSpecial
    Application.CutCopyMode = False
    
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    For Each r In rng
        If r.Value = True Then 'move it to next sheet
            r.EntireRow.Copy
            wksOut.Range("A2").Offset(l, 0).PasteSpecial
            Application.CutCopyMode = False
            l = l + 1
        End If
    Next r
            
    'clean up output
    wksOut.Range("A:A").Delete
End Sub

Open in new window


See attached demonstration workbook.

1.  Created a checkbox on the first 15 rows.  each checkbox has a linked cell underneath it.
2.  When the checkbox is checked, then we have a TRUE/FALSE sitting underneath that the macro can key in on.
3.  When you hit the button, the app traverses the range and finds the TRUE's, copying that data to the destination tab, keeping track of an index, so all output rows are sequential in order.

Dave
selectThenMoveCheckboxes-r1.xls
0
 

Author Comment

by:elwayisgod
ID: 37791788
This is really fricking close to what I need.  So few minor, minor things now that I think of it.  On the destination 'tab' can I have a box that 'Clears' everything moved over?  In case they want to start over?  Also on the source 'tab' can there be a 'Check All' and 'Clear All' so if they don't have to cherry pick every one if they dont want to and they can clear?  This looks sweet.  How hard was it to create each check box?  I have upto 300 rows. So wondering if it would be easier instead to have user choose a "Y' or "N" in each and all the "Y" get moved?

This is sweet
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37791792
You could use a DV list with Y/N as an option, rather than using checkboxes.  Want to do that?  Otherwise, I have code that builds checkboxes for me automatically the way they appear in the workbook I posted.

The code already clears the destination sheet when its run again, so is there really a need for the "Clear" on the destination sheet?

Let me know.

Dave
0
 

Author Comment

by:elwayisgod
ID: 37791832
OK.. Let me hear back from client to see what they prefer.  Might be tomorrow or most likely monday.  Appreciate it as I'm just not a VBA pro.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37791835
Let's just go with the checkboxes for now.  You can then wrap up this question and if you want to go another route, just post here.

Ok?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37791847
The attached has all you need.  By selection and macro buttons on the sheet, you can delete the existing checkboxes, you can select a range, then have it create the checkboxes for you (do that in column A).  You have the move macro we've already created and a clear macro button on the destination sheet.

See attached and advise any questions.

Enjoy!

Dave
selectThenMoveCheckboxes-r2.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37791851
PS - if you go with Data Validation lists in column A, where user selects Y or N, then use this code to achieve the same end, as a replacement of the current, without checkboxes:

Sub moveToAnotherTab()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksOut As Worksheet
Dim l As Long
Dim r As Range
Dim rng As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Sheet1")
    Set wksOut = wkb.Worksheets("Sheet2")
    
    'clear output sheet & make header row
    wksOut.Range("1:" & wksOut.Rows.Count).Clear
    wks.Range("1:1").Copy
    wksOut.Range("1:1").PasteSpecial
    Application.CutCopyMode = False
    
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    For Each r In rng
        If r.Value = "Y" Then 'move it to next sheet
            r.EntireRow.Copy
            wksOut.Range("A2").Offset(l, 0).PasteSpecial
            Application.CutCopyMode = False
            l = l + 1
        End If
    Next r
            
    'clean up output
    wksOut.Range("A:A").Delete
End Sub

Open in new window


Cheers,

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37791864
Sorry - forgot, you wanted a CheckAll and ClearAll.  Ok. here is that.  I believe I've done more than your original question.  Feel free to ask about what I've provided and I'm happy to respond.

See attached.

Cheers,

Dave
selectThenMoveCheckboxes-r3.xls
0
 

Author Closing Comment

by:elwayisgod
ID: 37792173
OK.  Ill upload actual spreadsheet too.
0
 

Author Comment

by:elwayisgod
ID: 37792396
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:elwayisgod
ID: 37792397
There's my real Excel example with actual tab names and the correct rows etc....

Thanks
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37792451
Ok.  It is setup as we did with the example.  To create checkboxes, just select any range, then hit the macro button to generate them.  Whatever range is selected will get a checkbox.  The rest of the macro buttons are there as well.

See attached.

Dave
checkbox-real-example-r1.xls
0
 

Author Comment

by:elwayisgod
ID: 37793054
This looks good.  Let me put it in my template.  Also, if they check a box, can it format that row into a different color so it's easy for them to see which rows are highlighted?  If not not a big deal.
0
 

Author Comment

by:elwayisgod
ID: 37793070
I get a compilation error.  Does it Matter that it's Office 2007?  It highligted this line in the VB Editor.

 Set myDict = loadDictExistingOLECB(wks)
0
 

Author Comment

by:elwayisgod
ID: 37793074
Hold on... I only copied on Sub.  I copied all and now it works.  I think they are intertwined :)
0
 

Author Comment

by:elwayisgod
ID: 37793312
I'm getting closer.  Attached is my latest.  On the retrieval tab only the 'Create Check Boxes' button works.  The 'Remove All Check Boxes' , 'Check All' , 'Clear All' and 'Move to Master' buttons throw vba errors.

Don't pay attention to the 'Login' , 'Get Data' and 'Logout' buttons.  Those are my Essbase ones and are working.

On the Modules, I copied your Module names exactly and the entire code from it.  Not sure why this is not working.
Actual-Example.xlsm
0
 

Author Comment

by:elwayisgod
ID: 37793313
I can post new question on how to add highlighting and how to fix this so I can award you more points... I can award as many as you like upto 500 each question.
0
 

Author Comment

by:elwayisgod
ID: 37793447
Base File is 4501KB.  When I add the 3K check boxes.  It grows to 10373KB.  I'm having all kinds of issues with it locking up and crashing.  I have 8GB RAM and Win 7 64Bit too.  Is there any better alternatives to check boxes to mark the lines to copy to Master tab?
0
 

Author Comment

by:elwayisgod
ID: 37793525
OK.  I found a issue with the 'Copy' VB.  It's not putting the rows from the source tab 'RETRIEVE' into the correct columns on the 'Master' tab.  It keeps chomping off Column A thus causing my Headers to shift to left and then data doesn't line up.  I attached a screen shot.
Copy-Problem.docx
0
 

Author Comment

by:elwayisgod
ID: 37793544
Excel just keeps crashing saying Microsoft VBA Out of Memory.... I think I need to get rid of Text Boxes and just have a 'Y' or 'N' and all 'Y' get moved????
0
 

Author Comment

by:elwayisgod
ID: 37793655
I posted a new question. With a new template etc... Other one just got too cumbersome to open and work with.  No idea but with fresh clean template I can't get into the VBA, it asks for password
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Thanks for the advise, but... 4 24
VB Script to add site to Java Exception List 4 18
VBA in SharePoint 3 19
MIN, using ARRAY 4 16
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now