I have in the code copied a range of cells.
Now i want to make sure that the same number of rows are avalible
In case that the i want to create one new row i use the following code
ActiveCell.EntireRow.Insert
The problem is that insted of inserting one black row, it wants to paste the rows which are already copied.
How do i insert one black row
I have a macro which inserts a row and copies formulas and formatting to the inserted row or rows, but leaves other fields blank. What would be great would be if I could force users to use this macro for inserting rows and prevent them from using the normal 'insert row' function.
View 9 Replies View RelatedI`m making a list of gas and electricity readings and want to show the difference between the last reading such as =h5-h4 and then the next to show =h6-h5 and so on. How do I copy this formula for subsequent rows or columns.
View 4 Replies View RelatedIs there a macro that will insert a sum formula on the last row of each category?
For example:
A 10.00
A 5.00
A 7.00
22.00
B 12.00
B 25.00
B 18.00
[Code]...
I know that I can insert a row over two worksheets by selecting both sheets and inserting a row. However, I have to make a worksheet usable by people who don't know excel at all, and if it isn't user friendly, they just don't try!
Therefore, is there any way I can set up my worksheet so that if a row is entered in sheet 2, a new row is inserted in the same place in sheet3?
I'm wanting to add a button on the last row of a worksheet to "add rows" above the buttion (entire row). I have some cells (6 total) that have a formula that needs to carry over to the newly created rows. Some cells remain blank but some cells need to have the formulas fill down to the new rows (i.e.: =sum(a2+1), =sum(a3+1)...
Is it possible to give option of choosing how many rows someone wants to insert?
Help in creating the button, option to choose # of rows and the macro to insert those rows.
I have a question about selecting an entire row, and then inserting a new row. The following code will select, and then insert a new row at row 30:
View 2 Replies View RelatedIm trying to inset
View 2 Replies View Relatedhttp://img177.imageshack.us/img177/2797/exceluf7.th.jpg
See above spreadsheet (some data has been blanked out for data protection)
I have column L formatted as a validation list with two options 'Open' and 'Closed'. What I want to happen, is when I select closed from the list, the "Date Closed" cell to the right (column m) changes to the date that Closed was entered, and does not change thereafter.
As you can see in the pic, all the close out dates are todays date. . . I need it to store the date it was closed, and not constantly update it.
I am using Ron De Bruin's code for sending emails as below, where it says "your monthly totals for", I want it to insert last months month. ie. if i send the mail in March it should automatically insert February in the field
RDB_Mail_PDF_Outlook Filename, "ron@debruin.nl", "This is the subject", _
"your monthly totals for "March" " _
& vbNewLine & vbNewLine & "Regards Ron de bruin", False
I have some VBA code which inserts a new row at the bottom of my data and copies the formulas down from the row above. This all works fine. However I now have the need to insert a new row within the data into the correct group.
So within the data there are 4 product groups (sorted).
So column A looks something like this (but much bigger):
Product1
Product1
Product1
Product2
Product3
Product3
Product4
Product4
Product4
I want the user to be able to select "Product1" from a drop down box (which I've set up). Then click the button called "New_Data"
So "New_Data" runs the code and inserts a new row at the bottom of all the other Product 1's (in this example it would be between row's 3 and 4).
Here is my existing code for just inserting a line at the end:
With Sheets("Sheet1")
.Unprotect
With .Range("A1").End(xlDown)
[Code]....
I'm trying to insert a set of strings, and for some reason its not going to the next open row, its just inserting them over the last record.
Code:
Private Sub Insert_Payment_Click()Dim Payment As String, PaymentAddress As String
Worksheets("Payments").Select
Payment = Range("E4")
PaymentAddress = Range("B4")
Worksheets("RentalDates").Select
Worksheets("RentalDates").Range("I1").Select
If Worksheets("RentalDates").Range("I1").Offset("1,0") "" Then
Worksheets("RentalDates").Range("I1").End(xlDown).Select
[code]....
I have a workbook with over 500 rows and those rows are divided in diferent sections, to which I have left five empty rows to add more data, the problem is, how do I add four more rows once I have only one empty row of the five empty ones I had previous? There's also a formula on column H that I'd like to be copied down on to the newly added rows.
I don't know how I'm gonna deal with the rest of the other data that is gonna shift down as more rows are added, I said this because I want to be able to do the same on all the sections I have.
I have a VLOOKUP formula in cell X1. I am trying to insert all the below 10 IF statements in it. I have three problems:
A) Excel does not allow inserting that many conditions in one cell.
B) Don't want to use VBA.
C) VLOOKUP doesn't work, since the table array contains formulas. I can't have the table arrays change from 1s to 2s, when copying & pasting X1 to X2.
1. IF(A1=1,C1*D1)
2. IF(A1=2,E1*F1)
3. IF(A1=3,G1*H1)
4. IF(A1=4,I1*J1)
5. IF(A1=5,K1*L1)
6. IF(A1=6,M1*N1)
7. IF(A1=7,O1*P1)
8. IF(A1=8,Q1*R1)
9. IF(A1=9,S1*T1)
10. If all false then "NO".
I have Data in Column a There in not demarcation between two Groups of Data
I want it Insert a Row between the two Groups of data.
Each group of Data end with a cell has "Employee" in it
The Row is to be inserted after the cell having “employee”
I am working with large sequences of letters and I'm trying to put them into their own columns. I know about the 'text to columns' tool but the problem is my sequences are not delimited by anything. I would have to think the easiest way to accomplish what I want to do is to insert a comma, space or other delimiter between every letter, then use the text to columns feture.
EX. I'm looking for something that does this...
AAAAAA --turns into--> A,A,A,A,A,A or A A A A A A
I get on regular bases an xls file by email.
With this file I have to do several things for which I'm trying to create VBA.
What is the easiest way to import this VBA in the file I get ?
Should I create a txt file with the code and copy/paste?
Maybe create a template with code to import the txt file ?
How would the code look like for importing ?
I'm working with a line of code I can't seem to get right. I've got this string that will put breaks in, but I've realized it would make my life easier if the code, along with inserting a break, then inserted a formatted gray divider, it would make it easier. Here's what I have:
Sub BreakSections()
'This macro breaks the new data on sheets into their individual groups, inserting a blank line_
'which will need to be filled with a gray dividing line
Dim i As Long, j As Long
Application. ScreenUpdating = False
With Sheets("Contract Manufacturers")
. Cells.UnMerge
i = .UsedRange.Rows.Count
End With
For j = i To 2 Step -1
If Not IsEmpty(Cells(j, 5)) And Cells(j + 1, 5) <> Cells(j, 5) Then Rows(j + 1).Insert
Next
Application.ScreenUpdating = True
End Sub
I've tried then to put this code in after the Insert:
Range(ActiveCell, Range("AJ" & ActiveCell.Row)).Select
and then the formatting code.....
But I end up getting a whole mess of gray rows, displacing a lot of data. Is it possible to do these things together, or I should write another macro that just looks for the blank spots? It seems like it would be easier to do it all at once.
I want a script that inserts a picture based upon criteria of another cell. So for instance if cell A1 = Mad then insert Mad.jpg else insert Happy.Jpg
I can get the picture inserted properly based upon the cell however the picture does not delete when I click the sub button again. I cannot make it delete Mad.jpg from it's location and replace it with Happy.jpg if the criteria in A1 says "Happy".
VB:
Sub Picture()
Dim myPict As Picture
Dim myPicts As Integer
[Code]....
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.
Inserting a row. I have a for loop, and after a certain condition is met, add a row.
VB : Rows(i).Insert Shift:=xlDown
I have entered the current format of the data and the required format as well...
View 3 Replies View RelatedI have an excel sheet which contains the data in blue. In another sheet I have the same data but with an extra code (red). For my question I have put both in one sheet. My problem is that I need to combine the extra code info in the sheet with the blue data. Both the blue column and first red column are identical.
View 4 Replies View RelatedThe delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.
I have a problem I am working on (see attachment for better understanding).
I have 4 sheets
1) quote
2) invoice
3) delivery note
4) products and settings
Now in the quote sheet I am trying to add a way of from a drop down menu, selecting the product from the products sheet and then a new line will be added automatically.
Then what ever is in the quote sheet is automatically copied to the invoice and delivery note sheet.
im on a spreadsheet that im plugging into falconview, a map viewing program-on the spreadsheet i have coordinates, but not directions (N,W,S,E)-how can i enter in say a "W" before each coord without manually plugging it in- i would greatly appreciate it-its for OTJ military work
View 11 Replies View RelatedI want to set up a table that automatically adds rows, ideally to the top... The way I was guessing to do it would be to alter rows in the table to have a variable... My variable is 'acctstotal' anyway I figured there would be a way to edit the rows to acctstotal+ 1 or somthing along those lines but I havent been able to figure it out
btw acctstotal is a variable that simply counts the number of nonblank lines that are in the first column of the table which tells me the total number of accounts
I have an excel sheet, where I want to insert a column or more columns between row C and D. The file is attached below. Usually, I would right click row D and choose "insert column". This does not work here. How would you do it?
View 2 Replies View RelatedI'm inserting a function in a cell via code:
[Code] ........
The problem is that I want the second cell of the NETWORKDAYS function (in this case: R2C11) to be a variable cell. I want it to be cells(2, c - 3).
How do I insert a row with A-N Merged and centered? Interior color should be dark blue with white font.
Currently I'm doing this to add a space based on a number I have in a column S.
[Code] .....
In column S I have a series of numbers 1 - 5 and when a unique number is found Insert this row I can't seem to figure out how to create.
[Code] .....