Need help please writing an Excel Macro from you professionals. My coding knowledge is through Word VBA but little to limited with Excel.
I have a report that is run each night that continually updates with additional data. I dump the report raw data into a spreadsheet each morning. Now I need to select and cut out all the rows out of the entire spreadsheet that contains the number ‘2’ in a column (say column ‘I’) and place in the next worksheet. Then after that is all separated out, I need the macro to go back and select all number ‘3’ rows from the same column, select the entire rows, cut from main spreadsheet, and place in the next successive worksheet. Then ‘4’, then ‘5’ then etc. I don’t have to move any data with a ‘1’. The column may have additional integer numbers added each night, so first nights data in column ‘I” may be from 1 to 8, then next night may be 1 to 22, then next night data from 1 to 15, etc.
I found code posted by another programmer to perform the selecting of all their rows that contain the letter ’G’ for their application, adapted for my first number ‘2’, but then can’t figure out how to loop it back to hunt for the next successive number ’3’, then ‘4’, then ‘5’, etc.
This is what I have so far. Works fine for the number ‘2’. Now I need the macro loop to repeat itself until no number is found. Fails on remaining selecting of rows. Can’t figure out how to bound this code into a loop for column ‘I’ with a number range of say 2 – 100.
Thanks for your help!!
Sub Test()
Dim i As Integer
For i = 2 To 100
Columns("I:I").Select
Dim a As Range
Dim rngA As Range
For Each a In Intersect(ActiveSheet.UsedRange, Columns("I"))
If a = i Then
If rngA Is Nothing Then Set rngA = a.EntireRow
Set rngA = Union(rngA, a.EntireRow)
End If
Next a
rngA.Select
Selection.Cut
Sheets.Add After:=ActiveSheet
Cells.Select
ActiveSheet.Paste
Sheets("WM200 - Work Order Material Rep").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Next i
End Sub
Raw data looks like this with about 50 more columns.
I have a report that is run each night that continually updates with additional data. I dump the report raw data into a spreadsheet each morning. Now I need to select and cut out all the rows out of the entire spreadsheet that contains the number ‘2’ in a column (say column ‘I’) and place in the next worksheet. Then after that is all separated out, I need the macro to go back and select all number ‘3’ rows from the same column, select the entire rows, cut from main spreadsheet, and place in the next successive worksheet. Then ‘4’, then ‘5’ then etc. I don’t have to move any data with a ‘1’. The column may have additional integer numbers added each night, so first nights data in column ‘I” may be from 1 to 8, then next night may be 1 to 22, then next night data from 1 to 15, etc.
I found code posted by another programmer to perform the selecting of all their rows that contain the letter ’G’ for their application, adapted for my first number ‘2’, but then can’t figure out how to loop it back to hunt for the next successive number ’3’, then ‘4’, then ‘5’, etc.
This is what I have so far. Works fine for the number ‘2’. Now I need the macro loop to repeat itself until no number is found. Fails on remaining selecting of rows. Can’t figure out how to bound this code into a loop for column ‘I’ with a number range of say 2 – 100.
Thanks for your help!!
Sub Test()
Dim i As Integer
For i = 2 To 100
Columns("I:I").Select
Dim a As Range
Dim rngA As Range
For Each a In Intersect(ActiveSheet.UsedRange, Columns("I"))
If a = i Then
If rngA Is Nothing Then Set rngA = a.EntireRow
Set rngA = Union(rngA, a.EntireRow)
End If
Next a
rngA.Select
Selection.Cut
Sheets.Add After:=ActiveSheet
Cells.Select
ActiveSheet.Paste
Sheets("WM200 - Work Order Material Rep").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Next i
End Sub
Raw data looks like this with about 50 more columns.