For Next Loop and copy Help

A

AcornNut

Board Regular
Joined
Aug 19, 2014
Messages
51
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:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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
 
Upvote 0
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!!!!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!!!!!!!!
 
Upvote 0
Add this just after the loop
Rich (BB code): 
   Next i
   If Sheets("Supply Usage Form").Range("B" & lastRow).Value = "" Then
      Rows(lastRow).Delete
   End If
   Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
 
Last edited:
Upvote 0
Solution
Eureka!!! This code now does EXACTLY what I was needing. Thank you so much for your help!!!!!!
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
372
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Change macro range from column A to B
2
Replies
10
Views
146
sachin483
S
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
211
howard
H
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
653
dinunan
D
G
  • Solved
Button for a For Loop
Replies
3
Views
127
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top