Hi,

I want to find and select portions of a large excel spreadsheet. Onc
I determine the range, I want to select it, copy it, and paste it t
another worksheet, then find the next range. I 've make some headway
but I need help programatically selecting a range for the cut/past
operation. Below is some of my code. I need to use variable to choos
starting and ending cells to create my range. This is where I need hel
or ideas.

Dim myfile As String
Dim stopstring As String
Dim StartAddress As String
Dim EndAddress As String
Dim wsSource As Worksheet, wsDestination As Worksheet, wsSummary A
Worksheet
Dim Rng As Range, Rng1 As Range

Set wsDestination = ActiveSheet
'myfile = Application.GetOpenFilename

'open workbook, and get handle on first worksheet
Set wsSource = Workbooks.Open("C:\testdata.xls").Sheets(1)
'Set wsSource = Workbooks.Open(myfile).Sheets(1)

'copy a chunk, including formats
'wsSource.Range("A1:C60").Copy wsDestination.Range("A1")

'look for the word widgets
Set Rng = Range("A1:C60").Find(What:="firstWidget", lookat:=xlPart
LookIn:=xlValues)
StartAddress = ActiveCell.Address 'First valid row

stopstring = "" 'string that determines an invalid row (empty)

Do While ActiveCell.Value < stopstring
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
EndAddress = ActiveCell.Address 'last valid row

'copy selection to a temp worksheet where I union all the copy/paste
selections
wsDestination.Range(StartAddress:EndAddress).Cop
wsSummary.Range("A1")
'close workbook
wsSource.Parent.Close (False)

End Sub



Thanks for any help

--
mnewna
-----------------------------------------------------------------------
mnewnam's Profile: http://www.excelforum.com/member.php...fo&userid=1584
View this thread: http://www.excelforum.com/showthread.php?threadid=27333