Difference between two columns

[commo27]

Hey everyone, I have created a pretty sophisticated parts database and one of the last things i cant figure out is outputting a list of parts that are below the Minimum Quantity.
I have a Administration Userform and i want the parts guy to be able to click a cmd button and he will then have a list of Parts that are below the minimum stock level that he can print.

Column J is actual stock level
Column L is Minimum level.

I have posted the link to my database if anyone would like to use it. In my database, you have to login into administration userform to be able to access the workbooks and vba.
To login to get to the admin screen
Username is Admin
Password is Password

Thanks for all your help.



https://www.sugarsync.com/pf/D219095_87_6866553312

 

[commo27]

Thank for your time kpark!! Looks like i ran into one debugging issue.

The first code was put in my user form called Admin. after clicking the command button i got the following highlighted

Worksheets("Parts").Range(Cells(1, 1), Cells(1, 13)).Copy newWS.Range("A1")

A new sheet was created
When i compile my Project, i get the following error.
Variable not defined and highlights
newWS.Delete in the second code.

Let me see if i got the coding correct.

First code goes into the userform

Private Sub admincheckout_Click()
    Application.ScreenUpdating = False
    Dim i&, LR&, count&
    LR = Worksheets("Parts").Range("J" & Rows.count).End(xlUp).Row
    Set newWS = Worksheets.Add
    
    Worksheets("Parts").Range(Cells(1, 1), Cells(1, 13)).Copy newWS.Range("A1")
    count = 2
    
    For i = 2 To LR
        If Range("J" & i).Value < Range("L" & i).Value Then
            Worksheets("Parts").Range(Cells(i, 1), Cells(i, 13)).Copy newWS.Range("A" & count)
            count = count + 1
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Second code goes into my workbook module which has some other coding already.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call HideSheets
         
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
    Application.DisplayAlerts = False
    newWS.Delete
    Application.DisplayAlerts = True
End Sub

Third code goes into Normal module which i have a module 1 with other code

Public newWS As Worksheet
Sub Search()
  frmSearchDatabase.Show
End Sub

Sub AddListViewControl()

  Dim P As Variant
  Dim Paths As Variant
  Dim RefFile As String
  Dim X As String
  
    RefFile = "MSCOMCTL.OCX"
    
    Paths = Split(Environ("Path"), ";")
      For Each P In Paths
        X = Dir(P & "\" & RefFile)
        If X = RefFile Then Exit For
      Next P
  
      If X = "" Then
         MsgBox "The directory for " & RefFile & " could not be found."
         Exit Sub
      Else
         RefFile = P & "\" & RefFile
      End If
      
    On Error Resume Next
      Application.VBE.ActiveVBProject.References.AddFromFIle RefFile
      If Err = 0 Or Err = 32813 Then Err.Clear
    On Error GoTo 0
  
End Sub

 

[kpark91]

Hi, the reason why it's erroring is because you put it in the userform module.
So, is does not know when it's referring to .Cells

You would just have to explicitly state the worksheet.
Replace this with ur userform code you have posted

    Application.ScreenUpdating = False
    Dim i&, LR&, count&
    LR = Worksheets("Parts").Range("J" & Rows.count).End(xlUp).Row
    Set newWS = Worksheets.Add
    
    Worksheets("Parts").Range(Worksheets("Parts").Cells(1, 1), Worksheets("Parts").Cells(1, 13)).Copy newWS.Range("A1")
    count = 2
    
    For i = 2 To LR
        If Range("J" & i).Value < Range("L" & i).Value Then
            Worksheets("Parts").Range(Worksheets("Parts").Cells(i, 1), Worksheets("Parts").Cells(i, 13)).Copy newWS.Range("A" & count)
            count = count + 1
        End If
    Next i
    Application.ScreenUpdating = True

 

[commo27]

Almost there..Seems to be two more issues.

The new worksheet is being created with the header columns but none of the rows are being filled with content.


If i close the workbook without running the report, i get a debug issue referencing to the newWS.delete

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call HideSheets
         
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
     Application.DisplayAlerts = False
    [B]newWS.Delete[/B]
    Application.DisplayAlerts = True
End Sub

 

[commo27]

Here are some screen shots

Parts Page

New sheet created by your code

 

[commo27]

Any ideas on this?

 

[commo27]

It looks like i figured out the debugging issue. I changed out the newws.delete code for the following

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next
Application.DisplayAlerts = True

I am still having issues with the temp spreadsheet not populating with the content

 

[commo27]

Looks like i finally got everything working and included the new sheet to autofit the columns,

Thanks for all the help

Private Sub Adminminreport_Click()
   Dim i As Long, LR As Long, count As Long
   Dim newWS As Worksheet, partsWS As Worksheet
   Set newWS = Worksheets.Add
   Set partsWS = Worksheets("Parts")
   Application.ScreenUpdating = False

   LR = partsWS.Range("J" & Rows.count).End(xlUp).Row
   Range(partsWS.Cells(1, 1), partsWS.Cells(1, 13)).Copy _
                                     newWS.Range("A1")
   count = 2
   
   For i = 2 To LR
    If partsWS.Range("J" & i).Value < partsWS.Range("L" & i).Value Then
         Range(partsWS.Cells(i, 1), partsWS.Cells(i, 13)).Copy _
                                    newWS.Range("A" & count)
         count = count + 1
    End If
newWS.Select
With Selection
Cells.EntireColumn.AutoFit
End With
   Next i
   Application.ScreenUpdating = True
   newWS.Activate
   Unload Me
End Sub