View original (French)

Paste on the last line

Solved
Gayoutte85 Posted messages 2 Registration date   Status Member Last intervention   -  
gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   -
Hello everyone, and thank you for your availability and your responses that have helped me progress a lot.

I tried to assemble several macros (that were working) and to simplify them regarding:
a condition on a range of cells,
that triggers a copy from one sheet,
and pastes to the last row of another sheet,
all in a single module in my sheet 2... (I may have aimed too high for my level)
Problem: I have a 1004 error at the end of my command when I try to select the last row, in this case the location of the "paste".

I've searched a lot, but I can't find a solution to the problem.
It might just be a syntax error?
Thank you in advance for the help you will provide me.

Below is my code (module attached to sheet2):

Private Sub Worksheet_Change(ByVal Target As Range)
'triggers when a value is entered in a cell

Dim r As Range ' row
'if we enter a value in a cell not belonging to the range H3:H500
'we stop the macro
If Intersect(Target, [H3:H500]) Is Nothing Then Exit Sub
'if the value of the modified cell is not empty
For Each r In Target.EntireRow.Rows 'There may be multiple modified rows...
If r.Cells(1, 8) <> "" Then 'If column H is not equal to quotes
'then we copy the entire row of this cell
Rows(Target.Row).Copy
' we activate sheet1 and select the last row of sheet1
Sheets("Feuil1").Activate
Cells(65535, 1).End(xlUp)(2).Select 'it's this line that is malfunctioning

ActiveCell.Paste 'we paste at the selected location

End If
Next r

'
End Sub

I hope the comments will help you easily understand the steps I tried to combine in this module.
Thank you in advance!

3 answers

  1. Maurice
     
    Hello
    Here is the modification 
     Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues Application.CutCopyMode = False

    See you
    Maurice
    0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    In order for your macro to do what you want, there are several modifications to make: 
    Private Sub Worksheet_Change(ByVal Target As Range) 'Triggers when a value is entered in a cell Dim r As Range ' row 'if a value is entered in a cell that is not in the range H3:H500 'stop the macro If Intersect(Target, [H3:H500]) Is Nothing Then Exit Sub 'if the value of the modified cell is not empty For Each r In Target.Rows 'There can be multiple rows modified.. If r <> "" Then 'If column H is not empty 'then copy the entire row of this cell r.EntireRow.Copy ' activate sheet1 and select the last row of sheet1 'paste at the selected location With Sheets("Feuil1") .Paste .Rows(.Cells(Rows.Count, 1).End(xlUp).Row + 1) End With End If Next r ' End Sub

    --
    Always zen
    Perfection is attained, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
  3. Gayoutte85 Posted messages 2 Registration date   Status Member Last intervention  
     
    Thank you Maurice and gbinforme!

    Not only do you respond quickly, but most importantly, your solutions work!

    Maurice's solution fixes THE line that was crashing and perfectly addresses my issue.

    The macro from gbinforme is just as functional.
    The added value is the corrections made to the other lines.
    Thank you very much, as it shows me how to simplify and clean up code while keeping it understandable.

    In testing, the result does not differ between the two solutions.

    Thank you very much, you are helping me improve!

    If other novices like me use these commands...
    this code tests for a "non-empty" in column A of sheet 1 to find THE last line to paste.
    In our case, I had a source (sheet 2) with column A empty.

    Also, even if all of this is triggered automatically and is a module,
    repeating changes in the source (column H of sheet 2) will only overwrite the line created in the destination sheet.
    => this will always result in containing the source, thus with cell A empty.

    This allows - subsequently - the creation of a message or dialog box, to force the user to fill in the newly created cell A...

    In any case, THANK YOU again!
    I will post some solutions in return quite quickly (at this pace of progress thanks to you.)

    Cheers!
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello,

      In the test, the result does not differ between the two solutions.
      You haven't quite seen everything:
      - for THE line, Maurice's solution copies the values while with what I put you copy the formats as well: it's up to you to choose what you want.
      - the other modifications I made in your code mean that "if multiple lines are modified" you copy the modified lines while your code copies the first line multiple times.

      These are nuances to fully grasp when starting out to create code that precisely corresponds to the desired result.
      0