Use Macro to Copy Formula from one Workbook to another

  • Thread starter Thread starter bevchapman
  • Start date Start date
B

bevchapman

I am trying to copy and paste a block of cells from one workbook to
another workbook. The original file is formulas and the destination file
cells need to have the same same formula.

any suggestions on how to add this into a macro?
 
What happened to your previous post??

Dim ws1 as Worksheet, ws2 as Worksheet

'Saved workbook will have extension like Workbooks("Book1.xls")
Set ws1 = Workbooks("Book1").Sheets("Sheet1")
Set ws2 = Workbooks("Book2").Sheets("Sheet1")

ws1.Range("A1:B10").Copy
ws2.Range("A1").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False

If this post helps click Yes
 
I understand the first part and can get it to work as one Workbook will
always be the same name. When I try to paste the formulas is where I am
getting hung up.
The workbook that the formula needs to be copied to will change. I have 60
plus files to copy these formulas to. How can I make it be like and
ActiveWorkbook of something like that
 
Try the below which will copy the range A1:B10 of formulas to the 1st sheet
of all the other workbooks which are opened...Is this what you are looking
for ?

ActiveSheet.Range("E1:E6").Copy
For Each book In Workbooks
If book.Name <> ActiveWorkbook.Name Then _
book.Sheets(1).Range("E1").PasteSpecial Paste:=xlPasteFormulas
Next
Application.CutCopyMode = False

If this post helps click Yes
 
I am getting hung up on the this line:
book.Sheets(1).Range("E1").PasteSpecial Paste:=xlPasteFormulas

Should I be changing a name or something somewhere to match my data?
 
Back
Top