Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy over formula to specific cell
The code that I have from Ron de Bruin to copy data from one workbook to another, and it works well. However I have one column (Col I) that I need the actual formula copied over. As it stands now, it works out the calculation and places the result in the cell, what I want is the copy command to paste the actual formula. This way the user can change something in the destination workbook and still have to formula to recalculate a result. I know I could add a line like Code: -------------------- destrange.PasteSpecial xlPasteFormulas, , False, False -------------------- but this copies over all formulas when I only want one specific one. Here is the copy and paste code Code: -------------------- Sub sendtotracking() Dim smallrng As Range Dim destrange As Range Dim destWB As Workbook Dim Lr As Long Application.ScreenUpdating = False If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls") Else Set destWB = Workbooks.Open("O:\PWM_Shared_Files\Stations Estimates\Estimate Tracking Sheet\P&WM Estimate Tracking Sheet.xls") End If Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1 Set SourceRange = ThisWorkbook.Worksheets("Links").Range("A1:X1") Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr) SourceRange.Copy destrange.PasteSpecial xlPasteValues, , False, False Application.CutCopyMode = False Application.ScreenUpdating = True ThisWorkbook.Worksheets("Input Form").Range("G43").Value = "a" ThisWorkbook.Worksheets("Input Form").Range("H43").Value = Now() End Sub -------------------- Thanks -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=565123 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy over formula to specific cell
This is the easiest way I can think of:
Sub sendtotracking() Dim smallrng As Range Dim destrange As Range Dim destWB As Workbook Dim Lr As Long Application.ScreenUpdating = False If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls") Else Set destWB = Workbooks.Open("O:\PWM_Shared_Files\Stations Estimates\Estimate Tracking Sheet\P&WM Estimate Tracking Sheet.xls") End If Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1 Set SourceRange = ThisWorkbook.Worksheets("Links").Range("A1:X1") Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr) SourceRange.Copy destrange.PasteSpecial xlPasteValues, , False, False Set SourceRange = ThisWorkbook.Worksheets("Links").Range("I1") Set destrange = destWB.Worksheets("Tracking Sheet").Range("I" & Lr) SourceRange.Copy destrange.PasteSpecial xlPasteFormulas, , False, False Application.CutCopyMode = False Application.ScreenUpdating = True ThisWorkbook.Worksheets("Input Form").Range("G43").Value = "a" ThisWorkbook.Worksheets("Input Form").Range("H43").Value = Now() End Sub Basically after you copy everything you go back and overwrite Column "I" with the formula HTH Die_Another_Day tanyhart wrote: The code that I have from Ron de Bruin to copy data from one workbook to another, and it works well. However I have one column (Col I) that I need the actual formula copied over. As it stands now, it works out the calculation and places the result in the cell, what I want is the copy command to paste the actual formula. This way the user can change something in the destination workbook and still have to formula to recalculate a result. I know I could add a line like Code: -------------------- destrange.PasteSpecial xlPasteFormulas, , False, False -------------------- but this copies over all formulas when I only want one specific one. Here is the copy and paste code Code: -------------------- Sub sendtotracking() Dim smallrng As Range Dim destrange As Range Dim destWB As Workbook Dim Lr As Long Application.ScreenUpdating = False If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls") Else Set destWB = Workbooks.Open("O:\PWM_Shared_Files\Stations Estimates\Estimate Tracking Sheet\P&WM Estimate Tracking Sheet.xls") End If Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1 Set SourceRange = ThisWorkbook.Worksheets("Links").Range("A1:X1") Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr) SourceRange.Copy destrange.PasteSpecial xlPasteValues, , False, False Application.CutCopyMode = False Application.ScreenUpdating = True ThisWorkbook.Worksheets("Input Form").Range("G43").Value = "a" ThisWorkbook.Worksheets("Input Form").Range("H43").Value = Now() End Sub -------------------- Thanks -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=565123 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy over formula to specific cell
Works like a charm, Thanks. -- tanyhart ------------------------------------------------------------------------ tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148 View this thread: http://www.excelforum.com/showthread...hreadid=565123 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy specific cell using Multiple criteria | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
copy text to specific cell | Excel Discussion (Misc queries) | |||
Copy text to specific cell. | Excel Programming |