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

Sweedler

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
moving row/cell values to another sheet if row is true

1607962743530.png

1607962834648.png


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?
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maras

New Member
Joined
Dec 12, 2019
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
It's hard to check without an attachment. Test it.
VBA Code:
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

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
It's hard to check without an attachment. Test it.
VBA Code:
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

New Member
Joined
Dec 12, 2019
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
Copy the code to the VBA module (Alt + F11 -> Insert -> Module) and run.
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Dec 12, 2019
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
Read about XL2BB - Excel Range to BBCode - link attached.
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Man

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,439
Messages
5,642,126
Members
417,257
Latest member
Sarahbw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top