0

I have 2 sheets. I want to transfer a fixed range (a row of data) from sheet #1 (data sheet) to sheet #2 (info sheet) by clicking on a command button. The transferred data should be kept as records in sheet #2. But when I use the code below it overwrites the existing entry instead of adding a new record (row). How do I add a new row to sheet #2 and not overwrite the existing one?

Sheets("DATA").Select

Range("B85:G85").Select

Selection.copy

Sheets("INFO").Select

Range("B5").Select

ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("DATA").Select

Range("B86:G86").Select

Application.CutCopyMode = False

Selection.copy

Sheets("INFO ").Select

Range("H5").Select

ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Roberto Anić Banić
  • 1,411
  • 10
  • 21
fatima
  • 3
  • 2
  • 2
    No need to [shout](https://en.wikipedia.org/wiki/All_caps#Association_with_shouting) – Ocaso Protal Jul 23 '19 at 06:51
  • You might benefit from this. [Avoid Using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Mikku Jul 23 '19 at 06:59

1 Answers1

0

Problem with the Code:

  • No calculation of Last Row
  • Excessive use of Select

Try this:

With Sheets("INFO")

Sheets("DATA").Range("B85:G85").Copy
.Range("B" & .Range("B5").End(xlDown).row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("DATA").Range("B86:G86").Copy
.Range("H" & .Range("H5").End(xlDown).row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End With
Mikku
  • 6,538
  • 3
  • 15
  • 38