[RESOLVED] Simple Question :)

[billhuard]

Is it possible to link an entire row from a worksheet to another in Excel??
If so, how???

[Webtest]

Yes, it is possible to "link an entire row from a worksheet to another in Excel".

Do you want a Macro to do this ... manually or when the sheet opens? Or do you want a formula for the "other sheet" to put in all 256 cells of the target row to reference the source row?

[Webtest]

Here is a sample formula to put in all the cells in the "linked row" on the destination sheet ...

=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

where:
Sheet1 is the source sheet name
A1 is the cell reference for the cell you are loading (I started with A1)

You can load all these cells with the appropriate formula manually or programmatically. You just need to decide when and how to start the program.

[DKenny]

Or...
1/ Select the row on the original sheet.
2/ Copy the row
3/ On the destination sheet -->Paste Special -->Paste Link

[Webtest]

Declan ... It took me a minute to find the "Paste Link" button! It is not in the list of radio buttons, but at the bottom of the popup.

One nice thing about the forumula I provided is that it doesn't write "0"s on the destination sheet in place of blanks on the source sheet.

[DKenny]

Yea, the paste link is not very intuitive.

On the zeros, one can always set the display option to hide zero values.

Either way this is very straightforward.

[billhuard]

thanks a lot guys,

I wanted to do it with a macro

The record macro option helped me..
And hell yeah the paste link button isn't intuitive.

Thanks again to both of you

[Webtest]

Bill ... the "Record Macro" won't allow you to programmatically select the Row on the Source sheet or on the Destination sheet ... do you want to know how to do that? I'm sure Declan and I will both race to get your solution first! Right, Declan?

[DKenny]

Giddy up!!

[billhuard]

On your mark
Let's get ready
Go!!!!

I need it by a macro.
By recording the macro the recorder gave me the following

Rows("32:32").Select
    Selection.Copy
    Sheets("Rep. Total").Select
    Range("A20").Select
    ActiveSheet.Paste Link:=True

[billhuard]

Seems like I got all what I needed because the paste special accomplished what I wanted it to do.

thanks again for your precious help

[Webtest]

Option Explicit
Sub Macro1()
    Dim srcSheet As Worksheet
    Dim dstSheet As Worksheet
    Dim srcRow As Long
    Dim dstRow As Long
    Dim srcShtName As String
    Dim dstShtName As String
    Dim i As Integer
    Dim tStr As String
    
    'Set the source sheet name and row HERE
    srcShtName = "Sheet1"
    'Which row on the source sheet do you want to link?
    srcRow = 1
    
    'Set the target sheet name and row HERE
    dstShtName = "Sheet2"
    'Which row on the destination sheet do you want to link?
    dstRow = 1
    
    'Set the source and destination sheet references
    Set srcSheet = Sheets(srcShtName)
    Set dstSheet = Sheets(dstShtName)
    
    'Load all of the cells by iterating through the columns
    For i = 1 To 256
        'Set up the source cell reference
        tStr = srcShtName & "!" & srcSheet.Cells(srcRow, i).Address
        'Load the completed formula
        dstSheet.Cells(dstRow, i).Formula = "=IF(ISBLANK(" & tStr & "),""""," & tStr & ")"
    Next i

    'Clean house
    Set srcSheet = Nothing
    Set dstSheet = Nothing

End Sub

Oh what fun! ... by the way, quality counts! The one with the most comments wins!

[DKenny]

(a la Jon Stewart) Damn you, Ten o'Clock conference call!!

I now cede the field to the victor.