?
Solved

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

Posted on 2013-01-17
3
Medium Priority
?
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
TazDevil1674 earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

764 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