Inserting Columns On Worksheet And Keeping Formula?
Jan 10, 2012
I have a spreadsheet that I can modify. It currently has a three product column but I need to insert 2 or 3 more columns to make 5 plus the total at the end. It also have a summary sheet. I insert the 2 columns and somehow got the formulas flowing. However when I hit the summary page its not showing results for those two new columns in the total. I am lost. I wish I could post the sheet. Its a multiple product break-even analysis exercise.
View 1 Replies
ADVERTISEMENT
Feb 13, 2008
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
View 3 Replies
View Related
Mar 12, 2012
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click()
Sheets("Original hours").Copy After:=Sheets("Original hours")
ActiveSheet.Name = "Update hours"
ActiveSheet.Unprotect "PASSWORD"
With ActiveSheet.UsedRange
.Value = .Value
Sheets("Update hours").Select
End With
End Sub
View 7 Replies
View Related
May 23, 2013
I'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies
View Related
Jul 26, 2006
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the “Convert Text to Columns Wizard”, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?
View 10 Replies
View Related
Jan 5, 2009
I'm trying to find a simple Excel template to add my sales invoices together for a year...just so I know what I've earnt...nothing complicated.
The Columns I need are;
date - invoice no. - customer - labour - materials - total
I'd like it to add together labour and materials to the total box ( or just labour in the total box if no materials for that job.)
Then each invoice adding up as I add more with subtotal of labour and materials then a grand total.
View 11 Replies
View Related
Feb 10, 2014
I maintain an excel application for a local theatre group using alot of vba actioned by buttons on worksheets. I regularly email the application back and forth between the treasurer and myself and I have noticed that the process of doing this often seems to result in the buttons losing their reference to the code I've assigned to them.
why this happens (is it a bug?) and how to make the linkage more robust? Can I link the buttons and code using vba so that I could establish all the links when the workbook is open?
View 5 Replies
View Related
Apr 7, 2009
using code to take raw data from one workbook <book1> tab <Phase1RawData> and pasting to summary workbook <book2> onto <SummarySheet>. New data is available with each Phase (14 in all) and will eventually fill all cells as indicated in the Summary Sheet Page. However raw data has in any one Phase only the columns up to the Phase its currently at. So Phase 1 will only have Phase 1 column, Phase two will have columns for Phase 1 and 2, Phase 3 will have 1,2,3 and so on (the example Book1 has two example sheets for phase 1 and phase 2 data - so each phase has a column added with the previous columns remaining.)
The problem is in having the summary sheet always show all 14 phases colmns (as in the example attached) irrespective of which Phase is being updated. So if up to Phase 3, insert the raw data available will be colmns for Phase 1, 2 and 3 - but I need ensure after that has been updated only the remaining Phase colums to 14 show. That is, if Phase 1, 2 or 3 etc are now in place, insert the remaining blank Phase columns to, and including Phase 14. Need to do this at end of each phase until 14 is met. The data up to column S is all from the raw data original sheet - Items from Column T I insert independantly as the data is transferred.
View 2 Replies
View Related
Sep 22, 2008
I have a table with one column of data. The data in this column repeats with 4 relevant pieces of information that I want to put in 4 different columns (fields) in a different spread sheet (or the same would work better and I would just delete the first column when done) keeping the same order the data is now in.
The data currently repeats in a regular pattern (i.e. 123412341234 with no other data in between). I would like to do this with a macro. Could someone help write a macro that will do this
View 9 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
Sep 1, 2008
After sorting and filtering rows with in a set range I will have several rows that are almost duplicates. This is normal and expected due to how the workbook is used. Among these rows also will be several single rows that are not duplicates. It is important that I combine any two duplicates into one row. Example:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS101300PS9
2 NameA0XX15930777PS91200PS10
3 NameX1159XXP555FBX1545PS9
4 NameB0A1234P123PS101263PS9
5 NameB1A1234P123PS90512PS10
What I need is this end result:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS91200PS10PS101300PS9
2 NameX1159XXP555FBX1545PS9
3 NameB1A1234P123PS90512PS10PS101263PS9
Its important that the data in each column stay with in that same column. Also of course it needs to be on the same row with the same person (NameA and NameB). The Columns that would determine if its a duplicate are D and E . I would need this to be preformed via macro or some easy way so that others will not have a hard time. It will be on a protected Shared Workbook with Excel 2003. I've enclosed a Sample. How can I sort these or accomplish this and maintain the data where it needs to be?
View 5 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 10, 2012
Date
#
Lname
Fname
[Code].....
With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following
Row #
143
Name:
=C143&" , "&D143&" "&E143
[Code]....
How do I write the functions to keep the columns the same, but change the row number based on the number I input?
View 3 Replies
View Related
Jan 30, 2012
I have a formula in column A, =AVERAGE(C2:Z2). I have a macro that moves columns C to Z over one column to the right every day. How do I keep the above formula the same as it is now. At present the range also moves one column. I have added $ but it still changes
View 5 Replies
View Related
Jun 28, 2014
How to express what I am trying to do in a sentence but basically I have this formula
[Code] ..........
I need B# to change automatally like it does when you copy the formula but i need the N1 and N2 to stay N1 and N2, how do i do this?
View 3 Replies
View Related
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
View Related
Feb 22, 2013
I have a spreadsheet filled with formulas that depend on a value being entered into A2, A3, A4, etc... So column A starting at A2 is where I will manually input a number and the formulas I have in columns B, C, and D will import information from another sheet based off what is put in column A. In column D the formula I have to import data
is =IFERROR(VLOOKUP(A2,Master!C:M,11,0),"").
This will import another number. Additionally in column D, I have conditional formatting that will return a red, yellow or green light based off the rule I have in place. Everything works fine, the only problem is that column D has a green light all the way down even without a value being placed in column A. I would like to find out a way to keep the cells in column D blank until a value is entered in column A. Also, if I go back and delete the value in column A, I would like the corresponding cell in column D to go back to blank as well.
View 2 Replies
View Related
Jan 25, 2010
I'm relatively new to VBA and require programming help with the following:
I have created a button to add a specific worksheet template ("TE - Template") after another worksheet ("CO - Cockpit") and then name it:
Sub Add_worksheet()
Sheets("TE - Template").Select
Sheets("TE - Template").Copy After:=Sheets("CO - Cockpit")
ActiveSheet.Name = "AL - Class 1"
End Sub
However, I would like to let Excel check (via VBA) if the "AL - Class 1" worksheet already exists. If it does, the same template sheet should be added but named differently: "AL - Class 2". This should be possible for X worksheets (i.e., "AL - Class (X + 1)" everytime I add a new template worksheet. Thus, I would like to keep the same name (i.e., "AL - Class"), but with an increasing number (i.e., 1, 2, 3, X).
how I should amend the above code or supply me with a better (and efficient) way of programming this query?
View 9 Replies
View Related
Apr 23, 2012
I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 Replies
View Related
Sep 25, 2012
what i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit
View 3 Replies
View Related
Oct 17, 2012
I have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
Copy of code is as follows:
Range("CF11:CH11").Select
Selection.Copy
Range("CJ11").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("CJ20:CL20").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
[code].....
View 5 Replies
View Related
May 5, 2014
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies
View Related
Jul 10, 2014
I have two worksheets.
1 worksheet I have a value and I need next to it the result i take from other worksheet.
LETTERS
AMOUNT
A
Result
C
Result
G
Result
[Code]...
The list goes on.
So Pretty much I need to compare the value "LETTERS" on the first worksheet with the array of letters from second worksheet and insert in the field of the Result, the value next to the correct find in the array of letters ...
View 9 Replies
View Related
Oct 24, 2011
To protect formulas and formatting, I have code, which copies a row, with formatting and formulas, from a hidden sheet, unprotect the main sheet, inserts the copied row and reprotect the main sheet. This works great, except with conditional formatting.
My current conditional formatting works on the range: $A$3:$B$100. The copied row is inserted on Row 4. When done, this splits the conditional formatting to: $A$3:$B$3 and $A$5:$B$101, whether or not the copied row contains the same conditional formatting. This makes sense, but is it possible to maintain the original, all-encompassing range? Otherwise I'll end up getting thousands of conditional formatting for each insert.
View 12 Replies
View Related
Nov 23, 2007
I m trying to sort this out myself before posting but its driving me crazy! I have the following userform :-
I would like all that data entered into a worksheet called "Purchase Record" into the following cells:-
View 4 Replies
View Related
Jul 13, 2009
I have been struggling to figure this out for several days now and i don't really know where to go from here.
I have 2 columns and i need to break them up at certain points.
The attached excel file should hopefully be clear enough to show what i mean.
The first tab is how the data starts the second tab is how i want it to look.
To clarify i need to break it after each change in the first column. ie from 1 to 2.
Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.
I think the excel file will clear up what i mean.
View 13 Replies
View Related
Sep 30, 2009
I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.
I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.
View 9 Replies
View Related
Feb 7, 2014
I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?
My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:
Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4]
Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]
I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.
View 1 Replies
View Related
Jul 3, 2008
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008 by kmayfield
'
'
Range("C3:F3").Select
ActiveCell.FormulaR1C1 = "Jan-2008"
Range("G3:J3").Select
ActiveCell.FormulaR1C1 = "Feb-2008"
Range("K3:N3").Select
ActiveCell.FormulaR1C1 = "Mar-2008"
Range("O3:R3").Select
ActiveCell.FormulaR1C1 = "Apr-2008"
Range("S3:V3").Select
ActiveCell.FormulaR1C1 = "May-2008"
Range("W3:Z3").Select
View 9 Replies
View Related
Nov 9, 2009
I'm trying to insert a worksheet change event using VBA. I have this sample code from here -
http://www.cpearson.com/Excel/vbe.aspx
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule...................
View 9 Replies
View Related
