Selectin an specific item on the spreadsheet.

[Gmata]

Hello Guys, I am trying to record a Macro for exell and i am dealing with
some information that changes size from day to day for example.

1 day i can have 100 rows of information another day i can have 300

The information is separeted with titles and it is all located in column A.

I am trying to record a macro to separate each set of information divided by
a title to a diferen sheet.

What i did was press the record button and then i did de function search and
searched for the title then i selected everything under the words that i
searched.

The problem:

since this information changes size everyday the macro goes to the same cell
everytime it does not matter where the word i searched is located.

Sorry if this is confusing but can any body help me?

Gmata

 

[Rick Rothstein]

If your data and titles are all Column A, what distinguishes a title from
your data? Is it that your data is numeric and, of course, your titles are
not? Or something else?

 

[Gmata]

The only difference between titles and Data is that Data is all Hyperlinks.

Also the title is always the same:

The following items have become out of stock:


I need to copy all the cells under that title even when the position of that
title changes, some times Title might be on A40, sometimes A60

Thanks

 

[Tom]

If your data starts immediately below the title, and cell A1 isn't where the
first title is, place the cursor on A1.
Begin macro:
1. Click the binoculus icon to start the search. In the search box, click
"by columns" and key in part of the title, "the following items" next to
"Find What". Click "Fnd Next". Click "Close" to get out of the search box.
2. Press the down arrow key to move down to the start of the data cell.
3. Then hold down these 3 keys together, "Ctrl-Shift-Down Arrow", to select
all the data cells that are below the title. I'm assuming there are no blank
rows inside the data. If there are delete them first either manually or
write another macro to delete them.
4. Next select "Copy", open a new worksheet and "Paste".
5. Then activate the starting document.
Stop macro.

Run the macro to copy the next set of data and repeat till all is done.
Note: Leave out Step 2 if you want the title to be copied.
You can save having to execute subsequent runs by incorporating a
looping statement. Hope that helps.

Tom

 

[Rick Rothstein]

Here is a macro that should do what you want (change my example settings of
1 for the FirstTitleRow constant and "Sheet4" for the worksheet name in the
With statement to reflect your actual conditions)...

Sub SplitHyperlinksToSeparateSheets()
Dim X As Long
Dim LastRow As Long
Dim TitleRow As Long
Dim SheetName As String
Const FirstTitleRow As Long = 1
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TitleRow = FirstTitleRow
For X = FirstTitleRow + 1 To LastRow + 1
If .Cells(X, "A").Hyperlinks.Count = 0 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
SheetName = .Cells(TitleRow, "A").Value
Worksheets(Worksheets.Count).Name = SheetName
.Range("A" & (TitleRow + 1) & ":A" & (X - 1)).Copy _
Worksheets(SheetName).Range("A1")
TitleRow = X
End If
Next
End With
End Sub