CODEWITHSUNDEEP
excelvba
Fritz
Fritz

Reputation: 1

Using VBA to copy a Reference to a Cell from Sheet 2 to Sheet 1

Anyone

I am new to Visual Basic Excel and would appreciate help with a particular problem. I have been reading reference books and searching Google to find a solution but I have not been successful. I have completed all of the other project requirements except for this one.

I have a workbook that has 48 separate sheets and depending on which detail sheet is referenced, I need to provide information to the Summary Sheet. I am attempting to Copy only the Reference to Cells from a particular Detail Sheet to Summary Sheet. For example I am trying to have the following in Cell(15,5) on the Summary Sheet ='Detail 1'!E10 The purpose is to reference Cell(10,5) on Sheet Detail 1 so the value in the Summary Sheet updates whenever Cell(10,5) on the Detail 1 sheet changes.

I tried this code but it does not work among a bunch of other attempts. It copies the formula but not the reference.

As I said before, I am still learning so, if what I show below is totally rookie code, I will suitably be embarrassed. Thank You in advance for any help.

'-----This is simplified Version of the code in a WorkBook with 45 sheets-----------

Sub CopyReference()

Dim SheetCounter As Integer

Dim RowNumber As Integer

Dim ColumnCounter As Integer


RowNumber = 10

ColumnCounter = 5

SheetCounter = 2   '------------This is the Detail 1 Sheet in the WorkBook-------------


Sheets(SheetCounter).Select

ActiveSheet.Range(Cells(RowNumber, ColumnCounter), Cells(RowNumber, ColumnCounter)).Copy

Sheets(1).Select                   '------- This is the Summary Sheet

ActiveSheet.Range(Cells(RowNumber + 5, ColumnCounter), Cells(RowNumber + 5, ColumnCounter)).PasteSpecial Operation:=xlPasteSpecialOperationNone

End Sub

Upvotes: 0

Views: 2799

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

This seems like what you need

ThisWorkbook.Sheets(1).Cells(15, 5).Formula = "=" & _
     ThisWorkbook.Sheets(2).Cells(10, 5).Address(False, False, , True)

Upvotes: 1

Related Questions