[Webinar] Streamline your web hosting managementRegister Today

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

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

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
shogun5
Asked:
shogun5
  • 2
1 Solution
 
TazDevil1674Commented:
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
 
shogun5Author Commented:
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
 
shogun5Author Commented:
Thanks! Works for me.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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