Copying portions of data

[Wendy Clarkson]

Is it possible to select a specific area of the worksheet using a find
command (the data changes every day) and copy just that portion onto a new
worksheet?

Thanks

Wendy

 

[Norman Jones]

Hi Wendy,

If a verbal rule (or rules) can be devised to locate the area, then it is
very likely that someone could aaist you with code to automate this.

 

[Dave Peterson]

I agree with Norman, but this may get you started:

Option Explicit
Sub testme03()
Dim FoundCell As Range
Dim DestCell As Range
Dim WhatToFind As String

WhatToFind = InputBox(Prompt:="What to find?")

If Trim(WhatToFind) = "" Then
Exit Sub
End If
With ActiveSheet.UsedRange
Set FoundCell = .Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not Found!"
Else
Set DestCell = Worksheets("sheet2").Range("a1")
FoundCell.Resize(12, 30).Copy _
Destination:=DestCell
End If
End Sub

But be careful.

You'll want to specify all the .find options (xlwhole v. xlpart, matchcase,
etc).

And this routine finds something and pastes it to A1 of sheet2.
And it copies 12 rows by 30 columns!