# how could stop copy to the bottom repeatedly after lastrow contains data



## abdo meghari (Dec 18, 2022)

Hello
how  can  I  make  this code   copy  data to  the  bottom after  data already existed  without  repeating  copying  to  the  bottom when  run the  macro  more  than  one  time

```
Sub COPY_RANGE()
Dim lr As Long, lr2 As Long
With Sheets("PURCHASE")
    lr2 = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B2:C" & lr2).Copy
End With
With Sheets("Table 1")
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B" & lr + 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

End Sub
```
SOURCE DATA
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND4Table 1

REPORT.xlsmABC1SrDescriptionQuantity21BS 1200R20 G580 TCF45032BS 1200R20 G58010043BS 325/95 R24 G58230054BS 325/95 R24 M8403065BS 1200R24 G58089876Dayton 1200R24 DA53 THA10087BS 1200R24 G5829598BS 650R16 R230250109BS 700R16 R2307001110BS 750R16 R230 TCF1991211BS 225/60 R18 ALENZA11001312BS 195/65 R15 EP1501001413BS 265/65 R17 D8402401514BS 205 R14C R624601615BS 1200R20 G580 TCF1,5331716BS 1200R20 G580 THA1001817BS 1200R20 R187 TCF341918BS 750R16 R230 TCF1,0692019BS 650R16 R2301002120BS 205R16C D8401002221BS 225/95 R16C D619642322BS 255/70 R15C D8401002423BS 195/65 R15 T001 91V TL1002524BS 195/65 R15 EP1501002625BS 265/65 R17 D8401102726BS 285/60 R18 D-SPORT JAP152827BS 265/60R18 D840402928BS 275/65 R18 AL01403029BS 225/60 R17 D-SPORT303130BS 275/55 R20 ALENZA163231BS 1200R20 R187 TCF100PURCHASE



result should be when  run the macro  more than  one  time
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND45756BS 1200R20 G580 TCF4505857BS 1200R20 G5801005958BS 325/95 R24 G5823006059BS 325/95 R24 M840306160BS 1200R24 G5808986261Dayton 1200R24 DA53 THA1006362BS 1200R24 G582956463BS 650R16 R2302506564BS 700R16 R2307006665BS 750R16 R230 TCF1996766BS 225/60 R18 ALENZA11006867BS 195/65 R15 EP1501006968BS 265/65 R17 D8402407069BS 205 R14C R624607170BS 1200R20 G580 TCF15337271BS 1200R20 G580 THA1007372BS 1200R20 R187 TCF347473BS 750R16 R230 TCF10697574BS 650R16 R2301007675BS 205R16C D8401007776BS 225/95 R16C D619647877BS 255/70 R15C D8401007978BS 195/65 R15 T001 91V TL1008079BS 195/65 R15 EP1501008180BS 265/65 R17 D8401108281BS 285/60 R18 D-SPORT JAP158382BS 265/60R18 D840408483BS 275/65 R18 AL01408584BS 225/60 R17 D-SPORT308685BS 275/55 R20 ALENZA168786BS 1200R20 R187 TCF10088Table 1
should clear data from row 57 before brings  data .  should  start after last row  contains  data .


----------



## Flashbond (Dec 19, 2022)

Like this?

```
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet

  Set Purchase = Worksheets("PURCHASE")
  lRow1 = Worksheets("Table 1").Cells(Rows.Count, 1).End(xlUp).Row
  lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("Table 1")
    For i = 2 To lRow2
      .Cells(lRow1 + i-1, 1).Value = Purchase.Cells(i, 1).Value
      .Cells(lRow1 + i-1, 2).Value = Purchase.Cells(i, 2).Value
      .Cells(lRow1 + i-1, 3).Value = Purchase.Cells(i, 3).Value
    Next
  End With
End Sub
```


----------



## HaHoBe (Dec 19, 2022)

Hi abdo meghari,

you mind telling us why data from a Column called _Quantity_ is entered under values in a Column called _SL_? 

Holger


----------



## abdo meghari (Dec 19, 2022)

@Flashbond 

exactly  !  this  is  what  I  look for  it  , but  I  need  fixing  autonumbering  in  column A  fter  bring  data  should  incrementing  based  on  last  row  contains  number   ,  should  not  bring  item number from column A   as  is  existed  from sheet purchase.


----------



## abdo meghari (Dec 19, 2022)

Hi  HaHoBe


> you mind telling us why data from a Column called _Quantity_ is entered under values in a Column called _SL_?


because  the column Quantity  is  relating  with  column SL .


----------



## Flashbond (Dec 19, 2022)

Then should be something like this:

```
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet

  Set Purchase = Worksheets("PURCHASE")
  
  lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("Table 1")
    lRow1 = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    For i = 2 To lRow2
      .Cells(lRow1 - i, 1).Value = .Cells(lRow1 - i - 1, 1).Value + 1
      .Cells(lRow1 - i, 2).Value = Purchase.Cells(i, 2).Value
      .Cells(lRow1 - i, 3).Value = Purchase.Cells(i, 3).Value
    Next
  End With
End Sub
```


----------



## abdo meghari (Dec 19, 2022)

thanks  . the code  doesn't   seem  to  copy  data at all sorry!!


----------



## Flashbond (Dec 19, 2022)

Was it copying before? I haven't changed anything. Try like this. Check worksheet names.

```
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet

  Set Purchase = Worksheets("PURCHASE")
 
  lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("Table 1")
    lRow1 = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    For i = 2 To lRow2
      .Cells(lRow1 + (i-2), 1).Value = .Cells(lRow1 + (i-3), 1).Value + 1
      .Cells(lRow1 + (i-2), 2).Value = Purchase.Cells(i, 2).Value
      .Cells(lRow1 + (i-2), 3).Value = Purchase.Cells(i, 3).Value
    Next
  End With
End Sub
```


----------



## abdo meghari (Dec 19, 2022)

```
Check worksheet names.
```
 I  don't  think  to  sheets names  are  not  matched because  the you  first   code  works  without  any  problem  ,second  code  doesn't  copy  any  thing !


----------



## Flashbond (Dec 19, 2022)

Try the code above. I edited it.








						how could stop copy to the bottom repeatedly after lastrow contains data
					

Hello how  can  I  make  this code   copy  data to  the  bottom after  data already existed  without  repeating  copying  to  the  bottom when  run the  macro  more  than  one  time  Sub COPY_RANGE() Dim lr As Long, lr2 As Long With Sheets("PURCHASE")     lr2 = .Range("B" &...




					www.mrexcel.com


----------



## abdo meghari (Dec 18, 2022)

Hello
how  can  I  make  this code   copy  data to  the  bottom after  data already existed  without  repeating  copying  to  the  bottom when  run the  macro  more  than  one  time

```
Sub COPY_RANGE()
Dim lr As Long, lr2 As Long
With Sheets("PURCHASE")
    lr2 = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B2:C" & lr2).Copy
End With
With Sheets("Table 1")
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B" & lr + 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

End Sub
```
SOURCE DATA
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND4Table 1

REPORT.xlsmABC1SrDescriptionQuantity21BS 1200R20 G580 TCF45032BS 1200R20 G58010043BS 325/95 R24 G58230054BS 325/95 R24 M8403065BS 1200R24 G58089876Dayton 1200R24 DA53 THA10087BS 1200R24 G5829598BS 650R16 R230250109BS 700R16 R2307001110BS 750R16 R230 TCF1991211BS 225/60 R18 ALENZA11001312BS 195/65 R15 EP1501001413BS 265/65 R17 D8402401514BS 205 R14C R624601615BS 1200R20 G580 TCF1,5331716BS 1200R20 G580 THA1001817BS 1200R20 R187 TCF341918BS 750R16 R230 TCF1,0692019BS 650R16 R2301002120BS 205R16C D8401002221BS 225/95 R16C D619642322BS 255/70 R15C D8401002423BS 195/65 R15 T001 91V TL1002524BS 195/65 R15 EP1501002625BS 265/65 R17 D8401102726BS 285/60 R18 D-SPORT JAP152827BS 265/60R18 D840402928BS 275/65 R18 AL01403029BS 225/60 R17 D-SPORT303130BS 275/55 R20 ALENZA163231BS 1200R20 R187 TCF100PURCHASE



result should be when  run the macro  more than  one  time
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND45756BS 1200R20 G580 TCF4505857BS 1200R20 G5801005958BS 325/95 R24 G5823006059BS 325/95 R24 M840306160BS 1200R24 G5808986261Dayton 1200R24 DA53 THA1006362BS 1200R24 G582956463BS 650R16 R2302506564BS 700R16 R2307006665BS 750R16 R230 TCF1996766BS 225/60 R18 ALENZA11006867BS 195/65 R15 EP1501006968BS 265/65 R17 D8402407069BS 205 R14C R624607170BS 1200R20 G580 TCF15337271BS 1200R20 G580 THA1007372BS 1200R20 R187 TCF347473BS 750R16 R230 TCF10697574BS 650R16 R2301007675BS 205R16C D8401007776BS 225/95 R16C D619647877BS 255/70 R15C D8401007978BS 195/65 R15 T001 91V TL1008079BS 195/65 R15 EP1501008180BS 265/65 R17 D8401108281BS 285/60 R18 D-SPORT JAP158382BS 265/60R18 D840408483BS 275/65 R18 AL01408584BS 225/60 R17 D-SPORT308685BS 275/55 R20 ALENZA168786BS 1200R20 R187 TCF10088Table 1
should clear data from row 57 before brings  data .  should  start after last row  contains  data .


----------



## abdo meghari (Dec 19, 2022)

thanks  but  it  copys  to  the  bottom repeatedly .


----------



## Flashbond (Dec 19, 2022)

To the same row? It should inrease i.
Ok I will go with the old code:

```
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet

  Set Purchase = Worksheets("PURCHASE")
  lRow1 = Worksheets("Table 1").Cells(Rows.Count, 1).End(xlUp).Row
  lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("Table 1")
    For i = 2 To lRow2
      .Cells(lRow1 + i-1, 1).Value = .Cells(lRow1 + i-2, 1).Value + 1
      .Cells(lRow1 + i-1, 2).Value = Purchase.Cells(i, 2).Value
      .Cells(lRow1 + i-1, 3).Value = Purchase.Cells(i, 3).Value
    Next
  End With
End Sub
```


----------



## abdo meghari (Dec 19, 2022)

nothing  changes


----------



## Flashbond (Dec 19, 2022)

I am sure you are doing something wrong. This is exactly the same code letter by letter with the first one.
Only changed this line for auto increment: `.Cells(lRow1 + i-1, 1).Value = .Cells(lRow1 + i-2, 1).Value + 1`








						how could stop copy to the bottom repeatedly after lastrow contains data
					

Hello how  can  I  make  this code   copy  data to  the  bottom after  data already existed  without  repeating  copying  to  the  bottom when  run the  macro  more  than  one  time  Sub COPY_RANGE() Dim lr As Long, lr2 As Long With Sheets("PURCHASE")     lr2 = .Range("B" &...




					www.mrexcel.com
				



So the first code is not working anymore? You said it was working?


----------



## abdo meghari (Dec 19, 2022)

ok see  the  file  and  you  can  check yourself 
cop.xlsm


----------



## Flashbond (Dec 19, 2022)

It worked on the first click. See your self below. Maybe your screenupdating was left False.
In a seperate sub first run the code below only at once. Then try again.

```
Sub test()
  Application.ScreenUpdating = True
End Sub
```


----------



## abdo meghari (Dec 19, 2022)

what  happens  if  you  run  the  second,third,....?
then  should  not  copy  to  the  bottom again and  this  is  waht  happens for me.


----------



## Flashbond (Dec 19, 2022)

It keeps adding. Don't you want it?


----------



## HaHoBe (Dec 19, 2022)

Hi abdo meghari,

how about


```
Sub MrE_1224954_161610A()
' https://www.mrexcel.com/board/threads/how-could-stop-copy-to-the-bottom-repeatedly-after-lastrow-contains-data.1224954/
  Dim wsTarg As Worksheet
  Dim wsSource As Worksheet
  Dim lngCounter As Long

  Set wsSource = Worksheets("PURCHASE")
  Set wsTarg = Worksheets("Table 1")
  With wsTarg
    For lngCounter = 2 To wsSource.Cells(Rows.Count, 1).End(xlUp).Row
      If WorksheetFunction.CountIf(.UsedRange.Columns(2), wsSource.Cells(lngCounter, 2).Value) = 0 Then
        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
          .Value = .Offset(-1, 0).Value + 1
          .Offset(0, 1).Value = wsSource.Cells(lngCounter, 2).Value
          .Offset(0, 2).Value = wsSource.Cells(lngCounter, 3).Value
        End With
      End If
    Next lngCounter
  End With
 
  Set wsTarg = Nothing
  Set wsSource = Nothing
End Sub
```

Ciao,
Holger


----------



## HaHoBe (Dec 19, 2022)

Or limit the range for the check to start in the first row after filled rows in Column A


```
Sub MrE_1224954_161610A2()
' https://www.mrexcel.com/board/threads/how-could-stop-copy-to-the-bottom-repeatedly-after-lastrow-contains-data.1224954/
  Dim wsTarg As Worksheet
  Dim wsSource As Worksheet
  Dim lngCounter As Long
  Dim rngSearch As Range

  Set wsSource = Worksheets("PURCHASE")
  Set wsTarg = Worksheets("Table 1")
  With wsTarg
    Set rngSearch = .Range("B" & .Cells(Rows.Count, 1).End(xlUp).Row + 1 & ":B" & .Rows.Count)
    For lngCounter = 2 To wsSource.Cells(Rows.Count, 1).End(xlUp).Row
      If WorksheetFunction.CountIf(rngSearch, wsSource.Cells(lngCounter, 2).Value) = 0 Then
        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
          .Value = .Offset(-1, 0).Value + 1
          .Offset(0, 1).Value = wsSource.Cells(lngCounter, 2).Value
          .Offset(0, 2).Value = wsSource.Cells(lngCounter, 3).Value
        End With
      End If
    Next lngCounter
  End With
  
  Set rngSearch = Nothing
  Set wsTarg = Nothing
  Set wsSource = Nothing
End Sub
```

Ciao,
Holger


----------



## abdo meghari (Dec 18, 2022)

Hello
how  can  I  make  this code   copy  data to  the  bottom after  data already existed  without  repeating  copying  to  the  bottom when  run the  macro  more  than  one  time

```
Sub COPY_RANGE()
Dim lr As Long, lr2 As Long
With Sheets("PURCHASE")
    lr2 = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B2:C" & lr2).Copy
End With
With Sheets("Table 1")
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B" & lr + 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

End Sub
```
SOURCE DATA
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND4Table 1

REPORT.xlsmABC1SrDescriptionQuantity21BS 1200R20 G580 TCF45032BS 1200R20 G58010043BS 325/95 R24 G58230054BS 325/95 R24 M8403065BS 1200R24 G58089876Dayton 1200R24 DA53 THA10087BS 1200R24 G5829598BS 650R16 R230250109BS 700R16 R2307001110BS 750R16 R230 TCF1991211BS 225/60 R18 ALENZA11001312BS 195/65 R15 EP1501001413BS 265/65 R17 D8402401514BS 205 R14C R624601615BS 1200R20 G580 TCF1,5331716BS 1200R20 G580 THA1001817BS 1200R20 R187 TCF341918BS 750R16 R230 TCF1,0692019BS 650R16 R2301002120BS 205R16C D8401002221BS 225/95 R16C D619642322BS 255/70 R15C D8401002423BS 195/65 R15 T001 91V TL1002524BS 195/65 R15 EP1501002625BS 265/65 R17 D8401102726BS 285/60 R18 D-SPORT JAP152827BS 265/60R18 D840402928BS 275/65 R18 AL01403029BS 225/60 R17 D-SPORT303130BS 275/55 R20 ALENZA163231BS 1200R20 R187 TCF100PURCHASE



result should be when  run the macro  more than  one  time
REPORT.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND45756BS 1200R20 G580 TCF4505857BS 1200R20 G5801005958BS 325/95 R24 G5823006059BS 325/95 R24 M840306160BS 1200R24 G5808986261Dayton 1200R24 DA53 THA1006362BS 1200R24 G582956463BS 650R16 R2302506564BS 700R16 R2307006665BS 750R16 R230 TCF1996766BS 225/60 R18 ALENZA11006867BS 195/65 R15 EP1501006968BS 265/65 R17 D8402407069BS 205 R14C R624607170BS 1200R20 G580 TCF15337271BS 1200R20 G580 THA1007372BS 1200R20 R187 TCF347473BS 750R16 R230 TCF10697574BS 650R16 R2301007675BS 205R16C D8401007776BS 225/95 R16C D619647877BS 255/70 R15C D8401007978BS 195/65 R15 T001 91V TL1008079BS 195/65 R15 EP1501008180BS 265/65 R17 D8401108281BS 285/60 R18 D-SPORT JAP158382BS 265/60R18 D840408483BS 275/65 R18 AL01408584BS 225/60 R17 D-SPORT308685BS 275/55 R20 ALENZA168786BS 1200R20 R187 TCF10088Table 1
should clear data from row 57 before brings  data .  should  start after last row  contains  data .


----------



## HaHoBe (Dec 19, 2022)

Hi abdo meghari,

for `MrE_1224954_161610A2` you should change codeline


```
Set rngSearch = .Range("B" & .Cells(Rows.Count, 1).End(xlUp).Row + 1 & ":B" & .Rows.Count)
```

which would take the last filled ro from Column A and allow multiple copying due to continuous numbers to


```
Set rngSearch = .Range("B" & .Cells(Rows.Count, 4).End(xlUp).Row + 1 & ":B" & .Rows.Count)
```

to look in Column D for the last filled row as that Column will not be touched by the copying.

Ciao,
Holger


----------



## Flashbond (Dec 19, 2022)

It will run only once:

```
Dim alreadyRun As Boolean
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet
  If alreadyRun Is Nothing Then
    alreadyRun = False
  End If
  If Not alreadyRun Then
    Set Purchase = Worksheets("PURCHASE")
    lRow1 = Worksheets("Table 1").Cells(Rows.Count, 1).End(xlUp).Row
    lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
    With Worksheets("Table 1")
      For i = 2 To lRow2
        .Cells(lRow1 + i-1, 1).Value = .Cells(lRow1 + i-2, 1).Value + 1
        .Cells(lRow1 + i-1, 2).Value = Purchase.Cells(i, 2).Value
        .Cells(lRow1 + i-1, 3).Value = Purchase.Cells(i, 3).Value
      Next
    End With
    alreadyRun = True
  End If
End Sub
```


----------



## Flashbond (Dec 19, 2022)

The previous code may throw an error. Try this one. It will only run once.

```
Dim alreadyRun As Boolean
Sub copyItems()
  Dim lRow1 As Long, lRow2 As Long
  Dim Purchase As Worksheet
 
  If Not alreadyRun Then
    Set Purchase = Worksheets("PURCHASE")
    lRow1 = Worksheets("Table 1").Cells(Rows.Count, 1).End(xlUp).Row
    lRow2 = Purchase.Cells(Rows.Count, 1).End(xlUp).Row
    With Worksheets("Table 1")
      For i = 2 To lRow2
        .Cells(lRow1 + i-1, 1).Value = .Cells(lRow1 + i-2, 1).Value + 1
        .Cells(lRow1 + i-1, 2).Value = Purchase.Cells(i, 2).Value
        .Cells(lRow1 + i-1, 3).Value = Purchase.Cells(i, 3).Value
      Next
    End With
    alreadyRun = True
  End If
End Sub
```


----------



## abdo meghari (Dec 20, 2022)

> It keeps adding. Don't you want it?


I  don't  want  it .
I'm surprised   after  modfying   the  code  doesn't  work  as  in  post#2
just  test  post#2  and  last  your  code  you  will see difference (should  run the  macro more  one  time  to  see the  difference)


----------



## abdo meghari (Dec 20, 2022)

@HaHoBe 
thanks  almost this  is  what  I  want , but  the  code  ignores  duplicates  items , shouldn't  do  that  , how  can   I  fix  this  problem,please?


----------



## abdo meghari (Dec 20, 2022)

@Flashbond  sorry  about  code  your  post#2 !    I thought   the  code  doesn't  repeat  copying  to  the  bottom  but  in  reality  does  as   your  others versions. 
as  to  last  code  I  don't want to   disable  the  macro   after  run  the  code  just  clear  data after  last  row  contains  data  because the  sheet  PURCHASE    will  change  data every  time   then    the  macro  should  work  every  time running  without  repeat  copying  the  same  data to  the  bottom .


----------



## HaHoBe (Dec 20, 2022)

Hi abdo meghari,

maybe checking for the last filled rows in Column A and D and if they are equal copy:


```
Sub MrE_1224954_161610A_mod3()
' https://www.mrexcel.com/board/threads/how-could-stop-copy-to-the-bottom-repeatedly-after-lastrow-contains-data.1224954/
  Dim wsTarg As Worksheet
  Dim wsSource As Worksheet

  Set wsSource = Worksheets("PURCHASE")
  Set wsTarg = Worksheets("Table 1")
  With wsTarg
    If .Cells(.Rows.Count, 1).End(xlUp).Row = .Cells(.Rows.Count, 4).End(xlUp).Row Then
      wsSource.Range("B2", wsSource.Cells(wsSource.Rows.Count, 3).End(xlUp)).Copy
      .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial
      .Range("A55:A56").AutoFill Destination:=.Range("A55:A" & .Cells(.Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
    End If
  End With
  
  Set wsTarg = Nothing
  Set wsSource = Nothing

End Sub
```

Ciao,
Holger


----------



## abdo meghari (Dec 20, 2022)

som items  are  missed and  doesn't  increment  correctly 
this  is  what  I got
cop.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND4571BS 1200R20 G580 TCF450582BS 1200R20 G580100593BS 325/95 R24 G582300604BS 325/95 R24 M84030615BS 1200R24 G580898626Dayton 1200R24 DA53 THA100637BS 1200R24 G58295648BS 650R16 R230250659BS 700R16 R2307006610BS 750R16 R230 TCF1996711BS 225/60 R18 ALENZA1100Table 1

should  copy  whether there  are  duplicates  items  or not .


----------



## HaHoBe (Dec 20, 2022)

Hi abdo meghari,

you mind telling us what code you have used (did you change the macro for the button?) as the result from my latest code looks like this:

MrE_1224954_161610A.xlsmABCD1ITEMBRANDSLBU21BS 1200 R20 18PR G580 THI3232BS 1200 R20 18PR G58059643BS 1200R20-18PR R187 JAP1954BS 1200R24 G58049265BS 1200R24 G58219176BS 13 R22.5 R187 JAP8287BS 175/65R14 EP150 THI498BS 185/65R15 B250 JAP4109BS 185/70R13 EP150 IND41110BS 195/55R16 EP300 THI21211BS 195R14C 613 JAP441312BS 195R14C R623 THI201413BS 195R14C R624 HD 106P104P 8 TURK121514BS 195R15C 613V JAP81615BS 195R15C R623 THI41716BS 205/60R16 T005 THI41817BS 205/65R15 EP150 IND41918BS 205/70R15C R623 THI182019BS 205/70R15C R660 TURK4182120BS 205R16C D840 THI282221BS 215/55R17 GR90 IND22322BS 215/60R16 ER30 JAP42423BS 215/65R16 D688 JAP42524BS 215/70R15C R624 HD109S107S 8 TURK162625BS 215/70R15C R660 TURK242726BS 225/45R18 T005 JAP42827BS 225/50R17 EP300 THI42928BS 225/55 R16 EP300 IND83029BS 225/55R16 T001 JAP23130BS 225/60R18 ALENZA1 JAP43231BS 225/70R15C R623 JAP283332BS 225/70R15C R624 112R110R TURK1163433BS 225/70R15C R660 112S110S 8 TURK243534BS 225/95R16C D619 JAP63635BS 235/55R17 ER300 JAP43736BS 245/40R17 T001 JAP43837BS 245/40R19 T005 JAP23938BS 245/70R16 D684 THI24039BS 255/70R15C D840 THI2764140BS 265/70R16 D840 THI14241BS 275/65R18 AL01 JAP44342BS 285/50R20 D-SPORT JAP204443BS 315/80R22.5 R152 JAP124544BS 315/80R22.5 R184 JAP224645BS 315/80R22.5 R184 THI124746BS 315/80R22.5-18PR G580 JAP24847BS 385/65 R22.5 R164 JAP24948BS 385/65 R22.5 R164 THI65049BS 650R16 R230 JAP55150BS 700R16 12PR R230 JAP65251BS 750R16 R230 TCF JAP2245352Dayton 1200r24 DA53 THI855453Dayton 385/65r22.5 DT40 THI65554FIRESTONE 215/60R16 ROADHAWK ITALY45655FS 275/65R18 116H IND45756BS 1200R20 G580 TCF4505857BS 1200R20 G5801005958BS 325/95 R24 G5823006059BS 325/95 R24 M840306160BS 1200R24 G5808986261Dayton 1200R24 DA53 THA1006362BS 1200R24 G582956463BS 650R16 R2302506564BS 700R16 R2307006665BS 750R16 R230 TCF1996766BS 225/60 R18 ALENZA11006867BS 195/65 R15 EP1501006968BS 265/65 R17 D8402407069BS 205 R14C R624607170BS 1200R20 G580 TCF15337271BS 1200R20 G580 THA1007372BS 1200R20 R187 TCF347473BS 750R16 R230 TCF10697574BS 650R16 R2301007675BS 205R16C D8401007776BS 225/95 R16C D619647877BS 255/70 R15C D8401007978BS 195/65 R15 T001 91V TL1008079BS 195/65 R15 EP1501008180BS 265/65 R17 D8401108281BS 285/60 R18 D-SPORT JAP158382BS 265/60R18 D840408483BS 275/65 R18 AL01408584BS 225/60 R17 D-SPORT308685BS 275/55 R20 ALENZA168786BS 1200R20 R187 TCF100Table 1

Holger


----------



## abdo meghari (Dec 20, 2022)

@HaHoBe
sorry  I  no  know  what  exactly  happens !!🙏
your code  works  perfectly
just  question  I have  to  specify  this  end of  range  for  this  line   if  the  last  row  change in location?
is there anyway to make that without interfere from me ?

```
Range("A55:A56").AutoFill Destination:=.Range("A55:A" & .Cells(.Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
```


----------

