Cell Ref question

R

Roger

Good Afternoon,

I have a macro that is writing from one workbook to another. After it has
finished opening and writing it's initial data, it calls this macro and
writes a special cell value to the front page. Though this works fine, I want
it to be a cell reference (linked value) vs. static.

Ive tried a couple ways unsuccessfully and was wondering if you might have
some ideas on how to accomplish this.

Thank you - Roger

Sub WritetoMainPage()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
.End(xlDown).Offset(1, 0).Row

ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want
a cell ref (link) vs. static value>>>

Worksheets("sheet1").Select

End sub
Was this post helpful to you?

Why should I
 
C

Conan Kelly

Roger,

Maybe something like this would work (haven't tested it):

Sub WritetoMainPage()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
.End(xlDown).Offset(1, 0).Row

'ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value
we.Cells(irow, 12).Formula = "='" & ActiveSheet.Name & "'!H5"

Worksheets("sheet1").Select

End Sub



HTH,


Conan
 
D

Dave Peterson

You could record a macro when you do
Edit|copy
followed by Edit|paste special|paste Link

Or you could just build the formula yourself:

ws.Cells(irow, 12).Formula _
= "=" & ActiveSheet.Range("h5").Address(external:=True)
 
R

Roger

Hi Dave,

That works just terrific – Thanks.

Say, can I tap your brain one last time? How can I expand on that formula
to add nothing if cell H5 is empty. Right now it links and puts the value in
the front page, but I don’t want to show a value in the H5 has nothing in its
cell?

Thanks so much for your review and thoughts - Roger
 
D

Dave Peterson

You have a couple of choices...

Either use a different formula that makes the cell look blank if that cell is
empty:

Option Explicit
Sub WritetoMainPage()

Dim irow As Long
Dim myAddr As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
.End(xlDown).Offset(1, 0).Row

myAddr = ActiveSheet.Range("h5").Address(external:=True)
ws.Cells(irow, 12).Formula _
= "=if(" & myAddr & " = """",""""," & myAddr & ")"

ws.Select

End Sub

Or you could avoid populating the cell with any formula:

Option Explicit
Sub WritetoMainPage()

Dim irow As Long
Dim ws As Worksheet
Dim myAddr As String

Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
.End(xlDown).Offset(1, 0).Row

With ActiveSheet.Range("h5")
If IsEmpty(.Value) Then
'do nothing
'or empty that cell???
'ws.Cells(irow, 12).ClearContents
Else
myAddr = .Address(external:=True)
ws.Cells(irow, 12).Formula _
= "=if(" & myAddr & " = """",""""," & myAddr & ")"
End If
End With

ws.Select

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top