monkeykg
07-23-2014, 02:43 AM
I am trying to create a macro so when i push a button it sort the data based on rising numbers in column B and if it has a 1 in column C copy that row into the next available row in worksheet 2. This is the code I am currently using. The problem is it is sorting everything fine and copying then deleting but every time it copies it is copying row 1 (my headers) with it irregardless of whats in C1.
Sub test()
'Sorts rows based lowest to highest in column B
Range("b2").CurrentRegion.SOrt key1:=Range("b2"), order1:=xlAscending, Header:=xlGuess
'If column C has a 1 in it then copy the row to next avaliable place in sheet 2
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Range("A:C").AutoFilter field:=3, Criteria1:="1"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With
Worksheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
'If columnC has a 1 in it delete that row
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("A2000").End(xlUp))
Do
Set c = SrchRng.Find("1", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
Thanks
Sub test()
'Sorts rows based lowest to highest in column B
Range("b2").CurrentRegion.SOrt key1:=Range("b2"), order1:=xlAscending, Header:=xlGuess
'If column C has a 1 in it then copy the row to next avaliable place in sheet 2
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Range("A:C").AutoFilter field:=3, Criteria1:="1"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With
Worksheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
'If columnC has a 1 in it delete that row
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("A2000").End(xlUp))
Do
Set c = SrchRng.Find("1", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
Thanks