E
E-on
Sub Copytom()
Dim rng As Range, i As Long, ws As Worksheet, sh As Worksheet
For Each ws In Worksheets
On Error Resume Next
If sh.Name = "New" Then sh.Delete
If sh.Name <> "New" Then
Set sh = Sheets.Add
sh.Name = "New"
End If
For i = 3 To 100
If ws.Cells(i, 5).Value = " Stockwell Motors" Then
ws.Range("F" & i & ":G" & i).Cop
Worksheets("New").Range("A" & _Rows.Count).End(xlUp).Offset(1, 0)
End If
Next
Next
End Sub
This is my second post on this forum. I had found a solution for m
first post and hopefully to get for a second post too.
I am really mystified by the above code. Let me explain line by line. I
is not my code, got it from someone.
For Each ws In Worksheets ‘to loop through each sheet
If sh.Name = "New" Then sh.Delete ‘ if sheet name “new” does exis
delete it and it does not create a new one
If sh.Name <> "New" Then
Set sh = Sheets.Add
sh.Name = "New"
For i = 3 To 100 ‘ loop through from row 3 to row 100
If ws.Cells(i, 5).Value = " Stockwell Motors" Then
‘ in each worksheet of each row of column E, look for Stockwell motors
And the last line is to copy column F and G of row which has Stockwel
motors in to the new created sheet.
I understand all lines, but lost as to why the code creates 97 blan
sheets instead of coping rows in to the new sheet. What is wrong wit
the code? Why the code is not doing what is supposed to do. Pleas
help.
Basically, I have many sheets. They all have the same number of rows an
column numbers, hence I set up rng as Set rng = ws.Range("A3:R100")
Where I am stuck is when I try to loop through each cell in column "E
of ws.Range("A3:R100"). If any cell in each sheet of column "E" has
name “Stockwell Motors", then copy, but I am stuck. i am sure thes
lines are wrong
Dim rng As Range, i As Long, ws As Worksheet, sh As Worksheet
For Each ws In Worksheets
On Error Resume Next
If sh.Name = "New" Then sh.Delete
If sh.Name <> "New" Then
Set sh = Sheets.Add
sh.Name = "New"
End If
For i = 3 To 100
If ws.Cells(i, 5).Value = " Stockwell Motors" Then
ws.Range("F" & i & ":G" & i).Cop
Worksheets("New").Range("A" & _Rows.Count).End(xlUp).Offset(1, 0)
End If
Next
Next
End Sub
This is my second post on this forum. I had found a solution for m
first post and hopefully to get for a second post too.
I am really mystified by the above code. Let me explain line by line. I
is not my code, got it from someone.
For Each ws In Worksheets ‘to loop through each sheet
If sh.Name = "New" Then sh.Delete ‘ if sheet name “new” does exis
delete it and it does not create a new one
If sh.Name <> "New" Then
Set sh = Sheets.Add
sh.Name = "New"
For i = 3 To 100 ‘ loop through from row 3 to row 100
If ws.Cells(i, 5).Value = " Stockwell Motors" Then
‘ in each worksheet of each row of column E, look for Stockwell motors
And the last line is to copy column F and G of row which has Stockwel
motors in to the new created sheet.
I understand all lines, but lost as to why the code creates 97 blan
sheets instead of coping rows in to the new sheet. What is wrong wit
the code? Why the code is not doing what is supposed to do. Pleas
help.
Basically, I have many sheets. They all have the same number of rows an
column numbers, hence I set up rng as Set rng = ws.Range("A3:R100")
Where I am stuck is when I try to loop through each cell in column "E
of ws.Range("A3:R100"). If any cell in each sheet of column "E" has
name “Stockwell Motors", then copy, but I am stuck. i am sure thes
lines are wrong