Hi,

So I have an input file that I need to be split up into various other tabs, this input file comes into my sheet nicely with my macro and generally has a very specific format so I am able to just code it to take specific row numbers and paste them into the correct tabs. If the formatting changes on my input file at all it will not work though. In very simple terms I just want to even just select all rows with a specific value in my column B.

I have attached the simplest form of data that I can provide to be able to do this, I would just like to be able to select all rows where column B = Sugar or Cotton or whatever it may be, so that I can copy and paste it into other tabs.

A small worry I have with this is also is whether there would be spaces in between the data when it gets copied and pasted. I can't have any spaces when it gets pasted.



THANKS!

Attached Files

book.xls (28.5 KB, 12 views)

What you need is the "Find" method. Here's the link that has a great explanation. After you define whatever you're looking for set a variable equal to your search variable then add ".EntireRow.Select" or copy or whatever you want to do. Here's an example of something I did before using .find :

Sub Frank ()

Dim source As Range
Dim FFS as Range
Dim destRow as String

Set source = ActiveWorkbook.Worksheets("IMPORT").Range("A9:A100 00")

Set FFS = source.Find(what:=searchString, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns)
destRow = FFS.EntireRow.Cut

End Sub


This code won't do EXACTLY what you're looking for, it's just an example of what how I used '.Find' . The link below will help tons.

http://www.globaliconnect.com/excel/...=79&Itemid=475

Thanks KingoftheKassel but I have actually just found a little bit of an easier way to do this using autofiltering

Sub CopyList()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheet1.Activate

Dim ListRange As Range
Set ListRange = Range("B3", Range("B3").End(xlDown))

ListRange.Cells(1, 1).Select

Dim i As Long

Do While ActiveCell <> ""
i = WorksheetFunction.CountIf(ListRange, ActiveCell.Value)
Range(ActiveCell, ActiveCell.Offset(i - 1, -1)).Copy
ActiveCell.Offset(i, 0).Select

Sheets.Add after:=Sheets(Worksheets.Count)
ActiveCell.PasteSpecial
On Error Resume Next
ActiveSheet.Name = Cells(1, 2).Value
Selection.EntireColumn.AutoFit

Sheet1.Activate
Loop

Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Attached Files

CopyList.xlsm (19.7 KB, 7 views)