View original (French)

Paste macro into the first empty column

Macroblem -  
cs_Le Pivert Posted messages 8437 Status Contributor -
Bonjour,

Hello, I have two sheets excel. One sheet (DATA) contains data, a second sheet (Storage data) to retrieve the data from the first sheet and save it. The data in the first sheet varies depending on the samples, hence the need to retrieve the data when needed.

I want a macro to copy from the first sheet and paste into the second sheet (so far so good). The problem is that I need to paste the data starting from the first empty column (on row E16) of the second page.
I have tried this macro and many others but I can't manage it. Please help me.

Best regards

Sub Macro1()
'
' Macro1 Macro
'
Dim emptycolumn As Long

Sheets("DATA").Select
Range("A53").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Cells(16, Columns.Count).End(xlToRight).Offset(, 1)

End Sub

2 answers

  1.
    cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    see the last used column:

    https://excel-malin.com/tutoriels/vba-tutoriels/vba-trouver-la-derniere-cellule-utilisee/

    and to copy a range:

    https://docs.microsoft.com/fr-fr/office/vba/api/excel.range.copy?irgwc=1&OCID=AID2000142_aff_7809_1246483&tduid=%28ir__6kqse6c239kfqwktkk0sohz3zv2xszudwxhno6pr00%29%287809%29%281246483%29%28%28ccf498c07423a97c10e599b3ab6caa6f%29%28152407%29%281910362%29%28v03040001208613671d3e7d824c2ca7cd619fd387bf61%29%28%29%29%28ccf498c07423a97c10e599b3ab6caa6f%29&irclickid=_6kqse6c239kfqwktkk0sohz3zv2xszudwxhno6pr00

    That's it
    --
    @+ Le Pivert
    1
  2. Macroblem
     
    Thank you for your response.

    I'm still having trouble; I know how to search but I don't know how to write the code that will allow me to paste into the last column.

    Could you please give me an example?

    Thank you in advance!
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      I need to paste the data starting from the first empty column (on line E16)

      there is an inconsistency, it is the cell E16 or on line 16 of the first empty column

      I lean towards the 2nd. Here is the macro:

      you will need to adjust the sheet names and the copy range 

      Option Explicit Sub test() Dim DerniereColonneUtilisee As Integer DerniereColonneUtilisee = Worksheets("Feuil2").Cells(1, Columns.Count).End(xlToLeft).Column + 1 'where X is the row number Worksheets("Feuil1").Range("A1:D20").Copy _ Destination:=Worksheets("Feuil2").Cells(16, DerniereColonneUtilisee) End Sub


      this code was in both links!
      0
      1. Macroblem > cs_Le Pivert Posted messages 8437 Status Contributor
         
        Thank you very much for your reply.

        I copied and pasted the code, the one that is underlined and in italics does not work, I do not know why.

        Knowing that I do not want to paste only a defined range, I use "Range(Selection, Selection.End(xlToRight)).Select"
        and "Range(Selection, Selection.End(xlDown)).Select" to take into account the variations in range. Could you please help me with the whole thing? I'm really struggling. Thank you again for everything.


        19.ccm2.net/qxJdZLfWYPuEYH8Wf699lOxccmw=/b1f16206e5d14184bd7564b620dd3a16/tmp/Capture.PNG |fancy]



        Sub Macro1()
        '
        ' Macro1 Macro
        Dim DerniereColonneUtilisee As Integer
        DerniereColonneUtilisee = Worksheets("Stockage donnees").Cells(1, Columns.Count).End(x1ToLeft).Column + 1 'where X is the row number
        Sheets("DONNEES").Select
        Range("A53").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy Destination:=Worksheets("stockage donnees").Cells(16, DerniereColonneUtilisee)

        End Sub
        0
      2. cs_Le Pivert Posted messages 8437 Status Contributor 730 > Macroblem
          


        DerniereColonneUtilisee = ("Stockage donnees")


        et: 

        Destination:=Worksheets("Stockage Donnees")
        0