I'm trying to create a fairly dynamic report in excel based on queries imported from access into a excel template (the queries are imported into separate worksheets). So what I want is simply use a =Count to find the number of rows in my imported query and based that count value insert an additional row beneath the first line of the report worksheet and copy down the formula.
View 2 RepliesI have a number of named ranges on seperate sheets. As I add more entries to each sheet, the named range is naturally increasing so I dont have a fixed cell refence
Effectively what I would like to know is - is there coding that would allow me to copy one named range - and paste the values to the the row beneath the last entry in a second named range. Filtering these I can do - it is just the copying that has me stumped
I have a spreadsheet where I need to insert a column then a formula that performs a calculation on the previous column, all the ranges change; so far I ahve managed to insert the column to the right of the one I want to perform the calculation on and insert the formula in the first line, but i'm having a problem copying this dowm to the end of the data range.
View 3 Replies View RelatedI'm having trouble with my self designed "program" in excel.
Is there a way to insert a row and copy the value or formula of the upper row? withouy using macro.
I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:
A
B
C
D
E
001
ADESF
500.00
1001210
EMF
The values in column C and D for the new row will be the same.
I have this formula that I'm trying to insert into cell AE2, then copy down the entire column. When I execute it, none of the cells are populated.
Range("AE2").Select
ActiveCell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$2/365)/P2)"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE3495")
Range("AE2:AE3495").Select
I am trying to finish off a form that allows a user to insert a row below the selected cell and copy the formula from the line above if one exists. The code inserts the line but does not copy the formulas if they exist.
Application. ScreenUpdating = False
Dim cRow
Dim j As Long
cRow = ActiveCell.Row
With ActiveCell
.EntireRow.Insert
End With
For j = 1 To Cells(1, 255).End(xlToLeft).Column
If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j)
Next j
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
And this code for deleting the column:
VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
I have a sheet made of 3 columns, every column has several rows, I am trying to bring the values of the second column beneath the values of the first column and then the values of the third column beneath them all. Attached are 2 sheets showing the current and required scenarios
View 4 Replies View RelatedHave you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedI have a chart, I need to select a cell beneath that chart. It can be the top cell or top left cell. So is there a way, by using vba, to know which cell is the top or left cell being covered by a chart
View 2 Replies View RelatedI have two columns and each record in the spreadsheet fills both cells. I want all rows beneath the data to be hidden except for one, which is for the user to input a new record. When they are done completing the record, 1 row becomes unhidden (which will be for another inputted record). I also need it so that the new row becomes unhidden ONLY when the user inputs values in BOTH columns of the new record. I have already used Data Validation on each individual cell (column a is validated only for numeric entries and column b can only be picked from a drop-down list)
View 9 Replies View RelatedI can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
I have some xls files. I need to find the sum of a specific column. the column heading is always TOTAL but the column it resides in changes column to column. so one file may have the text total in column H and others will have the text total in column X. how can i make a macro so it finds the text 'total' and then sums up all the values in that column underneath it and displays it at the very bottom of that column?
View 9 Replies View RelatedWhen recording a macro, how would I have it record me searching for a word in a cell, then select the two rows beneath it so that I can hide/unhide the hidden rows?
The reason it has to search for the word in the cell is that with changes above, the rows keep getting bumped further and further down (i.e., a moving target).
I am still new to this, and I pulled this code off of another site.
Background:
- Using Excel VBA, 2007
- I have a sheet that has a name in B6, has 4 blank spaces beneath, and then another name, 4 blank spaces, etc.
- I wrote a code to fill each of these 4 blank spaces with the value at the top of each
- The code works, but it won't fill the 4 spaces at the bottom of the column.
What is needed:
-how I can adjust to code below to fill in the 4 spaces beneath the last value in the column?
Code:
Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
[Code]....
I'm trying to count the number of non-empty cells beneath a reference cell and would be most grateful for a few pointers. The code I currently have (that doesn't work) is:
View 2 Replies View RelatedI've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.
But.. i want to:
insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )
I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies View RelatedI have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
What I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.
Please take a look at the attached sheet if it clarifies things.
i have a excel spreadsheet named "ECN Number". in this sheet i have a row in which column5 marked with value "x",column22 is empty " " and column23 has "some value".
if the above statement is true,i want to insert an entire blank row and i want to insert another row below this row below the empty row with column6 marked as "x",column22 empty " " and column23 with the samevalue from the above row.
after inserting this row with these values, i want to insert another entire blank row. i have attached the workbook with the specified sheet.
I have a list of names in Col A. These names are seperated by their respective department headers also in Col A. Rows with (Add Member) contain all the formulas and formating necessary for that perticular department. I need to copy the (Add Member) row and place it above the the old (Add Member) row, then clear the contents of the A"#" cell to allow the user to "Add" the new member to the list. I would like this to occur only when Add Member cell is clicked in some way.
The (Add Member) cell is locked to prevent deletion in a protected sheet. This new row will have to be unlocked for the new entry.
A1
Name
Department A
Joe Smith
Ann Doe
John Snuffy
(Add Member)
Department B
Jon Doe
Frank Lee
(Add Member)
Department C
Etc.
(Add Member)
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
The posts I have found regarding inserting rows and copying formulas etc. and their answers do not seem to work in my worksheet.
All I would like to do is insert a row then copy the row below into the new row.
I have tried to modify the VBA given in similar posts and cannot seem to make it work.
I am trying to figure out the code that will copy the contents and formatting from the row above a selected cell. I have already figured out how to insert the row, but I can't get it to copy the formatting..
Here is what I have so far...
Sub Insert()
Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
I need a macro that will copy a row to "n" number of identical rows below it, depending on user input. I am not skilled at VBA but I cobbled together some code I found online (see below). Unfortunately, it does not work properly. The input box pops up, but it only copies one new row regardless of what number you enter.
Sub InsertCopyRow2()
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
End Sub
I have the current macro which copies a hidden sheet and places it after the current sheet named "Background". I'd like the macro to instead place it at the end (far right) of all the sheets, regardless of what they are named. How can I do this?
Sub NewSheet()
Sheets("Blank Category Sheet").Visible = True
'Replace "Sheet1" with the name of the sheet to be copied.
ActiveWorkbook.Sheets("Blank Category Sheet").Copy _
after:=ActiveWorkbook.Sheets("Background")
Sheets("Blank Category Sheet").Visible = False
End Sub
I am trying to figure out how to automatically copy and insert rows based on the QTY number in column D. For instance, rows 2 and 3 are already correct since QTY=1, but since row 4 has a QTY of 5 I would want the formula/macro to then insert 4 rows above row 4 and paste the exact data that is in row into the inserted rows. I would want the macro to loop and do this for all the rows in the sheet until there are no rows with a QTY>1...
View 9 Replies View RelatedAttached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View 3 Replies View RelatedI am trying to insert a row every time a certain value appears in row.
Example attached.Example 1.xlsm
I need to copy the entire row that has the value 2 in column B and insert it in the line above (not fussy). It must be inserted and move all data in other rows accordingly.