Excel VBS issues

[juneau730]

So, after much searching the wonderful web, I still can find a solution. I am trying to create an Excel VBS Script that does multiple functions. I have some of it working (yay me), but now I need some help.

First part of it needs to find data in a specific column, that is greater than a number value and less than a subsequent number value. Once it identifies this it needs to move the rows within this range to a different worksheet. This what I have so far and it keeps hanging on the <> area...
Dim i, LastRow
LastRow = Sheets("RAW DATA_AD All Users Report").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Inactive Users").Range("A2:S7500").ClearContents
For i = 2 To LastRow
If Sheets("RAW DATA_AD All Users Report").Cells(i, "L").Value = ">30 & <90" Then
Sheets("RAW DATA_AD All Users Report").Cells(i, "L").EntireRow.Copy Destination:=Sheets("Inactive Users").Range("A" & Rows.Count).End(xlUp).Offset(1)
LastRow = Sheets("RAW DATA_AD All Users Report").Range("A" & Rows.Count).End(xlUp).Row
End If
Next i
End Sub

The other part is an IF AND function. I need to it find in a column the IF condition of "FALSE" followed by an AND IF a second column has a >= to a number value. Once it identifies this it needs to move the rows to a different worksheet.

Dim i, LastRow
LastRow = Sheets("RAW DATA_AD All Users Report").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Password Mangement").Range("A2:S7500").ClearContents
For i = 2 To LastRow
If Sheets("RAW DATA_AD All Users Report").Cells(i, "E").Value = "FALSE" And Sheets("RAW DATA_AD All Users Report").Cells(i, "L").Value = ">= 90" Then
Sheets("RAW DATA_AD All Users Report").Cells(i, "K").EntireRow.Copy Destination:=Sheets("Password Mangement").Range("A" & Rows.Count).End(xlUp).Offset(1)
LastRow = Sheets("RAW DATA_AD All Users Report").Range("A" & Rows.Count).End(xlUp).Row
End If
Next i
End Sub

 

[juneau730]

Sorry, this should read..

So, after much searching the wonderful web, I still can't find a solution. I am trying to create an Excel VBS Script that does multiple functions. I have some of it working (yay me), but now I need some help.

 

[JLGWhiz]

See if this will do it.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range
Set sh1 = Sheets("RAW DATA_AD All Users Report")
Set sh2 = Sheets("Inactive Users")
Set sh3 = Sheets("Password Management")
sh2.Range("A:S").ClearContents
    For Each c In sh1.Range("L2", sh1.Cells(Rows.Count, "L").End(xlUp))
        If c.Value > 30 And c.Value < 90 Then
            c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
sh3.Range("A:S").ClearContents
    For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, "E").End(xlUp))
        If c = False And c.Offset(7).Value >= 90 Then
            c.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

 

[juneau730]

Hi, thanks for the quick reply.. It worked if I removed this part of it, but it also cleared row 1, the headers, which I need to keep.

Next
sh3.Range("A:S").ClearContents
For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, "E").End(xlUp))
If c = False And c.Offset(7).Value >= 90 Then
c.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
End If

There is a total of 6 sheets in this workbook, all need to have different data populated to them. So I need one script that will perform all the functions required. I'll take this and play with it some tomorrow, to add in all the sheet data, and what I need to happen. Once I have that all put together, I'll shoot it up. Thanks for your help!

 

[JLGWhiz]

Hi, thanks for the quick reply.. It worked if I removed this part of it, but it also cleared row 1, the headers, which I need to keep.

Next
sh3.Range("A:S").ClearContents
For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, "E").End(xlUp))
If c = False And c.Offset(7).Value >= 90 Then
c.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
End If

There is a total of 6 sheets in this workbook, all need to have different data populated to them. So I need one script that will perform all the functions required. I'll take this and play with it some tomorrow, to add in all the sheet data, and what I need to happen. Once I have that all put together, I'll shoot it up. Thanks for your help!

OK, guess I got too innovative and tried to combine everything into one procedure. Good luck.
Regards, JLG

 

[juneau730]

Oh, no, that is exactly what I need, one script that can be used... I have a monthly report that I have to run, sorting through 5k + lines, and moving different data to the various tabs. I have one action built and working. I will be trying to incorporate that into what you provided and then I will put it up here for your assistance tweaking. Thanks so much for your help

 

[juneau730]

Okay, so here is what I have, using what you provided me, adding the data in your format for the additional sheets.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, sh5 As Worksheet, sh6 As Worksheet c As Range
Set sh1 = Sheets("RAW DATA_AD All Users Report")
Set sh2 = Sheets("Inactive Users")
Set sh3 = Sheets("Never Logged On")
Set sh4 = Sheets("Inactive Over 90-Days Enabled")
Set sh5 = Sheets("Inactive Over 365-Days")
Set sh6 = Sheets("Password Mangement")
sh2.Range("A:S").ClearContents
For Each c In sh1.Range("L2", sh1.Cells(Rows.Count, "L").End(xlUp))
If c.Value > 30 And c.Value < 90 Then
c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
sh3.Range("A:S").ClearContents
For i = 2 To LastRow
If Sheets("RAW DATA_AD All Users Report").Cells(i, "K").Value = "" Then
Sheets("RAW DATA_AD All Users Report").Cells(i, "K").Cells.Clear
End If
If Sheets("RAW DATA_AD All Users Report").Cells(i, "K").Value = "" Then
Sheets("RAW DATA_AD All Users Report").Cells(i, "K").EntireRow.Copy Destination:=Sheets("Never Logged On").Range("A" & Rows.Count).End(xlUp).Offset(1)
LastRow = Sheets("RAW DATA_AD All Users Report").Range("A" & Rows.Count).End(xlUp).Row
End If
Next
sh4.Range("A:S").ClearContents
End If
Next
sh5.Range("A:S").ClearContents
End If
Nextsh3.Range("A:S").ClearContents
End If
Next
sh6.Range("A:S").ClearContents
For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, "E").End(xlUp))
If c = False And c.Offset(7).Value >= 90 Then
c.EntireRow.Copy sh6.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub

Sheet 2 is what you gave me and works, minus removing the headed row (row 1) that I need to keep on all sheets.
Sheet 3 is mine, but likely in the wrong format, but it does exactly what is needed.
Sheet 4 needs to look at Sh 1 column H for the value "FALSE" and column K to ignore BLANK and column L for >= 90 then copy that from Sh1 to Sh4
Sheet 5 needs to look at Sh 1 column H for the value "TRUE" and column K to ignore BLANK and column L for >= 365 then copy that from Sh1 to Sh5
Sheet 6 (what you provided) needs to look at Sh 1 column E for the value "FALSE" and column K to ignore BLANK and column M for >= 90 then copy that from Sh1 to Sh6

 

[JLGWhiz]

Okay, so here is what I have, using what you provided me, adding the data in your format for the additional sheets.


Sheet 2 is what you gave me and works, minus removing the headed row (row 1) that I need to keep on all sheets.
Sheet 3 is mine, but likely in the wrong format, but it does exactly what is needed.
Sheet 4 needs to look at Sh 1 column H for the value "FALSE" and column K to ignore BLANK and column L for >= 90 then copy that from Sh1 to Sh4
Sheet 5 needs to look at Sh 1 column H for the value "TRUE" and column K to ignore BLANK and column L for >= 365 then copy that from Sh1 to Sh5
Sheet 6 (what you provided) needs to look at Sh 1 column E for the value "FALSE" and column K to ignore BLANK and column M for >= 90 then copy that from Sh1 to Sh6

What does "to ignore BLANK" mean? If K is blank do nothing? Or what? What if K is not blank?

BTW, when posting code, select the code section then click the pound symbol (#) in the tool bar and it will put code tags on code to hold the formatting.

 

[juneau730]

After some playing and clean up, I got more fixed.
The Clear.Contents no longer clears out the header row on any of the sheets.

sh2 is complete and doing exactly what I need.
sh3 not working at all
sh4 - sh6 all work except for the part find in column K empty cells and ignore that row

 

[juneau730]

So, in each row, I need it to look at column K first, if the cell is empty to skip and move to the next row column k, until it finds data in column K, when column K has data then to look at column L (or M) to see if it meets the < > = parameter. If it meets the < > = parameters, then copy that data to the respective sheet.

I hope that makes sense.