Copy range indicated by inputbox

Question

I am looking to a model which can copy a particular range in an excel sheet to a new sheet. Currently I have five columns. The first column is the year. I want to indicate, for example with an inputboxwhich year should be copied. In column B, the weeks of the year are indicated. In the columns C until E, specific data is recorded. As a result, my sheet look like this:

Col A   Col B   Col C   Col D   ColE
Year    Week    Amount  time    forecast
2000    1       368     2000w1  400
2000    2       8646    2000w2  8500
until...
2014    52      46546   2014w52 47000

With the input box, I want to indicate that the year 2014 should be copied to the next sheet. Until now I wrote/compiled the following macro:

Sub Copy_year()
    Dim Forecastyear As String
    Dim Rng As Range

    Forecastyear = InputBox("Enter a year to forecast")

    If Trim(Forecastyear) <> "" Then
        With Sheets(2).Range("A:A")
            Set Rng = .Find(What:=Forecastyear, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If

    'Range(ActiveCell, RC[52,4]).Select
End Sub

This select the first cell which contains the year value. I want to select the full 5 columns for the row with the selected cell and the next 51 weeks (together 52 weeks). How can I select all that data?

Answer 1

Use this

Sub Copy_year()
    Dim Forecastyear As String
    Dim Rng As Range

    Forecastyear = InputBox("Enter a year to forecast")

    If Trim(Forecastyear) <> "" Then
        With Sheets(2)
            For Each cell In .Range("A:A")
                If cell.Value = Forecastyear Then'find first occurrence of year
                    Set Rng = cell
                    Exit For
                End If
            Next
            .Range(Rng.Address).Resize(52, 5).Select'resize for 52 rows and 5 columns
        End With
    End If
End Sub

What are you going to do with it now? just selecting is rather inefficient in terms of cpu time