CODEWITHSUNDEEP
excelvbacopypaste
Silvio Di Palma
Silvio Di Palma

Reputation: 1

Copy and Paste on Excel with VBA in two differents sheets

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

Upvotes: 0

Views: 477

Answers (1)

Edoardo Berardo
Edoardo Berardo

Reputation: 172

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

Upvotes: 1

Related Questions