Copy row from Main data sheet to to a different sheet based on value input

MilkyWay1

New Member
Joined
Aug 26, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, very new to intermediate excel skills. I've been browsing this site over several days but still cannot get the following to work:

-I have a main data sheet named "Main" which has a header from A-P and about 150 rows of data
-I want to copy a row if the input of Column 'O' is set to "Active"
-Entry to column O is based on dropdown from a list in a sperate sheet. One of the options in the list is obviously "Active"
-If Active is selected, I'd like to copy that row to sheet "Active Items"
-However, I only want to copy columns A-H from sheet "Main" to sheet "Active Items"
-Lastly, the sheet "Active Items" has a header identical to "Main" but only columns A-H which I want copied. Other than that, the sheet is empty.

I was playing with the following code which I got from a different thread. I actually successfully got it to work at one point but cannot replicate my one successful row copy. Can anyone help me understand what I'm doing wrong? I am copying the code to sheet "Main"

1630004621599.png
 

Attachments

  • 1630004575275.png
    1630004575275.png
    23.4 KB · Views: 8

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

earthworm

Well-known Member
Joined
May 19, 2009
Messages
622
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You need to use loop with condition If along with cell reference to check each row individually and then do the stuff onwards if the value is true otherwise move to next .
 

MilkyWay1

New Member
Joined
Aug 26, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
You need to use loop with condition If along with cell reference to check each row individually and then do the stuff onwards if the value is true otherwise move to next .
I'm afraid I'm not savvy enough implement this code without an example.

I found this other VBA code (see below) from another thread which worked, but every time it copies to the new sheet, it writes over the existing copied row. So there is only one row in the copied sheet (the latest row copied in) as opposed to a list of all rows copied in....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E:E")) Is Nothing Then
If Evaluate("isref('" & Target.Value & "'!A1)") Then
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
MsgBox "No sheet exists for " & Target.Value
End If
End If
End Sub
 

earthworm

Well-known Member
Joined
May 19, 2009
Messages
622
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I
I'm afraid I'm not savvy enough implement this code without an example.

I found this other VBA code (see below) from another thread which worked, but every time it copies to the new sheet, it writes over the existing copied row. So there is only one row in the copied sheet (the latest row copied in) as opposed to a list of all rows copied in....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E:E")) Is Nothing Then
If Evaluate("isref('" & Target.Value & "'!A1)") Then
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
MsgBox "No sheet exists for " & Target.Value
End If
End If
End Sub
try to make changes in above bold highlighted part.
 

Forum statistics

Threads
1,147,962
Messages
5,744,053
Members
423,843
Latest member
alex2022

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