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:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. 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
52,755
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
52,755
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
52,755
Office Version
  1. 365
Platform
  1. 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
52,755
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top