Selecting a variable range to copy

[AZGJC]

I'm trying to program a macro to pick up a variable range of data and copy it. I'm doing this by having the macro find the date in the workbook and pulling the data adjacent to that date cell. The copying part is not the problem, it's how to define a range of data without specific cell addresses. For instance for today the data is in the range C4:G14, and tommorrow it will be in C20:G30, and so on. I'm also pulling the data from multiple files so it's not all in same range from file to file. Any help would be appreciated.

Thanks,
Garrett

 

[AZGJC]

Let me also add that the data isn't contiguous most of the time. I just need it to pick up those block of 60 cells wherever they may be in the worksheet.

 

[electricpete]

Not sure I understand the question. In case you want user to select a range at run-time, here is a way to do that:

Function getinputrange(prompt As String, defaultrange As Range) As Range
' Returns range corresponding to userinput
' If assigning to variable in calling procedure, use SET
Dim UserRange As Range
Dim originalrange As Range
    Set originalrange = Selection

    ' DefaultRange = Selection.Address
    On Error GoTo Canceled
    Set UserRange = Application.InputBox _
                    (prompt:=prompt, _
                     Title:="range prompt", _
                     Default:=defaultrange.Address, _
                     Type:=8)

Canceled:

    originalrange.Select        ' put cursor back
    Set getinputrange = UserRange
End Function

If not, then the code has to figure out the range. We can't help you with that unless you explain the logic that defines this range.

 

[electricpete]

OK, I see maybe it is defined. Let me clarify, you want to search a sheet for cells containing dates, and then build a range which contains every cell to the right of a date?

Also would help to know what differentiates these dates? Are they text dates? Are they numbers formated as dates?

 

[AZGJC]

I apologize if I'm not clear. I'm having the macro find a date formatted like (20080627) somewhere in a workbook, and I'm good with this code. Then I'm having it move over two columns and down three rows, and then I need to select a range that is in a block format, the data in that range may or may not be contiguou.s and may have blanks on the end or sides. But the block that I need to pick will always be the same number of cells.

Here's the part I cannot figure out: since the date is always in a different place in the workbook from the previous day, the range that I need to select will always be in a different location (But the same size). How can I get it to pick a specific range without the normal naming convention (B20:E40, for example). And I can't use named ranges because I'd have to name 250 individual ranges (# of working days in the year)

 

[electricpete]

Still not sure I get the problem.

But from the information in your very last paragraph, I'm thinking the offset property of a range may do what you want. Are you already familiar with that?

 

[AZGJC]

Not too much, but I can do some reasearch. Thanks for the help.

 

[electricpete]

dim myrange as range
set myrange = Range("B6:C7").Offset(4, 5).Resize(7, 9)

Will create myrange referring to address $G$10:$O$16

The starting point of myrange is 4 rows below B6 and 5 columns to the right of B6

The size of myrange is 7 rows by 9 columns

 

[AZGJC]

Hey, I think that will do it if I can put my date variable as the starting point of myrange.