Solved

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

Posted on 2013-01-17
3
361 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

Highfive Gives IT Their Time Back

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!

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

11 Experts available now in Live!

Get 1:1 Help Now