Copy certain lines from one Worksheet to another Worksheet

[Clamarc]

HI,
I need to copy a row from one spreadsheet to another using the value of a specific column in the original spreadsheet as a parameter.
See attached image.

Thank you.

 

[DanteAmor]

Try this:

Sub CopyRow()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim n As Long, lr As Long, lr2 As Long
  
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("Incidents")
  Set sh2 = Sheets("Incidents FR")
  n = sh1.ListObjects(1).ListColumns("Situation").Index
  sh1.ListObjects(1).Range.AutoFilter Field:=n, Criteria1:="Out of the Rules2"
  
  lr = sh2.Range("B:B").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
  sh1.AutoFilter.Range.Offset(1).Copy sh2.Range("B" & lr)
  lr2 = sh2.Range("B:B").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  sh2.Range("A" & lr & ":A" & lr2).Value = 1
  
  sh1.ShowAllData
  
  Application.ScreenUpdating = True
End Sub

 

[Clamarc]

Hi,
the code worked, thanks!... but I have three questions:

1 - my IncidentsFR spreadsheet has a table at the end, and I needed the lines to be copied before this table.
2 - how do I make the code run when I open the file?
3 - when I saved the file with the code, it appears with an exclamation mark.

See image

 

[DanteAmor]

You can clarify the following, in your first image on the "Incidents FR" sheet the "Opened" column is in column "C" and in your second image it appears in column "B"

1 - my IncidentsFR spreadsheet has a table at the end, and I needed the lines to be copied before this table.

For future threads, you must enter all the information necessary to provide you with a complete response.
To make the adjustment to the macro, you need to clarify the point above.

2 - how do I make the code run when I open the file?

In the events of ThisWorkbook put the following code:

Private Sub Workbook_Open()
  Call CopyRow
End Sub

NOTE: ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

3 - when I saved the file with the code, it appears with an exclamation mark.

That's because the file is now macro-enabled.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Clamarc]

Hi,

The columns are different because before I used a SUM command and needed to have the value 1 in column A... later I discovered that I could use the COUNT.VALUES command, so I simply changed the code and it worked.

OK, sorry....
...I didn't mention the table at the end of my file, because I thought your code would copy the row from the "Incidents" sheet, in the first empty row it found in the "Incidents FR" sheet, but the code didn't consider row 180 as empty and ended up copying it to the row after the table!

Thaks!!!

 

[DanteAmor]

Try this:

Sub CopyRow()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim rng As Range, f As Range, r As Range
  Dim i As Long, j As Long, n As Long
  Dim newRow As ListRow
  Dim cell As String
  
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("Incidents")
  Set sh2 = Sheets("Incidents FR")
  
  n = sh1.ListObjects(1).ListColumns("Situation").Index
  
  Set r = sh1.Columns(n)
  Set f = r.Find("Out of the Rules2", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      Set newRow = sh2.ListObjects(1).ListRows.Add
      For j = 2 To sh2.Range("B" & Rows.Count).End(3).Row
        If sh2.Range("B" & j).Value = "" Then
          sh1.Rows(f.Row).Copy sh2.Range("A" & j)
          Exit For
        End If
      Next
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If

  Application.ScreenUpdating = True
End Sub

 

[Clamarc]

Try this:....

Hi,

The code copied the line below the TOTAL 2024 line... would it be possible for the line to be copied above the TOTAL 2024 line? See image.

 

[DanteAmor]

Is that total line inside the table?
In your previous image it was in column B, now it appears in column A.
If you don't put things in from the initial post and continually change them, no macro is going to work.

 

[Clamarc]

Is that total line inside the table?
In your previous image it was in column B, now it appears in column A.
If you don't put things in from the initial post and continually change them, no macro is going to work.

yes the total line is inside the Table

As I had informed here...
...The columns are different because before I used a SUM command and needed to have the value 1 in column A, later I discovered that I could use the COUNT.VALUES command.