0

I've got some problems with a MACRO on excel, I'm going mad, please help. Here's the problem, I have two different sheets on excel, I want to do a macro that should performs 2 actions, first the macro have to copy the selected row in the first sheet at the bottom of the sheet, in the row following the last row with some text. I've already done this one with the following code

Sub Duplica_Riga()

    ' Copia il contenuto di un'intera riga
    Worksheets("LISTA MATERIALE").Activate
    ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
    ActiveCell.Offset(0, 0).Range("A1").Activate
    Selection.Copy
    
    'Incolla il contenuto selzionato nella riga subito sotto alla prima riga occupata partendo dal basso
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    ActiveCell.Offset(1, 0).Range("A1").Activate
    Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste

End Sub

The problem is that I have a table in my sheet and the script paste the data in the row following the last row of the table, I think the script consider the table's rows as rows full of something. How can I solve this one? I need to paste the data in the first free row inside the table, not outside.

For the second part I don't know how to do. I need that in a second sheet the MACRO select the same row selected in the first sheet and copy the reletive data in the first free row on the bottom of the sheet, as should be in the first sheet. Same story as the first sheet, same row selected but with different data. Can someone give me some help? Thanks

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Silvio Di Palma
  • 1
  • 1
  • Maybe easier could be to use the macro just in the first worksheet and double the copy command to the first and second worksheet thereafter? – Geographos Mar 11 '22 at 10:22
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Mar 11 '22 at 10:24
  • First of all thanks for answering man, I already taught about what you said, but the problem is that I don't know how to save the number of the row I select in the first sheet. For example, when I select the row n50 in the first sheet, after the MACRO copied it I don't how to tell the program to select again the row 50 in the second sheet, because if I copied the same code it want a new selection form the user and I want to select the row only in the first instance. – Silvio Di Palma Mar 11 '22 at 10:28

1 Answers1

1

It is better to count the number of non-empty rows instead of using "xlUp", so that you avoid to get the row under the table.

Sub Duplica_Riga()
    
    'r = numero di righe "occupate" da A1 ad A100000
    r = Application.WorksheetFunction.CountA(Worksheets("LISTA MATERIALE").Range("A1:A100000"))
    'copia il contenuto di un'intera riga
    Sheets("LISTA MATERIALE").Select
    'seleziona la riga fino dove ti interessa (io ho messo fino ad F per esempio)
    Range("A1:F1").Select
    Selection.Copy
    'incolla il contenuto selzionato nella riga subito sotto alla prima riga 
    Cells(r+1, "A").Select
    ActiveSheet.Paste

End Sub

For the second case you do the same, but select the other sheet instead

Edoardo Berardo
  • 142
  • 2
  • 9