copying cells from sheet 1 to various other sheets

[sbrown64]

Hi

I have a spreadsheet which has over 1000 lines, all for varying companies. What I would like to do is create an option so when you run it, it will copy the relevant lines to their respective sheet. The code listed below is some I have seen on here, which works to a point. It will run for one company, bit if I press run again it adds the same ones' to sheet 2. The other point is how to make it do if for ALL companies and not just the one? I hope you can help.

Regards

Stephen

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Copy_Bd()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheets(1).Activate
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "F").End(xlUp).Row + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] For i = 1 To Lastrow
If Cells(i, 4).Value = "Bd" Then
Rows(i).Copy Destination:=Sheets(2).Rows(Lastrowa)
Lastrowa = Lastrowa + 1
End If
Next
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub[/FONT]

 

[Fluff]

It's the parts in red here

         If Not Evaluate("isref('" & [COLOR=#ff0000]Left(Ky, 30)[/COLOR] & "'!A1)") Then
            Sheets.Add(, Sheets(1)).Name = [COLOR=#ff0000]Left(Ky, 30)[/COLOR]
            Ws.Range("A1:A" & UsdRws).SpecialCells(xlVisible).EntireRow.Copy Sheets([COLOR=#ff0000]Left(Ky, 30)[/COLOR]).Range("A1")
            Ws.Range("M5:M" & UsdRws).SpecialCells(xlVisible).Value = Date
         Else
            On Error Resume Next
            Ws.Range("A5:A" & UsdRws).SpecialCells(xlVisible).EntireRow.Copy Sheets([COLOR=#ff0000]Left(Ky, 30)[/COLOR]).Range("D" & Rows.Count).End(xlUp).Offset(1, -3)

You can happily change the 30 to another number, as long as it's no greater than 31, but be careful about making it too small.
Otherwise you could end up with company names like "Cannon" and "Cannon UK" being on the same sheet.

 

[sbrown64]

Are you saying that bit of code will work in any macro?

 

[Fluff]

No.
You can use the left function in VBA exactly the same way as in Xl, it just depends on what value you want it to operate on & where that value is.