Macro To Copy Formula While Keeping Original Cell References
Feb 17, 2009
I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.
E.g.
If the formula on Sheet1 is:
= sum(A1:B6)
then the copied formula on Sheet2 would read
=sum(Sheet1!A1:Sheet1!B6)
You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.
I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.
View 9 Replies
ADVERTISEMENT
Sep 1, 2009
I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations. So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?
View 2 Replies
View Related
Nov 20, 2012
Wanted to know if there is a macro that can copy the selection of cells and paste it as a formula with original cell refernce.
For Example :
Copy Selection Cells - Say Cells A1 B1 & C1
and Paste It as formula In Cell D1 as =A1+B1+C1
View 7 Replies
View Related
Feb 18, 2014
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
View 5 Replies
View Related
Feb 18, 2014
I want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.
View 3 Replies
View Related
Mar 4, 2014
I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.
View 1 Replies
View Related
Mar 5, 2014
I have one sheet with all the formulas for the entire workbook and would like to copy and paste the formulas from Sheet 1 to Sheet 2...Sheet 1 to Sheet 3 etc. without changing the cell referening in the original formula. I am not too sure how .formula works.
Sub CopyAndPaste()
' To copy formulas from Summary sheet to their respective sheets
With Worksheets("Summary")
.Range("R3").Copy Worksheets("2").Range("X3").Formula = Worksheets("Summary").Range("R3")
End With
End Sub
View 7 Replies
View Related
Apr 15, 2014
I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.
My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV
My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.
View 3 Replies
View Related
Dec 3, 2012
I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
View 12 Replies
View Related
May 29, 2007
I am having a few problems with dynamic named range in excel 2000.
When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.
I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.
View 10 Replies
View Related
Oct 25, 2007
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll()
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
End If
Next i
End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
View 3 Replies
View Related
Sep 5, 2012
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
View 4 Replies
View Related
Apr 15, 2014
At my work we are using Excel as a CMS to produce SQL scripts for insertion into a database. The data is then turned into mobile web pages. This involves multiple workbooks - 1 for each main page with the sub-pages as extra worksheets in the book.
Much of the data is duplicated so we have created master workbooks then copied them and laboriously referenced the relevant cells back to the original. That way if any data in the master is changed, all copies will change simultaneously.
This is obviously not the most efficient way of publishing web pages but we are stuck with it for now. So I am wondering if it is possible to write a VBA code to create a copy of the master and then reference all required tables in the worksheets automatically? The tables are all named ranges.
View 9 Replies
View Related
May 13, 2009
For example things like "=1+A1" or "=A1*B1"
Right now it changes the cells even with Paste Special!
View 8 Replies
View Related
Feb 1, 2013
I have 4 sheets name A, B, C and D.
The D worksheet is a summary sheet that pulls in data from A, B and C.
e.g.
=A!A4
=A!C4
There are approx 50 of these references to Cell A. Is there an easy way to copy the same formulas but reference Sheet B and C without having to retype them all?
View 1 Replies
View Related
Nov 5, 2009
I have a workbook with 7 sheets. Each sheet has about 9 different macro buttons that are repeated all over the sheet in various locations. I want to return automatically to the sheet and cell that was last active before running a macro.
View 2 Replies
View Related
Dec 3, 2007
I have a workbook where I have columns requiring entry from A to AD. I have set conditional formatting in column A to show a colour if there is an entry in column AD. I would like to use a macro to always freeze panes in column D (to show the data in the first 3 columns) and the macro would then show column AD in the other frozen pane/split.
As there will always be an entry in column A but not always in column AD I want to be able to select any highlighted row in column A , run the macro and the cursor will be showing in column AD on the same row ( relative reference?) thus showing the entry in AD next to the other info in columns A, B and C.
At the moment I have a macro that unfreezes my header row and is set to always choose the "next empty cell" but I don't know the code to change it to get what I want. Currently if I select any entry in column A e.g. row 100 and run the macro I find the cursor showing in AD2! Of course I would like to run another macro to return back to A on the same row too.....
View 9 Replies
View Related
May 4, 2014
I began to ponder a way to copy down cells so that the copying of the formulas results in references as shifting horizontally instead of vertically. One particular reason that this occurred to me had to do with my attempting to use Excel to make more orderly text copied from Adobe.
So, for example, if I copied text from Adobe, I would paste it in Excel. Thirty lines of text would past vertically into a column, from Row A1 to Row A30, with each line of text in its own row. I wanted to figure out a way so that in adjacent columns, I could put it so that I would have a set of formulas reading in =A1 in Column C cell/row 1, =A2 in Column D cell 1, =A3 in Column E cell/row 1, =A4 in Column F cell/row 1, =A5 in Column G cell/row 1, and so forth. I realized that if I copied this down, in the second row, the result would entail references to A2, A3, A4, A5, A6. I would prefer that the references update to A6, A7, A8, A9, and A10.
View 5 Replies
View Related
Nov 23, 2009
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
View 2 Replies
View Related
Dec 22, 2009
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
View 9 Replies
View Related
Jan 12, 2009
I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...).
How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month.
Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.)
An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same.
=SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)
View 4 Replies
View Related
Apr 29, 2014
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
View 2 Replies
View Related
Jun 15, 2014
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
View 2 Replies
View Related
Feb 23, 2008
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View 9 Replies
View Related
Aug 8, 2007
I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays.
I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA$502': I would like to store the path , file name and sheetname in variables/arrays as well.
Any pointers as to how I should approach this problem?
View 7 Replies
View Related
Sep 6, 2009
Is there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet?
View 9 Replies
View Related
May 18, 2009
I have a number of Cell names LR0Cost, EX0Cost, IM0cost that hold %
formula might be
View 4 Replies
View Related
Mar 12, 2012
can Excel do a double if formula by looking at cells and not fixed type info.
EG: I have a % achieved and $ bonus to pay: however the % achieved & the $ bonus to pay information in the cell will change each month
So... I want the formula to say
=IF (look for cell A1 (which has %achieved) and if found use A2 ($bonus) to give me an answer... otherwise false
The second dillema I have is that A1 has 3 ranges from 100-150, 150-200 & 200-300 to calculate with a set % in A2
View 8 Replies
View Related
Mar 26, 2013
I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?
View 1 Replies
View Related
Jul 2, 2008
I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.
For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"
Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).
I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.
View 9 Replies
View Related
