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

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
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:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
So here is a script you can put in the sheet where you plan to select the range

Select the range you want to copy then do a Right click
Then the range you selected will be copied to the exact same range on a sheet named "Alpha"
Change the sheet name as needed.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab where you plan to select the values
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
VBA Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Modified  12/14/2020  5:30:42 PM  EST
Selection.Copy Sheets("Alpha").Range(Selection.Address)
End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.
The sheet with the entire list of articles is called "artikellista" and the sheet I want to copy any row that is deemed TRUE to is called "beställningsunderlag".
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
So here is a script you can put in the sheet where you plan to select the range

Select the range you want to copy then do a Right click
Then the range you selected will be copied to the exact same range on a sheet named "Alpha"
Change the sheet name as needed.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab where you plan to select the values
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
VBA Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Modified  12/14/2020  5:30:42 PM  EST
Selection.Copy Sheets("Alpha").Range(Selection.Address)
End Sub
I will try this in a while, but I do kind of need for the row/range to be copied over to a specific place on the other sheet. Not the exact same place. Is that possible
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
I will try this in a while, but I do kind of need for the row/range to be copied over to a specific place on the other sheet. Not the exact same place. Is that possible
You will have to give specific details. Copy what and paste where.
And to do this automatically like you wanted you will have to be specific about where the copy range is and where to paste the range
And you will have to say when a value in entered into what range then copy what range.
Like when I enter a value into any cell in column A copy the entire row to sheet "Alpha" same row or something like that.
Specific details like this is important.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You will have to give specific details. Copy what and paste where.
And to do this automatically like you wanted you will have to be specific about where the copy range is and where to paste the range
And you will have to say when a value in entered into what range then copy what range.
Like when I enter a value into any cell in column A copy the entire row to sheet "Alpha" same row or something like that.
Specific details like this are important. And we need sheet names do not say "Other Sheet"
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
In a earlier post you said:
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.
That's why I gave you my last script.
And I asked earlier how did you put those check marks in column A
Or exactly what are they
 

Sweedler

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

ADVERTISEMENT

In a earlier post you said:
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.
That's why I gave you my last script.
And I asked earlier how did you put those check marks in column A
Or exactly what are they
Yes, that is exactly what I still want to do. The checkboxes are entered through "FORM CONTROL", and then I have linked them to the cell that they are in. The cell has its font in white so that the TRUE/FALSE text does not appear. So when a line is checked, then I want the article's name to be transposed into the second sheet without any blank lines. Does that make sense
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
Yes, that is exactly what I still want to do. The checkboxes are entered through "FORM CONTROL", and then I have linked them to the cell that they are in. The cell has its font in white so that the TRUE/FALSE text does not appear. So when a line is checked, then I want the article's name to be transposed into the second sheet without any blank lines. Does that make sense
Are you saying if you select the checkbox in column A you want that row copied into the first empty cell in column A of the other sheet ?
And I asked for a sheet name. I cannot tell the script to copy the row into the other sheet.
Unless the other sheet is name "Other Sheet"
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Are you saying if you select the checkbox in column A you want that row copied into the first empty cell in column A of the other sheet ?
And I asked for a sheet name. I cannot tell the script to copy the row into the other sheet.
Unless the other sheet is name "Other Sheet"
On sheet 1 (artikellista). When the checkbox (located in column B) is clicked (TRUE), then I want the information in column C (of the same row) to be copied to the first blank space on sheet2 (beställningsunderlag). Ideally I would want this to be instantaneous (ie not needing to run a macro to transpose everything).

If that is not possible then I just need the macro to be run when everything on sheet 1 is clicked and for the same macro to clear sheet2 from row 6 and down to make sure there is nothing in the new order that should not be there.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,402
Office Version
  1. 2013
Platform
  1. Windows
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I'm not use to using checkboxes to run a script. And something like this requires Vba code.
And again missing specific details. Like you said:
to be copied to the first blank space on sheet2
First blank Space ??
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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