Copying Last Row Then Inserting It Screws Up Part Of Formula?
Jan 12, 2014
I have a spreadsheet serving as a simple document register. Column A is a unique document reference number, which is a simple concatenation of some text which prefixes all of the document numbers, plus the date in "yyymmdd" format (from column B) and a 3-digit sequence number (from column C).
Column B has no formula - my macro just pastes a timestamp in there.
Column C is a sequence number field which has a formula comparing the "yyymmdd" part of the timestamp with the cell directly above it. If they are different then the sequence number becomes "001" - if they are the same then the sequence number is incremented by 1, so all docs created on the same day have an incremental sequence number.
Here's the problem. When I want to add a new document I need to copy the last row down to the next row (in preparation for the next document to be created by my system) and there are two ways I've been doing this - one which works and one which doesn't.
This works > Say my last row (with the hidden formulae) is row 13. I select A13:C13 plus the three blank cells directly below (A14:C14) and do a Ctrl+D to copy them down. All is well.
But the following doesn't work - why? > I select the entire row 13, copy it, then reinsert it at row 13 so the formulae in row 13 get shifted down to row 14. What goes wrong here is that my formula in column C which WAS comparing the date in B13 to the one in B12 NOW compares B13 to B11
I've attached an example spreadsheet to try and demonstrate the problem. Sheet 1 is the original sheet. Sheet 2 is the "copying down" method which works, and Sheet 3 shows what happens when you copy the entire row 13 and "Insert copied cells".
If I want to change only A7 to A8, A9, A10 without changing the other parts of the formula, how do I do that. I need to copy this formula in about 300 rows.
I am trying to work out a method which will allow me to drag the forumula which is in cell B13 from B13:B17, and have in increase the date serial number by one for each row by which it descends.
In other words I want to drag the formula down to, say B14, and have it increase the date serial to = that which is in cell A14.
[the obvious benefit of being able to do this is that I don't have to manually adjust the date serial in each cell on each new row]
I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.
This Code is Pasting "A1" into the destination - format and all. I need it to only paste in the value.
Sub test() Dim lastrow As Long lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet2").Range("A1").Copy Destination:=Sheets("Sheet1").Range("A" & lastrow) Application.CutCopyMode = False End Sub
I have a spread sheet that I have locked with the expection of certain cells. I also have left the ability to insert and delete rows. The cells that are locked have functions in them run in sequance.
Is there a way that when the new row is inserted its copies the formula from the row above automatically but also adjust for the new row and adjusts the rows below it automatically?
For example if I insert a new row between rows 2 and 3 below. (orginal layout) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (locked)(function is =sum(a3:b3) a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
(layout after inserting row) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (unlocked) *inserted row* a4 (unlocked) b4 (locked)(function is =sum(a4:b4) a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
this is what i want the end product to be. (layout if formulas are copied and adjusted after inserting row) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (locked)(function is =sum(a3:b3) *inserted row* a4 (unlocked) b4 (locked)(function is =sum(a4:b4) a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
How to copy part of a cell to a new cell when there are no spaces or specific features to use in the formula. My cell contains a data and time as such: 2013-10-22T14:05:34Z
My hope is to keep the data 2013-10-22 in one column and transfer the 14:05:34 to a new column...if the 'T' and 'Z'
I have a lot of titles like "A Company - Strategy A", "A Company - Strategy B" and what I am hoping to do is strip out the part after the hyphen to the cell directly above. This is the case across about 200 columns, I'd imagine this is pretty easy?
I have generated some dates that appear in column a. I want to make a macro that copies lines from sheet 2 and inserts them between the appropriate dates in column a sheet 1.
eg: A 1/1/01 23/1/01 4/2/01 5/2/01 7/2/01
sheet2: 2/1/01 23/1/01 6/2/01
outcome: A 1/1/01 2/1/01 23/1/01 23/1/01 4/2/01 5/2/01 6/2/01 7/2/01
the lines will also have 2 other fields for copying (description and amount). I will also need to copy formulas to the inserted rows in some columns (eg balance) but not all (not the date column).
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
On sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
I'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1 Test 2 32, 343, 63, 34 email2 Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1 Test 2 32 email2 Test 2 343 email2 Test 2 63 email2 Test 2 34 email2 Test 3 4934 email3 Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code: Option Explicit Sub Macro1() Dim fromCol As String Dim toCol As String Dim fromRow As String Dim toRow As String Dim inVal As String Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
I have just generated a report of current customer numbers for our company. Unfortunately it reports it as "162 (162)". No idea why the program does it like that because it now makes using VLOOKUP very difficult.
What I want to do is copy the "162" part of the cell and paste it into the adjacent cell to make it easier to manipulate the worksheet. All of the customer numbers are in column D if that makes a difference.
I have a data set which is structured such that there are variable numbers of products (column A) from 1 - 48 and these repeat multiple times for each "Name" held in column B. I have attached an example of this which uses a data set with 7 entries reapeating 7 times.
I need excel to insert 2 formula for me multiple times which needs to varry according as follows
1. Calculate the average value of data in Column C - G for n cells starting at a specific cell (C2), I have inserted an example of this and highlighted it in yellow. This then needs to repeat down column C several times, the number of times this will repeat depends upon the number of different names held in column B. But I have this calculated already and stored in a cell im my main document.
2. Calculate the variance of each value in the x cells above from the average calculated in point 1 above. I have highlighted this also in yellow.
When inserting a row in lets say row 5, i want the formula to be kept for that row. (e5) Table doesnt seems to work. Any macro for this, and no button but automatically.?
I'm trying to insert a formula into cell "A8" when cell "A1" changes. These formula gives me (into cell "A8") the next week day, depending on the value of cell "A1". If A1="FRIDAY", "A8" will be 08-11-2013.
The problem is that an error ocurred (Run-time error '1004'). If I put the formula directly into cell it works...
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A1") Then ActiveWorkbook.Worksheets("Sheet1").Range("A8").Formula = "=TODAY()+8-WEEKDAY(TODAY()-MATCH(A1;{""MONDAY"";""TUESDAY"";""WEDNESDAY"";""THURSDAY"";""FRIDAY""};0))" End If End Sub
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: , Groceries General Household Cleaning Education/Lessons Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either SD1, SD4, BDO, BD (not neccessarily in Caps) and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1 or BDO xyz. when macro runs G2 returns BDO
This IF formula does not work on the last part of this formula IF(E10>50,"$50.00",0)))) and I think there is a conflict with the IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
I have a simple time sheet that I want to insert weekly dates by the date I have. I don't know the formula for that. I've attached the time sheet. TimeSheet2.xls
I have some formula's that look at a range of rows
[Code] .....
The trouble is I insert rows to add new data to my sheet, this then changes the formula. $A$9 then becomes $A$10, I don't want this. I want the start of the formula to stay the same. How do I do this?
I'm trying to come up with a formula that allows me to insert the date of the previous trading day. Because for several reasons I can't create a column array of manually entered holidays, I'm trying to find a formula that would produce this. I've tried this, but excel doesn't like it.
