How to find certain text and copy a data set below it

[yi3o8]

Hello,

I'm very new to VBA and am not in a position to download Excel Jeanie so I apologize in advance if I'm not following forum etiquette in how I'm asking my question.

The scenario is that I have sheet of data that is out of order but labeled. I tried to record a macro to find the label and then select the data (which has about 10 columns and 10 rows of information) but the problem is that after the macro finds the label, it selects the data based of a specific cell and so the data is out of order.

I need to find the label of the data, select and copy what's below it (Offset?), and then paste it in a different sheet.

Example of data:
a b c d e
1out of order label
2*******
3*******
4*******
5out of order label
6*******
7*******
8*******
(there will be six sets of data in total, everything works perfectly except that the data is out of order)


Thank you so much, I greatly appreciate any help with this issue.

I'm using Excel 2007

 

[yi3o8]

I dont' know if it helps, but here is the code that finds the label and then still selects specific cells instead of the data below the label:

Range("A1").Select
Cells.Find(What:="Queue Performance - TrendSC Overflow s3 (3)", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A1:I27").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A1").Select
ActiveSheet.Paste
Range("A29").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Cells.Find(What:="Queue Performance - TrendSC Tax Center s51 (51)", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A55:A69").Select
ActiveWindow.SmallScroll Down:=21
Range("A55:I81").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A29").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=36
Range("A57").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Cells.Find(What:="Queue Performance - TrendSC Tech Customer s2 (2)", After _
:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False). _
Activate
ActiveWindow.SmallScroll Down:=21
Range("A82:I108").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A57").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=27
Range("A85").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Cells.Find(What:="Queue Service Level - TrendSC Overflow s3 (3)", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveWindow.SmallScroll Down:=15
Range("A109:L135").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A85").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=33
Range("A113").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Cells.Find(What:="Queue Service Level - TrendSC Tax Center s51 (51)", After _
:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False). _
Activate
ActiveWindow.SmallScroll Down:=21
Range("A136:L162").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A113").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=30
Range("A141").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Cells.Find(What:="Queue Service Level - TrendSC Tech Customer s2 (2)", After _
:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False). _
Activate
ActiveWindow.SmallScroll Down:=21
Range("A28:L54").Select
Selection.Copy
Sheets("Avaya Data").Select
Range("A141").Select
ActiveSheet.Paste
Range("A1").Select

 

[yi3o8]

This got past the problem, Thank you for the great forum! I will be back


Dim rngCell As Range
Dim rngArea As Range

Set rngCell = ActiveCell
Set rngArea = Range(rngCell, rngCell.Offset(26, 12))

find ***.activate

Set rngCell = ActiveCell
Set rngArea = Range(rngCell, rngCell.Offset(26, 12))

find ***.activate

Set rngCell = ActiveCell
Set rngArea = Range(rngCell, rngCell.Offset(26, 12))

find ***.activate

Set rngCell = ActiveCell
Set rngArea = Range(rngCell, rngCell.Offset(26, 12))

find ***.activate

Set rngCell = ActiveCell
Set rngArea = Range(rngCell, rngCell.Offset(26, 12))