Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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