Use Cell Values to define and select a range

Good afternoon - I'm working on a spreadsheet and I have run into a snag.

I have used the concatenate function of Excel to determine a range of cells that I need to copy.

In my workbook, the sheet with this information is called "Lists and References." The range I would like to select is on "Daily."

In cells I42:I49, I have the ranges in the format Daily!W7:W20 - this indicates the selection I would like to copy onto another sheet, called "Show your Work." The values in the cells that identify the range I want to select come from the concatenate function.

I'm stuck in VBA. Essentially, I want to use the values in the cell, for example I42, in my code for the range selection. Is there a very simple way to do this? If someone can help me get the range selected, then I can handle the rest. Thanks!

Originally Posted by WadeLair

Good afternoon - I'm working on a spreadsheet and I have run into a snag.

I have used the concatenate function of Excel to determine a range of cells that I need to copy.

In my workbook, the sheet with this information is called "Lists and References." The range I would like to select is on "Daily."

In cells I42:I49, I have the ranges in the format Daily!W7:W20 - this indicates the selection I would like to copy onto another sheet, called "Show your Work." The values in the cells that identify the range I want to select come from the concatenate function.

I'm stuck in VBA. Essentially, I want to use the values in the cell, for example I42, in my code for the range selection. Is there a very simple way to do this? If someone can help me get the range selected, then I can handle the rest. Thanks!

I have cells with the beginning and the end ranges of the array - these are also in one column, if that helps.

That is a little over my head! Thanks very much.

So, if the destination for the values I copy is Show your Work!B30:O30, then, how does this line look?

rng.Copy Destination:=Sheets("Show your Work").Cells(Rows.Count, "A").End(xlUp).Offset(1)

???

Thanks again for all your hard work. This just isn't doing it for me.

I'm thinking about a different approach, maybe it will be easier.

OK - I have written a short function to give me the location of where certain text appears. Then, I use the LEN functions to remove the $, then I use concatenate to add in the name of the sheet where this is found and add the :. I may have made this too complicated, and I'm not sold on any of this. I do need the search capability because the text I'm searching for could be in different columns on the Daily! sheet.

What if...We took the ranges identified below and named them as a function, then, just copied the function? Easier?


Field Formatting Column Begin End Range
Net Rooms Available $W$4 $W W W7 W20 Daily!W7:W20
OOO Rooms $AW$4 $AW AW AW7 AW20 Daily!AW7:AW20
Transient Rooms Sold $Y$5 $Y Y Y7 Y20 Daily!Y7:Y20
Group Rooms Sold $AL$5 $AL AL AL7 AL20 Daily!AL7:AL20
Group Rooms Available $AM$5 $AM AM AM7 AM20 Daily!AM7:AM20
Arrivals $AU$4 $AU AU AU7 AU20 Daily!AU7:AU20
Departures $AV$4 $AV AV AV7 AV20 Daily!AV7:AV20
Arrival Date $B$4 $B B B7 B20 Daily!B7:B20


Like for this, how could be define what we name TestFunction, based on what appears in these cells.


ActiveWorkbook.Names.Add Name:="TestFunction", RefersToR1C1:= _
"='Lists and References'!R44C16:R50C16"

Application.Goto Reference:="TestFunction"
Selection.Copy
Range("R56").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True


While loops are nice, I don't mind just adding the code for each one instead of completing the loop.

Thanks,