Copying formulas, not links or just data

**Storm08** · 01-27-2012, 04:25 AM

Hi all,

Title seems quite simple, pretty much because it is :P

Ive got this code (kindly figured out by snb):

Sub Update_Click()

For Each tb In Sheet1.CheckBoxes
   With tb.TopLeftCell
     If tb = 1 Then
     
        If .Row = 1 Then Sheets("Sheet2").Cells(1, .Column).Resize(Sheet2.UsedRange.Rows.Count) = Sheet1.UsedRange.Columns(.Column).Value
        If .Column = 1 Then Sheets("Sheet2").Cells(.Column, 1).Resize(Sheet2.UsedRange.Columns.Count) = Sheet1.UsedRange.Rows(.Row).Value
        
     Else
     
        If .Row = 1 Then Sheets("Sheet2").Columns(.Column).ClearContents
        If .Column = 1 Then Sheets("Sheet2").Rows(.Row).ClearContents
    End If
   End With
  Next
End Sub

And i would like to copy the formula in the cells as well as the data. so then the user can use the new spreadsheet as a stand alone sheet, with out the data being linked to the other sheet.

Any help would be very much appreciated

Thanks,
Storm08

**JonathanMoore** · 01-27-2012, 04:30 AM

Hi,

You need to use the paste-special command - something along these lines:

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Just use that code on the cell you're copying and it will only paste the formula,

Does that help?

- Jon

**Storm08** · 01-27-2012, 05:03 AM

hello

That is somewhat helpful but how would I incorporate that into this line of code?:

If .Row = 1 Then Sheets("Sheet2").Cells(1, .Column).Resize(Sheet2.UsedRange.Rows.Count) = Sheet1.UsedRange.Columns(.Column).Value

**snb** · 01-27-2012, 05:32 AM

Sub Update_Click()

For Each tb In Sheet1.CheckBoxes
   With tb.TopLeftCell
     If tb = 1 Then
     
        If .Row = 1 Then Sheets("Sheet2").Cells(1, .Column).Resize(Sheet2.UsedRange.Rows.Count).formula = Sheet1.UsedRange.Columns(.Column).formula
        If .Column = 1 Then Sheets("Sheet2").Cells(.Column, 1).Resize(Sheet2.UsedRange.Columns.Count).formula = Sheet1.UsedRange.Rows(.Row).Formula
        
     Else
     
        If .Row = 1 Then Sheets("Sheet2").Columns(.Column).ClearContents
        If .Column = 1 Then Sheets("Sheet2").Rows(.Row).ClearContents
    End If
   End With
  Next
End Sub