moving row/cell values to another sheet if row is true

[Sweedler]

moving row/cell values to another sheet if row is true

Ok,

So here is tough one .. maybe.

From my first sheet I have a product list with a checkbox next to each one . The checkbox is linked to the cell that it is in and then that cell font is white to make the TRUE/FALSE value invisible. What I want to do is that based on the TRUE/FALSE value of the check box I want the value in column C to be moved into column A in the other sheet. I would prefer for the value to disapear as well if the value is altered to FALSE.

IDEAS?

 

[maras]

It's hard to check without an attachment. Test it.

Sub Moving_True()
    Dim a(), rws
    Dim rng As Range
    
    With Sheets(1).[b5].CurrentRegion
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        a = rng.Value
    End With
    With rng
        rws = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "=" & True & "),row(1:" & .Rows.Count & ")))"), False, 0)
    End With
    If UBound(rws) > -1 Then
        With Sheets(2)
            .[a6].Resize(UBound(rws) + 1) = Application.Transpose(Application.Index(a, rws, 2))
        End With
    End If
    Set rng = Nothing
End Sub

 

[Sweedler]

It's hard to check without an attachment. Test it.

Sub Moving_True()
    Dim a(), rws
    Dim rng As Range
   
    With Sheets(1).[b5].CurrentRegion
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        a = rng.Value
    End With
    With rng
        rws = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "=" & True & "),row(1:" & .Rows.Count & ")))"), False, 0)
    End With
    If UBound(rws) > -1 Then
        With Sheets(2)
            .[a6].Resize(UBound(rws) + 1) = Application.Transpose(Application.Index(a, rws, 2))
        End With
    End If
    Set rng = Nothing
End Sub

Hey

I'm not quite getting it to work. is there a way to attach an EXCEL file here? I'm not seeing it.

 

[maras]

Copy the code to the VBA module (Alt + F11 -> Insert -> Module) and run.

 

[Sweedler]

Copy the code to the VBA module (Alt + F11 -> Insert -> Module) and run.

I did that. It gives me an error message that the index is outside of the intervall. Or something like that. Quick translation from the Swedish Excel I'm on.

Is there any way to send the Excel your way?

 

[maras]

Read about XL2BB - Excel Range to BBCode - link attached.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Sweedler]

Man

My brain is off for the day. Time to make dinner here. Thanks for all the help so far.

 

[My Aswer Is This]

Man

My brain is off for the day. Time to make dinner here. Thanks for all the help so far.

I see this looks like a Table.
How did you put all those check marks in column 1 of the table
What is the name of the table

 

[Sweedler]

I see this looks like a Table.
How did you put all those check marks in column 1 of the table
What is the name of the table

I have removed it from being a table. That was something that I was trying in order to achieve another thing for the document. What I am basically trying to do is being able to select from a large array of values from one sheet and have only the ones I select to appear on the second sheet. Preferably instantaneously. But not sure that can be done.

There are of course some hands on(ie copy/paste and so on) work arounds, but I would prefer to avoid them.

 

[My Aswer Is This]

So you want to select a range and then have that range copied to another sheet an paste in same range is that true.

I would need to know what is the sheet name where you want to paste the range.