Replicate cells with INDIRECT Formulas

Source sheet is Sheet2

Select an area on a new sheet and run macro

Delete the rows you do not want, because the INDIRECT worksheet formula is used and the cell address is within double quotes, the formulas will not adjust addresses due to insertions/deletions on the source worksheet.

Sample Forumulas   displayed using:    =personal.xls!GetFormulaD(A1)

A1:  =IF(INDIRECT("'sheet2'!A1")="","",INDIRECT("'sheet2'!A1"))
A2:  =IF(INDIRECT("'sheet2'!A2")="","",INDIRECT("'sheet2'!A2"))
A3:  =IF(INDIRECT("'sheet2'!A9")="","",INDIRECT("'sheet2'!A9"))
A4:  =IF(INDIRECT("'sheet2'!A10")="","",INDIRECT("'sheet2'!A10"))
A5:  =IF(INDIRECT("'sheet2'!A11")="","",INDIRECT("'sheet2'!A11"))

Option Explicit
Sub Replicate_from()
   'David McRitchie, 2006-03-04,
  '   http://www.mvps.org/dmcritchie/excel/replicate.htm
  Dim Other_sheet As String, This_sheet As String
  Dim This_range As String
  Dim inner  As String
  Dim cell As Range
  Other_sheet = "sheetx"   '-- will use if it exists
retry:
  On Error Resume Next
  If Len(Worksheets(Other_sheet).Name) < 1 Then
     If LCase(Other_sheet) = "sheetx" Then Other_sheet = "sheet2"
     On Error GoTo 0   '-- sheet2 is a suggestion
     Other_sheet = Application.InputBox("supply name " _
        & "of input worksheet", "Name of input worksheet", _
        Other_sheet, , , , , 2)
     If Other_sheet = "" Or Other_sheet = "False" Then
        MsgBox "cancelled by your command -- " & Other_sheet
        Exit Sub
     End If
     GoTo retry
  End If
  On Error GoTo 0  
  This_sheet = ActiveSheet.Name
  This_range = Selection.Address(0, 0)
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Sheets(Other_sheet).Range(This_range).Copy
  Sheets(This_sheet).Activate
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  On Error Resume Next
  For Each cell In Selection
     inner = "INDIRECT(""'" & Other_sheet _
         & "'!" & cell.Address(0, 0) & """)"
     cell.Formula = "=IF(" & inner & "="""",""""," & inner & ")"
  Next cell
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Related   (#related)

• Show FORMULA or FORMAT of another cell (formula.htm), the user defined function GetFormulaD was used on this page.
• INDIRECT Worksheet Function (indirect.htm)
• Rearranging Data in Columns (join.htm)
• Using OFFSET to maintain formulas when inserting lines (offset.htm)
• Worksheets in VBA Coding and in Worksheet Formulas (sheets.htm)
• Slow Response, Memory Problems, and Speeding up Excel (slowresp.htm)
• Summarizing Data Examples (an Overview) (sumdata.htm)
• Tips and Techniques, Excel (contextures.com),  Filtering may be a solution for many similar requests.  Also see video on Filtering (other videos on site are membership) at datapigtechnologies.com)