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 deleted the data and made up some figures but kept the format to protect privacy. Highlighted blue and green because it's possible to have the same name have 2 different types. I put in dashed lines to more clearly divide months.
1. For each UNIQUE "Name" and "Type" add a new row starting from last entry 2. Copy A,B,C to new row 3. Copy LAST (most recent) "End Balance" from column "I" corresponding to last person 4. Copy column "I" formula into new role
So Name, ID#, type, column D and the formula in "I" (excluding "Name" and "Type" duplicates) should be added to the end of the sheet with their new row
Unsure if possible:
5. Make old amounts (columns D,F,H,I) not count towards the totals 6. Totals reflect new amounts only so values aren't counted multiple times
I currently have a spreadsheet with 4 columns that represent Surface corridinates and Bottom Hole Corridnates. Surface X, Surface Y, BH X, BH Y
We need to get the Bottom Hole XY to be directly under the Surface XY.
Attached is some sample Data and What I mocked up for a temp solution is in VBA Module2 RunAll it will complete 399 rows of data.
In Module1 I have the starting Code that I modeled off of a macro that I started to get the base code.
I just need this to loop till it has no more data to copy. and not use module2 at all.
I commented everything for my personal benifit and to help me understand the steps. also know its a monster right now but it does most of what I need it to do.
Sub CopyPasteBHXYs() 'Wrote Based on recorded macro 'Selects second data row and inserts a row above Rows("4:4").Select Selection.Insert Shift:=xlDown
'How to make it loop till the last data is reached?
End Sub
Side Note I'm finally taking a Excel VBA course Apr 8th and 9th.
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
I have a simple command button in my worksheet that will insert a blank row and copy formatting, but not the formulas. How do I adjust the code so that the formulas are copied to the new blank row? This is currently what I have:
Private Sub CommandButton2_Click() Sheets("WAWF Track").Range("A9").Select ActiveCell.EntireRow.Insert Shift:=x1Down End Sub
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:
1) Select and Copy entire row 2 from sheet(1) 2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable. 3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3. 4) loop through all records until complete
I have tried writing the code in VBA but am not even getting close!
I have a spreadsheet with data in the first few columns, then a few columns of different formulae which reference the data.
This spreadsheet is constantly getting rows inserted into it, and I'd like for the formulae to be copied into the new rows automatically, rather than having to copy/paste the formulae every time columns are inserted.
So an extra row needs inserting below the last record of each Data 1, with each value in Data 1 through 5 coming from the record above, but Data 6 is called "Diff.". The values in columns 1 through 4 for "Diff." are then driven by the following formula:
to be able to insert a number of rows via formula. I am having issues coming up with anything on my own. I am hoping to duplicate(and insert) a specific row (x) number of times based on a number returned from a previous formula.
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.
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.
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00 X DAVE 43.93 DAVE 92.00 X FRED 4.66 X GREG 23.55 GREG 84.21 GREG 8.69 X MIKE 83.50 X SETH 41.33
I have the following macro that inserts a blank line when a field changes. This works fine. However I would like to use this in another spreadsheet that has several addition columns of unrelated data. Therefore I want to insert 1 (row of) blank cells only in the columns specified in the range. The range is 3 columns wide, E - G. I think I'm close, but ...
Dim myRow As Long myRow = 3 'data starting row Do Until Cells(myRow, 5) = "" ' the # is the column that changes which I want to trigger a blank insertion If Cells(myRow, 5) = Cells(myRow - 1, 5) Then myRow = myRow + 1
I have a large file that has the following setup 2003 Zipper X 2004 Zipper X 2005 Zipper X 2003 Zipper Z 2004 Zipper Z 2005 Zipper Z How can I insert rows at changes in the product description
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 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.
I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?
I receive a Excel download daily consisting of 6 columns of nine numbers and currently "copy" column 2 and "insert copied cells" into a running spreadsheet. I would like a simple way to have the 9 lines pasted as below rather than as sequential lines.
i have some columns which contain the IF function and so it returns me a value in each cell. Is there any way to copy only the values AND insert shift down the copied cells onto somehwere else?
I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .
Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.
This is a reformulation of this post: [URL] ...........
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
