[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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

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
elwayisgod
Asked:
elwayisgod
  • 14
  • 7
1 Solution
 
dlmilleCommented:
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
 
elwayisgodAuthor Commented:
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
 
dlmilleCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
elwayisgodAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
elwayisgodAuthor Commented:
OK.  Ill upload actual spreadsheet too.
0
 
elwayisgodAuthor Commented:
0
 
elwayisgodAuthor Commented:
There's my real Excel example with actual tab names and the correct rows etc....

Thanks
0
 
dlmilleCommented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
Hold on... I only copied on Sub.  I copied all and now it works.  I think they are intertwined :)
0
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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
 
elwayisgodAuthor Commented:
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now