Solved: Copy range help

[jmarkc]

I'm hoping someone can help. I've been searching this site and other quite a bit, but cannot find the code I need
or can't modify code enough to suit my need.

I have a couple of simple worksheets that will be part of a bigger project. To begin with, 1 worksheet ("Output")
will contain some company information and by pressing a command button, the user can pull up the source of that
information.

So, sheet 2 ("Data List") contains all the source information. In that worksheet, column A contains the company
name, column b contains the source information, which can be 2 rows or 15 rows of information (in the form of hyperlinks).

I have some code that looks in cell b4 of the Output sheet for the company name, then finds it in the Data List sheet.
That works fine. What I can't seem to do is to copy the accompanying data in column b of the Data List sheet and
paste it to B10 in the Output sheet.

ub SupportDataMacro1()
Application.ScreenUpdating = False

Dim Name As String
Dim rFound As Range

' Find Supplier Name from 1 sheet in another sheet
Name = Sheets("Output").Range("b4")

On Error Resume Next
With Sheets("Data List")
Set rFound = .Columns(1).Find(What:=Name, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then Application.Goto rFound, True
End With
' Once Supplier Name is found, copy web data

Selection.Offset(0, 1).Activate

'NEED SOME HELP HERE!

Application.ScreenUpdating = True

End Sub
I think it's a simple solution, but I can't seem to wrap my hands around it. Any help is very much appreciated.
I have attached the workbook.

[Bob Phillips]

Where are those sources supposed to go?

[jmarkc]

The sources will be copied from "Data List" sheet to cell B10 of "Output" sheet. Because the sources can be copied from a different number of rows depending on the company name, I know that it will extend beyond B10, but that should be the starting point.

Thanks for the help.

[jmarkc]

Just bumping this message up in hopes for help.

Yesterday I spent some time trying a loop to get the data in the column from the active cell to when it sees a blank cell. I tried For/Next & Do /While but had little success. I seperated the data with a space with thoughts that it would be the "trigger" for the routine to stop. I am new to VBA and don't have it down. Can someone assist in solving this?

The code below runs continuously and pastes 20 rows of data, presumably because of icount2.


Sub SupportDataMacro1()
' Application.ScreenUpdating = False

Dim Name As String
Dim rFound As Range
Dim Webdata As Object

' Find Supplier Name from 1 sheet in another sheet
Name = Sheets("Output").Range("b4")

On Error Resume Next
With Sheets("Data List")
Set rFound = .Columns(1).Find(What:=Name, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then Application.Goto rFound, True
End With
' Once Supplier Name is found, copy web data

Selection.Offset(0, 1).Activate

For icount = 1 To 20
Set Webdata = ActiveCell.Offset(icount, 0)
If Webdata Is Nothing Then Application.Goto rFound, True
For icount2 = 1 To 20
ActiveCell.Offset(icount2, 0).Copy
Sheets("Output").Range("b9").Offset(icount2, 0).PasteSpecial
Sheets("data list").Activate
Next icount2
Next icount

End Sub

[jmarkc]

Found what I needed on another site.

Basically needed to copy an unspecified range and paste it to a different worksheet. Thought a loop was the answer, but it was unnecessary.

Set Range1 = Selection.Offset(1, 1)
Set Range2 = Range1.End(xlDown)
Range(Range1, Range2).Copy
Sheets("Output").Range("b10").PasteSpecial