# nesting a loop to overwrite value if needed



## Mr_Ragweed2 (Dec 19, 2022)

Hello and thanks,
      This should be easy, i just need a jumpstart i think.  I have a code that copies certain data from one worksheet and pastes it to the finalrow on another worksheet.  This is initiated via a button click and everything works great.  My issue is that if you push the button twice you get the data twice and what i would want to do is to overwrite any previously copied data.  The paste location is dynamic (hence the finalrow statement.) What i need to do is look for a value in column c, if it does not exist then proceed with my current code.  If it does exist then i want to overwrite it with what is currently being copied.  My code is below:


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
  '12-19-22 Need to look first to see if vendor already exists. if "yes" then overwrite based on location, if "no" then proceed as normal.
        'Dim Overwrite As Integer ???
        If OSumWS.Cells(I, 3).Value = "Dekalb" Then
           'set Overwrite = the range i found "dekalb" in
 
 'then maybe i Else If the rest of this?
   
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I
```

As you can see i tried to start a little segment there that is half in laymans terms (i have to lay things out logically in regular words and then convert those steps to vba since i don't do this often.)
Any help is appreciated.


----------



## Flashbond (Dec 19, 2022)

Take `Dim ThisFinal As Long` out of sub. Since its default value is zero, you can make the following check:

```
If ThisFinal =0 Then
  ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
End If
```
No one can touch him again after a value is assigned greater than 0.

Also you can add another check-valve:

```
If ThisFinal =0 Then
  ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
ElseIf Not ColumnCValueFound Then
      ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
End If
```


----------



## Mr_Ragweed2 (Dec 19, 2022)

Thank you for helping me!
Ok, i'm following you so far i think.  Default is 0 so run a check to make sure its actually zero. got it.  Where i'm losing you is the next line. If the column c value is found are you still running the same finalrow statement?  How do i find where in column c the value is so i can paste to that location?
Sorry, just trying to understand it better so i don't have to ask as often.


----------



## Flashbond (Dec 19, 2022)

No, if it's Not found then proceed with the lastrow statement also.
In other words, if it is already assigned to a value >0 (which means code was run before) or no value fouund on column C, then don't touch it.

You should loop through column c in order to find the value. Declare a boolean inside Sub:

```
Dim ThisFinal As Long
Sub test()
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long

  lRow = Cells(Rows.Count, 3).End(xlUp).Row
  For i = 1 To lRow
    If Cells(i, 3).Value = "your_value" Then
      ColumnCValueFound = True
      Exit For
    End If
  Next

  If ThisFinal =0 Or Not ColumnCValueFound Then 'Or just If Not ColumnCValueFound Then should be enough in your case
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
End Sub
```


----------



## Mr_Ragweed2 (Dec 20, 2022)

I like this, very instructional/informative. Thank you.  
So then if it does find my value in column c, then that becomes the new final row and it will paste it there to overwrite what exists?  
(Sorry i have not had a chance to test any of this yet. I have been travelling.)


----------



## Flashbond (Dec 20, 2022)

Mr_Ragweed2 said:


> So then if it does find my value in column c, then that becomes the new final row and it will paste it there to overwrite what exists?


if it does find the value in column c, then the last used row becomes the new final row. Else, does nothing.


----------



## Mr_Ragweed2 (Dec 21, 2022)

Awesome.  Thank you. I should get to try this today and will post results either way.


----------



## Mr_Ragweed2 (Dec 21, 2022)

So i tried inserting your code and get an error "For variable already in use".  Here's what i did:

```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet

    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
   
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 
   
    For I = 19 To 31
       
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
   
'your code starts here: 
Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long

  lRow = Cells(Rows.Count, 3).End(xlUp).Row
  For i = 1 To lRow
    If Cells(i, 3).Value = "your_value" Then
      ColumnCValueFound = True
      Exit For
    End If
  Next

  If ThisFinal =0 Or Not ColumnCValueFound Then 'Or just If Not ColumnCValueFound Then should be enough in your case
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
'your code ends here
  
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
           
        End If
    Next I
```
I tried putting an "Exit For" after my For but that did nothing. I also tried commenting out your For and that did nothing. I'm sure i am just overlooking something maybe?


----------



## Flashbond (Dec 21, 2022)

Ok, change this:

```
For i = 1 To lRow
```
to this

```
For j = 1 To lRow
```


----------



## Mr_Ragweed2 (Dec 21, 2022)

If it helps, we are looking for the ColumnCValue on the OSumWS.  We are copying data from DekalbWS.  (and just to be clear, i did change "your_value" to Dekalb since that is the value i am looking for.)


----------



## Mr_Ragweed2 (Dec 19, 2022)

Hello and thanks,
      This should be easy, i just need a jumpstart i think.  I have a code that copies certain data from one worksheet and pastes it to the finalrow on another worksheet.  This is initiated via a button click and everything works great.  My issue is that if you push the button twice you get the data twice and what i would want to do is to overwrite any previously copied data.  The paste location is dynamic (hence the finalrow statement.) What i need to do is look for a value in column c, if it does not exist then proceed with my current code.  If it does exist then i want to overwrite it with what is currently being copied.  My code is below:


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
  '12-19-22 Need to look first to see if vendor already exists. if "yes" then overwrite based on location, if "no" then proceed as normal.
        'Dim Overwrite As Integer ???
        If OSumWS.Cells(I, 3).Value = "Dekalb" Then
           'set Overwrite = the range i found "dekalb" in
 
 'then maybe i Else If the rest of this?
   
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I
```

As you can see i tried to start a little segment there that is half in laymans terms (i have to lay things out logically in regular words and then convert those steps to vba since i don't do this often.)
Any help is appreciated.


----------



## Mr_Ragweed2 (Dec 21, 2022)

Ok. I changed the I's to J's in your code. no errors but it is not doing anything at all.  it just pastes to the next available row. it does not overwrite.  When i step into the code, ColumnCValueFound is always False. I have verified that column C is the correct location we should be looking.  I am just not sure we are looking at the OSumWS where the value exists or if we are looking at the DekalbWS where it does not.


----------



## Flashbond (Dec 21, 2022)

Ley me check the code again.


----------



## Mr_Ragweed2 (Dec 21, 2022)

Another update. I changed the code to this (to make sure we are looking in the right place) and now ColumnCValueFound is TRUE, but it does not overwrite it still pastes to the next available row. I'm guessing it is because we exit the For and end the IF and then proceed to my ThisFinal statement when we paste?


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
            
  'starts here
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long
Dim J As Integer

  lRow = OSumWS.Cells(Rows.Count, 3).End(xlUp).Row
  For J = 1 To lRow
    If OSumWS.Cells(J, 3).Value = "Dekalb" Then
      ColumnCValueFound = True
      Exit For
    End If
  Next

  If Not ColumnCValueFound Then
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
  End If
 'ends here
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
            
        End If
    Next I
```


----------



## Flashbond (Dec 21, 2022)

I am totally lost. Can you please explain step by step from the very beginning. What do you want achieve in general?


----------



## Flashbond (Dec 21, 2022)

Mr_Ragweed2 said:


> Another update. I changed the code to this (to make sure we are looking in the right place) and now ColumnCValueFound is TRUE, but it does not overwrite it still pastes to the next available row. I'm guessing it is because we exit the For and end the IF and then proceed to my ThisFinal statement when we paste?
> 
> 
> ```
> ...


It just exists j For


----------



## Mr_Ragweed2 (Jan 3, 2023)

Flashbond, sorry i have been away from this project but i am back now. Are you still willing to help? if so, i can post a refresher to bring us both back up to speed.
Thanks.


----------



## Flashbond (Jan 3, 2023)

Yes, please tell me what you want to achieve from the beginnig


----------



## Mr_Ragweed2 (Jan 3, 2023)

ok, simple version -  I am trying to overwrite data if it exists based on a value (vendor name) in column c.  I have code that copies data (a dynamic number of lines) from one ws and pastes it to the finalrow of another ws.  
Here is the code i have so far:


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
  'starts here
  Dim ColumnCValueFound As Boolean
  Dim rng As Range
  Dim lRow As Long
Dim J As Integer

  lRow = OSumWS.Cells(Rows.Count, 3).End(xlUp).Row
  For J = 1 To lRow
    If OSumWS.Cells(J, 3).Value = "Dekalb" Then
      ColumnCValueFound = True
      
      OSumWS.Cells(lRow, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            lRow = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
      
      Exit For
    End If
  Next

  ElseIf Not ColumnCValueFound Then
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row
 'ends here
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
            
        End If
    Next I
```

The above code mostly works. Mostly.
Here is a snip of what it looks like before i try and overwrite:





so in theory, if i change the units to 55 it should copy right over these same two lines. (numerics will obviously change as well.) This is not happening entirely. The second line of data is not overwriting and i get this:





The line that starts in column N with $193.23 is supposed to be overwritten with the line below it.  Beyond that, i think we (you) have helped me through most everything else.


----------



## Mr_Ragweed2 (Jan 3, 2023)

Major update here.  The code i posted before is doing exactly what it should be.  The second line of data that pastes is in a different part of code (shown below):


```
Dim copyRange1 As Range
     Dim copyRange2 As Range
     Dim copyRange3 As Range
     Dim copyRange4 As Range
     
     Dim cel As Range
     Dim pasteRange1 As Range
     Dim pasteRange2 As Range
     Dim pasteRange3 As Range
     Dim pasteRange4 As Range
     
     Dim FinalColumn As Long
     
     Set copyRange1 = Sheets("Dekalb Seed Order Form").Range("T39")
     Set copyRange2 = Sheets("Dekalb Seed Order Form").Range("T47")
     Set copyRange3 = Sheets("Dekalb Seed Order Form").Range("T57")
     Set copyRange4 = Sheets("Dekalb Seed Order Form").Range("N61")
     
     Set pasteRange1 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange2 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange3 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange4 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
 
     For Each cel In copyRange1
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -6).Column
        pasteRange1.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Next
     
     For Each cel In copyRange2
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -5).Column
        pasteRange2.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
      For Each cel In copyRange3
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -4).Column
        pasteRange3.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
       For Each cel In copyRange4
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -3).Column
        pasteRange4.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
        Application.CutCopyMode = False
    
    End If
```

I know it's clunky and very novice looking - sorry.  So now i guess the issue is that all of this needs to be conditional based on the overwrite being true.  If the ovewrite code is false then this needs to run as written. 
Said another way: Let's call the "ColumnCValueFound" bit of code the "overwrite code".
If ColumnCValueFound code = false then basically nothing happens and my original code runs as written and the overwrite code is not used. But....
If ColumnCValueFound code = true then it executes the overwrite code we worked on for Part A (1st line of overwrite) and then amends the code in this reply Part B (2nd line of overwrite). -Shifting the paste destination up 1 line.
How do i do that?

Thanks


----------



## Mr_Ragweed2 (Jan 3, 2023)

For clarity, the code i just posted follows immediately after the code we were working on, with these two lines in between for formatting the ws:

```
OSumWS.UsedRange.Columns.AutoFit
    Sheets("Dekalb Seed Order Form").Activate
```


----------



## Mr_Ragweed2 (Dec 19, 2022)

Hello and thanks,
      This should be easy, i just need a jumpstart i think.  I have a code that copies certain data from one worksheet and pastes it to the finalrow on another worksheet.  This is initiated via a button click and everything works great.  My issue is that if you push the button twice you get the data twice and what i would want to do is to overwrite any previously copied data.  The paste location is dynamic (hence the finalrow statement.) What i need to do is look for a value in column c, if it does not exist then proceed with my current code.  If it does exist then i want to overwrite it with what is currently being copied.  My code is below:


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet


    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
  '12-19-22 Need to look first to see if vendor already exists. if "yes" then overwrite based on location, if "no" then proceed as normal.
        'Dim Overwrite As Integer ???
        If OSumWS.Cells(I, 3).Value = "Dekalb" Then
           'set Overwrite = the range i found "dekalb" in
 
 'then maybe i Else If the rest of this?
   
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I
```

As you can see i tried to start a little segment there that is half in laymans terms (i have to lay things out logically in regular words and then convert those steps to vba since i don't do this often.)
Any help is appreciated.


----------



## Flashbond (Jan 4, 2023)

ı still don't understand why do you need to find the last row if you are going to write to same row each time? Just try to find the vendor name and write one line below? Isn't it supposed to be?

Ahh ok, if there is no existing vendor then, it will write to the new line   

Could you please show me some data sample from Dekalb Seed Order Form? I want to see how does your data structure look like. It can be much more easy than you may think.


----------



## Mr_Ragweed2 (Jan 4, 2023)

here are some screenshots.
This is is from the dekalb seed order form as you requested. here is if they are only selling one product:





Here is if they are selling two ( but this could go to as many as 5 or 6 in some cases).




the issue we are working on has to do with the OSumWS:
as you can see below 2 products equals three lines with the third line being some totals that are taken from noncontiguous cells from the dekalb sheet.




and correctly you have guessed there may be more than one vendor as seen below:




so to summarize: number of products is dynamic, number of vendors is dynamic, order of vendors is variable.
Now, to reset the stage for my issue.  A user is inputting a dekalb sales order and select products.  There is a button to push when they are done that takes the appropriate info from the dekalb sheet and puts in on the OSumWS.  They could at that point be done or move onto writing an order for another vendor. (Each vendor has its own sheet and all are summarized together on the same OSumWS.) If at some point they realize they need to edit the order i need the copy and paste to find the data on the OSumWS and overwrite it.
BUT......
As i am typing this i think it may be easier to find any existing data, delete those rows, shift the cells up, and then paste the new data on the next available row.  Sorry for the left turn here in what i am wanting but i believe this could/will be easier.  The code we added only works on a single product order. When i ran the scenario with multiple products it overwrites the first one with the second product because that's what we are now telling it to do.  So sorry for this.  So there is one good thin to know there is always 1 more line than there are products (ex 1 product = 2 rows, 3 products = 4 rows) to account for some totals. Based on this new thought process, here is the original code we are working with: 


```
Dim ThisFinal As Long
    Dim I As Integer
    Dim OSumWS As Worksheet
    Dim DekalbWS As Worksheet

'12-19-22 Need to look first to see if vendor already exists. if "yes" then delete based on location, if "no" then proceed as normal.

    Set OSumWS = Sheets("Order Summary")
    Set DekalbWS = Sheets("Dekalb Seed Order Form")
    
    ThisFinal = OSumWS.Cells(Rows.Count, 17).End(xlUp).Row 'new line
    
    For I = 19 To 31
        
        If DekalbWS.Cells(I, 3).Value <> "" Then
            With Application.Intersect(DekalbWS.Rows(I).EntireRow, DekalbWS.Range("C:U"))
                .UnMerge
                .Copy
            End With
    
            OSumWS.Cells(ThisFinal + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row 'new line
            
        End If
    Next I
    OSumWS.UsedRange.Columns.AutoFit
    Sheets("Dekalb Seed Order Form").Activate
    '----------------------------------------------------------------------------------------
    'below this line needs relocate to next available row after all product rows have been copied - works
               
     Dim copyRange1 As Range
     Dim copyRange2 As Range
     Dim copyRange3 As Range
     Dim copyRange4 As Range
     
     Dim cel As Range
     Dim pasteRange1 As Range
     Dim pasteRange2 As Range
     Dim pasteRange3 As Range
     Dim pasteRange4 As Range
     
     Dim FinalColumn As Long
     
     Set copyRange1 = Sheets("Dekalb Seed Order Form").Range("T39")
     Set copyRange2 = Sheets("Dekalb Seed Order Form").Range("T47")
     Set copyRange3 = Sheets("Dekalb Seed Order Form").Range("T57")
     Set copyRange4 = Sheets("Dekalb Seed Order Form").Range("N61")
     
     Set pasteRange1 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange2 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange3 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange4 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
 
     For Each cel In copyRange1
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -6).Column
        pasteRange1.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Next
     
     For Each cel In copyRange2
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -5).Column
        pasteRange2.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
      For Each cel In copyRange3
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -4).Column
        pasteRange3.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
       For Each cel In copyRange4
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(1, -3).Column
        pasteRange4.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
        Application.CutCopyMode = False
    
    End If
```


Does any of this help or make sense? I realize i may be rambling a bit. Sorry for the long post.


----------



## Flashbond (Jan 4, 2023)

Ok, I see. What is the formula for N, O, P and Q columns? Or are they coming from somewhere else directly?


----------



## Mr_Ragweed2 (Jan 4, 2023)

They are coming from somewhere else and they are noncontiguous.  There were a lot of ws formatting issues i had to deal with and this was the cleanest way to get there. Not ideal, but it's what i was given.


----------



## Mr_Ragweed2 (Jan 4, 2023)

SInce we are searching for and deleting a dynamic number of rows, this is a new topic. Should i start a new thread?


----------



## Flashbond (Jan 4, 2023)

Ok, I prepared a sample workbook for you. I write comments next to each line.
1. Since I don't know where column N, O, P, Q values come from, I input them into vendor sheets. Eveytime last record discounts will be taken into account. I hope you'll figure it out by your self according to you data.
2. At first, the Summary sheet wil be empty. Run the code. You will see non-existing records will be added.
3. Then add a new record to Dekalb sheet. You may add more than one. You will see it will insert records as you want. And last discount amounts will be displayed.

Good luck!









						Sample.xlsm
					

1 file sent via WeTransfer, the simplest way to send your files around the world




					we.tl


----------



## Mr_Ragweed2 (Jan 4, 2023)

Nice. And thank you, but it is not fully doing what i need.  It adds a product no problem.  However, if i edit or delete an existing product there is no change.  I am wanting basically to reset the Order Summary page each time the macro is run, (but only reset it for the particular vendor page i am on.)  Each time i run the "finish" button, i want to delete any existing info from that vendor on the Order Summary sheet and then paste the new info on the next available row.
We only need concern ourselves with dekalb in this example as each vendor sheet has its own button and i can change names accordingly.  I have allegiant on there there to remind myself it is not a simple delete the whole page solution


----------



## Flashbond (Jan 5, 2023)

I see. Is it ok for you to remove all related vendor rows and insert fresh rows to the very end of the summary sheet? Or do you want them with the old location they were? I hope I could explain.

I mean let's say you have Dekalb from 10th to 50th rows. I will remove them. Let's say Summary sheet have empty rows at 400th row. Is it ok to insert new Dekalb information after 400th row? Or do you want to insert them after 10th row again?


----------



## Flashbond (Jan 5, 2023)

Ok, I prepared two examples for you. One, inserts the new data to the old position. The other one appends the new information to the end of the summary sheet. I hope it helps you.








						Append To End.xlsm and 1 more file
					

2 files sent via WeTransfer, the simplest way to send your files around the world




					we.tl


----------



## Mr_Ragweed2 (Jan 6, 2023)

Thank you very much for all of your help.  Both versions worked. I ended up choosing the "Append to end" version in case i run into a scenario later down the road that would become a problem.  After tweaking around a few names and ranges it seems to work perfectly.  At this point i think we are done.  I will let you know if i run into any issues.  
Thanks again.
And to any moderators that may be reading this - This place is excellent. I love using this forum although the one drawback is my bosses now think i have more ability than i actually do. haha.  At least i have an army of helpers when i need it.  Maybe one day i will be able to contribute back.


----------

