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
vba to copy and insert row based on cell value
I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example,
Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet.
I would want to copy and insert the entire row.
LABELAB110
2
1
I have managed to write a macro that almost does what I require, but it inserts the copied row at the end and I want it to insert before the row it's just copied. Any ideas how I can modify the code to do this?
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 am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
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
Attached 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.
Looking for a code to do the following:
If rows 1 through 20 are unlocked and rows 21 to end are Locked
I want to use a Command Button to automatically
> Insert a new row (after row 20 and not before)
> Copy row 20 (which is already formatted)
> Paste row 20 onto the newly inserted row with all formats
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 Replies View RelatedI 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 have a large spreadsheet of demographic data. In column A there is a name, in column B there is Address1 and in column C there is Address2. First I need to insert a new line under each line of data. Then I need the Address2 data (column C) of each line to be copied/pasted to that new inserted line, but in the Address1 column (column B).
View 9 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 have a macro that was working fine until I added a new column to my active sheet. Now it fails with a run time error 1004, insert method of class failed. The code is as follows:
Private Sub UpdatePart_Click()
Dim S As String
Dim r As Excel. Range
Range("A2").Activate
S = InputBox("Enter the part no. you wish to update")
On Error Resume Next
If S = "" Then 'Exit Sub
'If StrPtr(S) = 0 Then
MsgBox "Update Cancelled"
Else
Set r = Columns(1). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)...............
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
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 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.
1
2
3
4
5
6
7
8
9
I'm not an experienced VBA programmer, so hopefully (and probably) for you it's easy to tell me how to do it: So far, I copy a row(1) to another position (7) with this macro:
Rows("1:1").Select
Selection.Copy
Range("A7:BU7").Select
Selection.Insert Shift:=xlDown
Range("A7:BU7").Select
Application.CutCopyMode = False
Range("A7").Select
That works well so far. Now what I want to do is, to use a form and enter a number and have the row 1 inserted in this row number (because it should not always be in row 7). For example that i can insert 38 in the form and it will copy the row number 1 to row number 38. I tried this:
Range ("frm_insertrow.txt_rownumber").Select
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 have a list of 12.000 pictures which have to be "connected" with the article number (1000) & color (21). Here starts the problem... Each article usually has more than 2 colors, but is listed only once per row and the colors are stated in columns.
What I would need is a macro or function that would do from such structure:
art description color1 color2 color 3
1000 product A 21 22 23
1001 product B 19 23
this:
art. description picture
1000-21 product A, color 21 1000-21.jpg
1000-22 product A, color 21 1000-21.jpg
1000-23 product A, color 21 1000-21.jpg
1001-19 product B, color 21 1001-19.jpg
1001-23 product B, color 21 1001-19.jpg
1. check if the row in color columns is not null
2. if this is true, then I would like to copy the entire row & paste it below existing
3. then the "art." column would be changed (=A2&"-"&C2), so the article woul get suffix of the color (and column "picture" would be created, maybe something like =A2&".jpg")
4. if any other color column in the same row is greater than null, then proceed to next color & repeat steps 2. & 3., else go to next row
5. until all 5000 rows are converted in about 12.000 rows.
I was searching through this site and found this macro ...
View 9 Replies View RelatedI 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 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?
View 3 Replies View RelatedI need to find a way to insert a row (preferably at the bottom of an existing list) that mirrors the formatting of the row above.
I have had a stab at this (see attached example file) but my solution requires the user to select the correct activecell before it works correctly
I have a macro that searches a column for a date and then selects the appropriate date. The next step is to then copy the row and insert a new one below the copied one and paste only the formulas and none of the data. I have tried several iterations but all of the seem to paste the data too.
View 6 Replies View Related
