It's been a long time since I've written macros and I guess I have gotten (extremely) rusty. I have a workbook with some data and I want to move all lines with a particular error code onto another worksheet called "Do Not Work"
I have 2 problems.
1) I have pieced together the below code and it only moves some of the lines with the error code. Prior to adding in the code to create a new worksheet if the macro was executed about 5 times everything would eventually move.I know it's a problem with the loop and needing to step backwards but I can't figure out how to implement in this code.
2) Right now my code is explicitly written to move data from "Sheet1". The reality is the sheet with the data that needs to be moved will always be called something different but will always be the 1st worksheet. Is there a way to address this?
Thanks for any help!
<code>Sub BillRunErr_27807()
Dim Newsheet
Set Newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
Newsheet.Name = "DO NOT WORK"
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Copy
Sheets("DO NOT WORK").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Dim xRg As Range
Dim xCell As Range
Dim i As Long
Dim J As Long
i = Sheets("Sheet1").UsedRange.Rows.Count
J = Sheets("DO NOT WORK").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("DO NOT WORK").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A1:A" & i)
For Each xCell In xRg
If CStr(xCell.Value) Like "*YMM27807*" Then
xCell.EntireRow.Copy Destination:=Worksheets("DO NOT WORK").Range("A" & J + 1)
xCell.EntireRow.Delete
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub </code>
I have 2 problems.
1) I have pieced together the below code and it only moves some of the lines with the error code. Prior to adding in the code to create a new worksheet if the macro was executed about 5 times everything would eventually move.I know it's a problem with the loop and needing to step backwards but I can't figure out how to implement in this code.
2) Right now my code is explicitly written to move data from "Sheet1". The reality is the sheet with the data that needs to be moved will always be called something different but will always be the 1st worksheet. Is there a way to address this?
Thanks for any help!
<code>Sub BillRunErr_27807()
Dim Newsheet
Set Newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
Newsheet.Name = "DO NOT WORK"
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Copy
Sheets("DO NOT WORK").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Dim xRg As Range
Dim xCell As Range
Dim i As Long
Dim J As Long
i = Sheets("Sheet1").UsedRange.Rows.Count
J = Sheets("DO NOT WORK").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("DO NOT WORK").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A1:A" & i)
For Each xCell In xRg
If CStr(xCell.Value) Like "*YMM27807*" Then
xCell.EntireRow.Copy Destination:=Worksheets("DO NOT WORK").Range("A" & J + 1)
xCell.EntireRow.Delete
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub </code>