using vba to reference cells on different sheets

[Benoni2]

I am trying to move the values from one excel sheet to another Sheet1 is named "Quarter Overview" and sheet2 is named "Activity Report".

The nicest solution would be to just use an excel function or referencing to move the values from sheet to another. But what I have found so far is that the the place I move the values doesn't contain the values, but the reference to the values from the previous sheet. I need the values themselves!

So part 2, I am trying to move them using VBA. I can move around the sheet using I think the range object. But I can't for the life of me figure out how to reference from sheet to sheet without it giving me errors about the "subscript being out of range." Any help?

 

[AnotherHiggins]

TONS of ways to get around this.

My first thought, having read your first attempted solution, is to forego VBA and simply copy, Paste Special > Values to replace the formulas with the values they retrieve. To do this, simply copy a cell or a range (can be several entire columns - whatever you need, really) then while that same range is selected, go to Edit > Paste Special and tick the box beside Values.

But one question, why do you need to replace the formulas with actual values? Leaving them as formulas keeps things dynamic. The cell will display the resulting value and will print correctly.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Benoni2]

Okay, maybe a better description. I want to use the built in dropdown menu feature using the "Data/Validation/list." It seems to only allow values that are on its own worksheet. It won't seem to reference values on another sheet. If I move values over dynamically to the page first and then reference them with the dropdown data/validation dropdown feature, it gives the reference values (ex. B3) and not the values contained on the first page.

Case: I have the user fill in several cells on the first page with city names they serve throughout Alaska. On the second page, one column on the activties report gives them a combo box list of all possible cities that they serve so they can select which city a particular activity was done in.

It is proving most difficult to get that list to hold the city name values from the first page.

 

[AnotherHiggins]

Ah, got it. Yes, "." It seems to only allow values that are on its own worksheet" is a limitation of Excel - kind of.

The way around this is to use a Named Range.

For example, on Sheet2 you can select column A:A. In the box to the left of the "=" sign (will read "fx" in Excel XP), type in a name - let's say rngMyList.

Now back on Sheet1, in the Data Validation, you can choose List, then type
[COLOR=blue white]=rngMyList[/color]
in the Source field.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Grr - itchy trigger finger.

You may also want to read up on Dynamic Named Ranges. Our own SkipVought has put together a FAQ, How can I rename a table as it changes size with info on how to do this.

But in this case, you probably won't need to worry about it because if you select and entire column ( like A:A) it will automatically expand when you add names.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Benoni2]

Hey thanks! You were a big help. Took me a while to figure out how to name a range. But I got it and then it worked great!

 

[AnotherHiggins]

Glad to help!


[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.