VBA Code for Excel needs minor 'tweak'

[KenoshaCanuck]

Good afternoon all:

I have the following code for an Excel macro, that works almost perfectly. First, here's the code (the problem/question will follow):

Sub SalesOrderChangeAutomation()
     
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim SalesOrderNumber As Range
    Dim NoOrders As Integer
    Dim I As Integer
        
    Set wsSource = Worksheets("Query Output")
    Set wsDest = Worksheets("Data input")
    Set wsForm = Worksheets("Order Change")
    
    NoOrders = 1
    I = 2
         
    Set SalesOrderNumber = wsSource.Range("D" & I)
           
        Do
        While SalesOrderNumber <> ""
        
                wsSource.Range("A" & I & ":D" & I).Copy
                wsDest.Range("D3").PasteSpecial Paste:=xlValues, Transpose:=True
                
                wsSource.Range("E" & I & ":G" & I).Copy
                wsDest.Range("D9").PasteSpecial Paste:=xlValues, Transpose:=True
                
                wsSource.Range("H" & I & ":N" & I).Copy
                wsDest.Range("B" & (14 + NoOrders)).PasteSpecial Paste:=xlValues
                
                wsSource.Range("O" & I & ":T" & I).Copy
                wsDest.Range("K" & (14 + NoOrders)).PasteSpecial Paste:=xlValues
                
                wsSource.Range("U" & I).Copy
                wsDest.Range("B39").PasteSpecial Paste:=xlValues
                
                    If SalesOrderNumber.Value <> SalesOrderNumber.Offset(1, 0).Value Then
                        wsForm.PrintOut Copies:=1, Collate:=True
                        wsDest.Range("D3:D11").ClearContents
                        wsDest.Range("B15:I27").ClearContents
                        wsDest.Range("K15:Q27").ClearContents
                        wsDest.Range("B39:B42").ClearContents
                    End If
                                        
    Set SalesOrderNumber = SalesOrderNumber.Offset(1, 0)
    I = I + 1
    NoOrders = NoOrders + 1
 
        Wend
        Loop Until IsEmpty(wsSource.Range("D" & I))
     
End Sub

This code takes a simple columnar spreadsheet of data and cuts/pastes the lines into a "Sales Order Change" form. If the Sales Order has more than one line of data on it that needs to be changed, the macro copies the multiple lines onto one change form.


As part of the copying/pasting of data, the following (from the code above) are copied into my destination sheet.

Copy wsDest.Range("B" & (14 + NoOrders)).PasteSpecial Paste:=xlValues
Copy wsDest.Range("K" & (14 + NoOrders)).PasteSpecial Paste:=xlValues

This works great for the first sales order. However when it comes time for the 2nd (and subsequent sales orders) it won't paste the data into the top row (in this case Row 15), but rather pastes it into the 'next' row of data from where it left off after ending the previous sales order form.

So, if my first form went perfectly, I had data copied into rows 15 and 16 in my destination sheet. But, my 2nd sales order change now has it's first line of data pasted into row 17 of the destination sheet, when in fact I need it to also begin in row 15. No matter how many lines of changes I have cut/pasted for one sales order form, I need the data to start in row 15 on my destination sheet.

Any ideas how to tweak this code to fix this problem? Did I lose anyone in the explanation? Any assistance would be greatly appreciated.

FYI, I am VERY much a rookie in VBA, so please feel free to 'dumb it down' for me.

Thanks!

 

[martinee]

By always pasting to row 15, won't you always overwrite your data? Is that what you wanted? Perhaps you could post some sample data from your source worksheet because my confusion comes from this running on multimple sales orders. If the code loops through multiple sales orders and overwrites the data, then the net result would be to just copy the last data no? Please, some clarification.

 

[KenoshaCanuck]

On my 'source sheet'. If the first sales order has five lines, then those lines are copied into rows 15, 16, 17, 18, 19 on my destination sheet.

The problem happens when the macro goes to the next 'new' sales order. It should (as the first one did) START it's pasting in row 15, but instead it begins it's pasting in my destination sheet into row 20.

I actually understand from the code why it's doing that, but I can't figure out how to re-write that section of code for the macro.

Hopefully that clarifies my situation somewhat.

 

[martinee]

How is the sheet set up though? How is the code to know that you have gone to a new sales order and start over? That is what I and excel need to know to solve this.

 

[KenoshaCanuck]

The following part of the code is what is checking to see if there's a new sales order number:

If SalesOrderNumber.Value <> SalesOrderNumber.Offset(1, 0).Value Then


The declarations associated with this statement appear (or course) near the top of my code.

 

[martinee]

Try reseting the NoOrders after you print:

If SalesOrderNumber.Value <> SalesOrderNumber.Offset(1, 0).Value Then 
                        wsForm.PrintOut Copies:=1, Collate:=True 
                        wsDest.Range("D3:D11").ClearContents 
                        wsDest.Range("B15:I27").ClearContents 
                        wsDest.Range("K15:Q27").ClearContents 
                        wsDest.Range("B39:B42").ClearContents 
     NoOrders = 0 '0 rather than 1 because you will add to it in just a second
                    End If

HTH.

 

[KenoshaCanuck]

That did it!!

Thank you very much!

 

[martinee]

Glad we got it sorted out, even if it did take forever.

 

[KenoshaCanuck]

Yep, I've been working on that entire code for over a week now, and am so glad it's done and works.

My first VBA project under my belt. WHEW. I don't think I can handle too many more of those, too stressful

Thanks again Martinee, I really appreciate your time and assistance.