how to include a cell reference that is contained in a cell withi.

[Guest]

cell named 'gohere' contains a cell reference created by concatenation:
="$A$"&D15
(where d15 contains the count of non-blank cells.) so i end up with e.g.
$a$7. my question is how to reference the cell named 'gohere' in a macro
that will place me in $a$7. i'm only a beginning macro-ist with no vb
experience......

my ultimate purpose is to paste content from sheet 1 to the next available
cell in sheet 2

i've tried to apply similar scenarios that i found on the forum to my
situation but haven't been very successful.

thank you for any advice.

 

[Anne Troy]

Well, till someone else comes along, that's not really how they do it to my
knowledge. What they do is use xlUp. Just do a google search for it, and
you'll find lots of samples.
************
Anne Troy
www.OfficeArticles.com

 

[Nick Hodge]

I usually look from the known bottom cell up, so in your case I would use

Dim lLastRow as Long
lLastRow=Range("A65536").End(xlUp).Row

You can then use the lLastRow variable in your code, e.g

Range("A1").Copy Destination:= Range("A"&lLastRow)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Gord Dibben]

If the ultimate goal is to paste content from sheet1 to next available cell in
sheet2 in column A.............

Sub testing()
Sheets("Sheet1").Range("A1:G1").Copy _
Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thank You much! Google and You Shall Find! thanks for letting me know
what to look for though!

 

[Anne Troy]

You're welcome. I've found that's true for many things... people just don't
know the key word to use. A lot of people wonder why I created Office
Articles. Well, in them, I try to use words that people use, too, and not
just the names the software uses for a feature.
************
Anne Troy
www.OfficeArticles.com