Copy Row Based On Cell Value To Another Worksheet

cbgar300 · Jun 23rd 2020

Hello trying to do something that is probably pretty simple, but I have no experience at all with Marcos.

In cell F I have either 1's or blanks.

If cell F has a 1 i would like it to copy over the rows A-J to a new work sheet.

If in cell F the 1 is changed back to a blank, it would be removed from the said work sheet.

Doesn't have to be Automatic, I can create a but to run the Macro.

From what I have been reading this is "Dynamic" but i cant find anything that provides a similar code for me to tune to fit my needs.

ANY HELP is much appreciated.

Thank you, thank you, thank you in advance!

**royUK** · Jun 24th 2020

Quote

If in cell F the 1 is changed back to a blank, it would be removed from the said work sheet.

How would Excel know what rows to remove if they have been removed from the original sheet to a new one?

cbgar300 · Jun 24th 2020

Not to remove, just to copy over. Removed was a bad word choice. I guess update is better.

I used this code... but it doesn't update when I delete "1" on the master sheet.

Code

Sub CopyYes()    Dim c As Range    Dim j As Integer    Dim Source As Worksheet    Dim Target As Worksheet
    ' Change worksheet designations as needed    Set Source = ActiveWorkbook.Worksheets("Sheet1")    Set Target = ActiveWorkbook.Worksheets("Sheet2")
    J = 1     ' Start copying to row 1 in target sheet    For Each c In Source.Range("E1:E1000")   ' Do 1000 rows        If c = "yes" Then           Source.Rows(c.Row).Copy Target.Rows(j)           j = j + 1        End If    Next c
End Sub

Code

**royUK** · Jun 25th 2020

rows A-J - this refers to columns not rows.

I think an example workbook would be best.

cbgar300 · Jun 25th 2020

Of course.

It is a workbook of songs for a live band.

Sheet 1 is a Master list containing song name, artist, key signature etc.

One the columns is labeled current set list. If a 1 is marked in this column the row that the "1" is marked in will be copied and placed in Sheet 2 "Current Set"

The code that I listed above worked except that Sheet 2 just does not update if "1" is deleted from the column in Sheet 1 master list. Or to phrase differently, if a song is chosen not to be a part of the the current set list, and the "1" is deleted on the master list. Sheet 2 does not reflect this change

I am attaching a picture as well. Thank you for your help! Im sorry if I am not explaining it well.

**royUK** · Jun 25th 2020

I cannot see the example workbook

cbgar300 · Jun 25th 2020

See attached.

**royUK** · Jun 26th 2020

Try this

**royUK** · Jun 26th 2020

Replace the code with this and the problem should be resolved.

Code

Sub CopyData()
Dim tempSht As Worksheet
Dim rng As Range
Dim x As String


x = Worksheets("sheet2").Range("a1").Value
Worksheets.add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
With Worksheets("data")
''///check whether a filter Is applied
If Not .AutoFilterMode Then .Range("A1").AutoFilter
.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=*" & x & "*" _
, Operator:=xlAnd
''///set a range = to visible cells (excluding the  header)
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1)


With ActiveSheet
rng.Copy .Range("A1")
.Columns("B:F").EntireColumn.Delete
.Range("A1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 19).Copy Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 3).End(xlUp).Offset(1)
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End With
End Sub

Display More

Copy Row Based On Cell Value To Another Worksheet

Participate now!

Share

Tags