For Next Loop and copy Help

AcornNut

New Member
Joined
Aug 19, 2014
Messages
35
I am in desperate need of some help, please!

I have an inventory sheet ("LOCATION") and an Order Form sheet ("Supply Usage Form").
I'm trying to get the below code to look at each row from row 9, column "B" to the last row (may vary from time to time) in the inventory sheet in columns 12 (missing items) and 13 (expired items) and if any numbers are found, copy certain cells from that same row over to the order form in certain cells. The bottom half of the code works perfectly every time. The problem comes in when I need to order more items than I have empty rows. The last empty row is row 53. The twist is that there is information below row 53 that needs to stay at the bottom of the form.

What WANT to happen is this... Until row 53 is filled, fill the order form from row 24 to 53 (this is the bottom half of the code below and it works beautifully). When row 53 is filled with data, I need a new row added below row 53 with the same format as row 53, and continue adding the data as before, all the while pushing the information at the bottom of the form down (inserting new rows after the last filled row and before the info at the bottom of the form).

What is ACTUALLY happening is... it adds the extra rows with the formatting just fine, but then it fills in column "B" of the order form with items listed from bottom to top (instead of top to bottom) AND the other columns are offset or simply blank... or it doesn't add any new rows and just fills in the next cells and overwrites the info at the bottom.

Any suggestions or help is GREATLY appreciated.

Sorry if this is confusing, but I'm confused on how to get it to do what I need it to do. I feel like I'm close, but I can't hammer it out. Please help!!!
Thank you in advance! If any more info is needed, please let me know.


Code:
Sub fillorder1()


    Dim finalrow As Long
    Dim i As Integer
    Dim ExpDate As Date
    Dim lastRow As Long
    
    Application.ScreenUpdating = False
    
   lastRow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
   finalrow = Sheets("Location").Range("B9").End(xlDown).Row
   ExpDate = Date + 30
   
  For i = 9 To finalrow
      
       If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") And (lastRow >= 53) Then
          Rows(lastRow + 1).Insert
          Rows(lastRow).Copy
          Rows(lastRow + 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
          
          Sheets("Location").Cells(i, 1).Copy
              Cells(lastRow, "B").PasteSpecial xlPasteValuesAndNumberFormats
          Sheets("Location").Cells(i, 2).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
          Sheets("Location").Cells(i, 12).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
      
       ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow >= 53) Then
          Rows(lastRow + 1).Insert
          Rows(lastRow).Copy
          Rows(lastRow + 1).EntireRow.PasteSpecial Paste:=xlPasteForma           

          Sheets("Location").Cells(i, 1).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
           Sheets("Location").Cells(i, 2).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
           Sheets("Location").Cells(i, 13).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats


        ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow >= 53) Then
          Rows(lastRow + 1).Insert          Rows(lastRow).Copy
          Rows(lastRow + 1).EntireRow.PasteSpecial Paste:=xlPasteForma


          Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats


       ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") And (lastRow <> 53) Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
        
        ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow <> 53) Then
           Sheets("Location").Cells(i, 1).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
           Sheets("Location").Cells(i, 2).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
           Sheets("Location").Cells(i, 13).Copy
               Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats


        ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow <> 53) Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
        End If
    Next i




    Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
        
Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,125
Office Version
365
Platform
Windows
How about
Code:
Sub fillorder1()

   
   Dim finalrow As Long
   Dim i As Integer
   Dim ExpDate As Date
   Dim lastRow As Long, NextRow As Long
   
   Application.ScreenUpdating = False
   
   NextRow = 53
   lastRow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
   finalrow = Sheets("Location").Range("B9").End(xlDown).Row
   ExpDate = Date + 30
   
   For i = 9 To finalrow
      If lastRow > 53 Then
         Rows(NextRow).Insert
         NextRow = NextRow + 1
         lastRow = NextRow
      End If
      
      
      If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 12).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
      
      ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 13).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
      
      
      ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 12).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 13).Copy
         Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
      End If
      lastRow = lastRow + 1
   Next i
   
   
   
   
   Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
   
   Application.ScreenUpdating = True
End Sub
 

AcornNut

New Member
Joined
Aug 19, 2014
Messages
35
Still doing the same thing, except this time it's overwriting the data that's already in row 53. The problem is with the code adding the new row (with or without the formatting). Without it adding the new row, it works perfectly. But it doesn't like adding the new row. It seems simple enough to me, but I can't iron it out. For each row in the inventory columns 12 and/or 13, AND the last row on the order form is = or > row 53...insert a new row after the last row (with all the formatting), then copy and paste as if the row was already there. I've even tried separating the commands (insert the new rows first, then go back and copy and paste), but to no avail. I wish I could add a screen shot so you could see exactly what it's doing.

Thanks for the help, though!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,125
Office Version
365
Platform
Windows
Maybe
Code:
Sub fillorder1()
   Dim finalrow As Long
   Dim i As Integer
   Dim ExpDate As Date
   Dim lastRow As Long, NextRow As Long
   Dim Flg As Boolean
   Application.ScreenUpdating = False
   
   NextRow = 54
   lastRow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
   finalrow = Sheets("Location").Range("B9").End(xlDown).Row
   ExpDate = Date + 30
   Flg = True
   For i = 9 To finalrow
      If Flg And lastRow > 53 Then
         Rows(NextRow).Insert
         lastRow = NextRow
         NextRow = NextRow + 1
      End If
      Flg = False
      If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 12).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
         lastRow = lastRow + 1
         Flg = True
      ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 13).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
         lastRow = lastRow + 1
         Flg = True
      ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
         Sheets("Location").Cells(i, 1).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 2).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 12).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
         Sheets("Location").Cells(i, 13).Copy
         Sheets("Supply Usage Form").Range("B" & lastRow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
         lastRow = lastRow + 1
         Flg = True
      End If
   Next i
   Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
   
   Application.ScreenUpdating = True
End Sub
 

AcornNut

New Member
Joined
Aug 19, 2014
Messages
35
ALMOST!!!! I'm not familiar with the "flg."

I think if I can get the formatting to be the same in the new rows as the rest of the form, it just MIGHT work. Columns B&C are merged; D&E are merged; F&G are merged; J&K are merged; and M&N (N is the last column in the form) are merged. I need to figure out how to copy the formatting of an existing row (say, row 53) and apply it each new row. Hmmmm...so very close!!! Awesome work!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,125
Office Version
365
Platform
Windows
Get rid of the merged cells, they should be avoided like the plague. Otherwise they will simply cause you no end of problems and hassle.
 

AcornNut

New Member
Joined
Aug 19, 2014
Messages
35
Actually, I added 2 lines to the "flg" section:

If Flg And lastRow > 53 Then
Rows(NextRow).Insert
Rows(53).Copy
Rows(NextRow).PasteSpecial xlFormats
lastRow = NextRow
NextRow = NextRow + 1
End If

This works beautifully. The only thing it does now is add a blank row at the bottom. And if the code has to be run again, it adds an additional blank row. Not a "make-or-break" thing, rather it's a minor nuisance.
Thank you so much for your help!!!!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,125
Office Version
365
Platform
Windows
Add this just after the loop
Code:
   Next i
   [COLOR=#ff0000]If Sheets("Supply Usage Form").Range("B" & lastRow).Value = "" Then
      Rows(lastRow).Delete
   End If[/COLOR]
   Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
 

AcornNut

New Member
Joined
Aug 19, 2014
Messages
35
Eureka!!! This code now does EXACTLY what I was needing. Thank you so much for your help!!!!!!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,125
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,795
Messages
5,361,333
Members
400,627
Latest member
Mcomeaux

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top