VBA Macro question

[bignoggin]

I have a worksheet that is 15000 cells long and I was wondering if there is a way I can make a macro the copy all the cells between 2 keywords. from a cell that has "Conference" in it until the next cell that has "Conference" in it

All the cells are in column A

 

[Hui]

Use an Advanced Filter
Refer to: http://chandoo.org/wp/2011/10/10/how-to-use-advanced-filters/

 

[bignoggin]

Im still not sure if I can use these to Move a section of cells to a different worksheet so the rest of my macro can run the data from there.

Any help with this would be greatly appreciated

 

[Chihiro]

• Use Copy to Another Location Option to copy the filtered values elsewhere.

So yes, you can use Advanced filter to move (i.e. copy) data that meets criteria to another sheet.

There are many detailed tutorials out there. See below for some examples.

http://www.exceltrainingvideos.com/...et-to-another-using-advanced-filter-with-vba/

 

[bignoggin]

What I'm working with is data that is being presented like this(this is all in column A):

Conference Description: Number 1
Number 1 Data
Number 1 Data

Conference Description: Number 2
Number 2 Data
Number 2 Data

I am trying to get the macro to move the data starting at that first "Conference Description" until it gets to the next "Conference Description" and move it to a separate sheet like:

Conference Description: Number 1
Number 1 Data
Number 1 Data

I cant just have it move a fixed number of rows because the "Number 1 data" is variable sometimes its 2 rows sometimes its 200

 

[ashish mehra]

Hi,

See if this is Ok.

Sub SearchText_Move2_OtherSheets()
'This macro will find the Description till it occurs in column A

Application.ScreenUpdating = False

    Dim SearchCell As Range
    Dim SearchRange As Range
    Dim SearchDescription As String
    Dim FirstSearchCell As String
           
    Worksheets("Sheet1").Activate
       
    SearchDescription = InputBox("Enter text you want to search!")
   
    Set SearchRange = Range("A2", Range("A1").End(xlDown))
   
    Set SearchCell = SearchRange.Find(what:=SearchDescription, MatchCase:=False, lookat:=xlPart)
   
    If SearchCell Is Nothing Then
        MsgBox "Text was not found"
    Else
       
        FirstSearchCell = SearchCell.Address
        Do
            Range(SearchCell, SearchCell.End(xlToRight)).Copy
            Worksheets("Description").Activate
            ActiveCell.PasteSpecial
            ActiveCell.Offset(1, 0).Select
   
            Worksheets("Sheet1").Activate
           
            Set SearchCell = SearchRange.FindNext(SearchCell)
        Loop While SearchCell.Address <> FirstSearchCell
    End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub