Solved

How to do move specific cell data to another worksheet in MS Excel

Posted on 2013-01-17
3
365 Views
Last Modified: 2013-03-12
Dear Experts,

I have a row of data with hidden columns. I want to create VBA code tied to a check box event that will move data in specific cells in the same row the check box was select to another row in the worksheet. I have code to move an entire row then delete the row but the hidden values come over as well. I just need the visible cell values to move over.

Ideas?

This is a student assessment system and once the students finish the assessments I have an exit column with a checkbox object. What I would like is when the user checks the box the student is removed from the row and moved over to the "Exited Students" sheet.

Thanks!
0
Comment
Question by:shogun5
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
TazDevil1674 earned 500 total points
ID: 38790056
An example using Selection.SpecialCells(xlCellTypeVisible).Select

I hid column C

Range("A3:F3").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
0
 

Author Comment

by:shogun5
ID: 38791888
I found some code that works great but copies in the hidden cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row

    If Flag = True Then Exit Sub
    If Not Intersect(Target, Range("L6:L" & LR)) Is Nothing Then
        If Target.Value = "Complete" Then

            LR = Sheets("Exited Students").Range("A" & Rows.Count).End(xlUp).Row + 1
            Target.SpecialCells(xlCellTypeVisible).Copy
            'Target.EntireRow.Copy    'replaced this line with the one above
            Sheets("Exited Students").Range("A" & LR).PasteSpecial
            Flag = True
            Target.EntireRow.Delete
        End If
    End If
    Application.CutCopyMode = False
    Flag = False
End Sub

Open in new window


I would like to use with the statement you provided "Selection.SpecialCells(xlCellTypeVisible).Select
" gettting only the visible cells but when I use the code above I get the following error message:

Run-time error '1004':

Cannot change part of the merged cell.

So I do not think I am integrating the syntax correctly.
0
 

Author Closing Comment

by:shogun5
ID: 38979942
Thanks! Works for me.
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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