Help! How Do I Copy A Range to Workbook but keep destination cell formulas

[Smith369]

I am trying to copy a range across workbooks however I have formulas in the destination workbook that when I copy range across get deleted below is my code how would I implement the "PasteSpecial xlPasteValues" ? thanks very much for your time.

Code:

Sub ShowAll
Dim mydata As String


mydata = "='C:\Test\[Quick Test.xlsm]Companies'!$A$2:$W$79"


'link to worksheet
With ThisWorkbook.Worksheets(1).Range("A5:W79")
.Formula = mydata
'convert formula to text
.Value = .Value

End With
End Sub

<strike></strike>

 

[yky]

??? Not sure what you are asking. If the destination formula gives a value of 3 and you paste 1 to the cell, what do you want Excel to do? Keeping 3 or keeping 1? As it is now, Excel will paste one to the cell, thus, erasing the formula.

 

[Smith369]

Apologies basically the cells that i want to paste the range into have formulas to such as in cell B5 [=A5-sum(C5:F5)] i want to copy the values into these cells but leave the formulas so that the formulas work with the new values pasted in ?

Hopefully that is slightly clearer ?

I have a workbook with a range of values (copying from)

A workbook that has formulas in cells (pasting to)

I wish for the formulas to remain in the cells after the values have been pasted in .

 

[GTO]

...such as in cell B5 [=A5-sum(C5:F5)] i want to copy the values into these cells but leave the formulas so that the formulas work with the new values pasted in ?

Hopefully that is slightly clearer ?...

At least to me, I am afraid it remains unclear. In a given cell, you can have a value or a formula, not both. Can you describe in a single-cell context, what you are trying to do?

Mark

 

[Smith369]

Apologies , Basically within the range i am copying A5:W79 , 2 columns are Remaining Fee (Column H) Invoiced To Date (Column G) , how do i stop those from being copied across so that the formulas in the new workbook do not get removed ?

 

[GTO]

Hopefully I am understanding better. Would it be?

Sub ShowAll()
Dim mydata As String
  
   'mydata = "='C:\Test\[Quick Test.xlsm]Companies'!$A$2:$W$79"
   mydata = "='C:\Test\[Quick Test.xlsm]Companies'!$A5:$F$79"
  With ThisWorkbook.Worksheets(1).Range("A5:F79")
    .Formula = mydata
    .Value = .Value
  End With
  mydata = "='C:\Test\[Quick Test.xlsm]Companies'!$I$5:$W$79"
  With ThisWorkbook.Worksheets(1).Range("I5:W79")
    .Formula = mydata
    .Value = .Value
  End With
End Sub

Please note I think A2 should be A5 where listed in red.

 

[Smith369]

GTO , thanks very much thats perfect can i ask where in this code you tell it to not include column G & H ? , because i have a macro to clear all the worksheet :

Sub ClearAll()


Range("A5:w79").ClearContents


End Sub

Which I need to alter to correlate , thanks very much again really helpful

 

[yky]

The columns to copy are A to F and I to W, columns G and H are not included.

[COLOR=darkblue]Sub[/COLOR][COLOR=#333333] ShowAll()[/COLOR][COLOR=darkblue]Dim[/COLOR] mydata [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  
   'mydata = "='C:\Test\[Quick Test.xlsm]Companies'!$A$[COLOR=#ff0000][B]2[/B][/COLOR]:$W$79"
   mydata = "='C:\Test\[Quick Test.xlsm]Companies'![B][COLOR=#ff0000]$A5:$F$79[/COLOR][/B]"
  [COLOR=darkblue]With[/COLOR] ThisWorkbook.Worksheets(1).Range("A5:F79")
    .Formula = mydata
    .Value = .Value
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With
[/COLOR]  mydata = "='C:\Test\[Quick Test.xlsm]Companies'![B][COLOR=#ff0000]$I$5:$W$79[/COLOR][/B]"
  [COLOR=darkblue]With[/COLOR] ThisWorkbook.Worksheets(1).Range("I5:W79")
    .Formula = mydata
    .Value = .Value
  [COLOR=darkblue]End[/COLOR] With [COLOR=#333333]End [/COLOR][COLOR=darkblue]Sub[/COLOR]

 

[Smith369]

Oh sorry after reading through code again can see the simple change , thank you both very much for all the help.