Hello all! I'm very new to Excel and VBA coding. I've searched the internet and found a lot of close topics, but still haven't been able to solve my delima.
Basically, I have column "F" in "sheet1" in which the cells contain a fomula ("=Sheet3!B4", or "=Sum(Sheet3!B4:Sheet3!B55") that return date values (ex. 9/19/2014). I need a code that, when a command button is pressed... if the dates are less than 30 days away (items are expiring), will copy the cells in columns B,C, and E to the corrosponding cells on Sheet2.
Here's what I have so far:
Sub fillorder()
Dim finalrow As Integer
Dim i As Integer
Dim ExpDate As Date
Dim lngRow As Long, lngCol As Long
'Sheet2's range is the actual content area of an order form
Sheets("Sheet2").Range("B25:J44").ClearContents
'column M containes quantity of an item needed as a value = or > 0
finalrow = Sheets("Sheet1").Range("M315").End(xlUp).Row ExpDate = Date + 30
For i = 7 To finalrow
If Cells(i, 13) > 0 Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 13).Copy 'column 13 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
End If
Next i
Sheets("Sheet1").Cells(i, 6).Value = dblVal
For lngRow = 7 To finalrow
If Cells(i, 6) < ExpDate Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 5).Copy 'column 5 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 5).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next lngRow
End Sub
The first "If Then" works just fine, but the second "If Then" returns nothing...and there are about 6 items in my inventory that are expiring.
Any help would be greatly appreciated.
Thanks in advance.
Basically, I have column "F" in "sheet1" in which the cells contain a fomula ("=Sheet3!B4", or "=Sum(Sheet3!B4:Sheet3!B55") that return date values (ex. 9/19/2014). I need a code that, when a command button is pressed... if the dates are less than 30 days away (items are expiring), will copy the cells in columns B,C, and E to the corrosponding cells on Sheet2.
Here's what I have so far:
Sub fillorder()
Dim finalrow As Integer
Dim i As Integer
Dim ExpDate As Date
Dim lngRow As Long, lngCol As Long
'Sheet2's range is the actual content area of an order form
Sheets("Sheet2").Range("B25:J44").ClearContents
'column M containes quantity of an item needed as a value = or > 0
finalrow = Sheets("Sheet1").Range("M315").End(xlUp).Row ExpDate = Date + 30
For i = 7 To finalrow
If Cells(i, 13) > 0 Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 13).Copy 'column 13 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
End If
Next i
Sheets("Sheet1").Cells(i, 6).Value = dblVal
For lngRow = 7 To finalrow
If Cells(i, 6) < ExpDate Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 5).Copy 'column 5 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 5).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next lngRow
End Sub
The first "If Then" works just fine, but the second "If Then" returns nothing...and there are about 6 items in my inventory that are expiring.
Any help would be greatly appreciated.
Thanks in advance.