Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add a row
Good afternoon!
I know there are several posts already about this subject, but I can't seem to make any of the previous answers work. I am trying to insert a row after a given number defined by "INSERTPT" then copy data into the new row. (Column A has a list of record numbers...INSERTPT is a user defined record number) With the following code, the data gets pasted wherever the last selection was made in that worksheet. (Most of the time, the previously run macro will have the user defined record number highlighted, so this macro pastes right over it, rather than adding a row and pasting under it.) Thanks for any help! Sheets("Update TEMP").Select Range("A3").Select INSERTPT = ActiveCell.Value Sheets("Update TEMP").Select Rows("8:8").Select Range("I8").Activate Selection.Copy Sheets("Incom Inspect Report (IIR)").Select Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 1 Step 1 If Cells(i, 1).Value = INSERTPT Then Rows(i + 1).EntireRow.Insert End If Next i Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add a row
Your code has this line that assigns a value to INSERTPT:
INSERTPT = ActiveCell.Value .... and later, an IF statement that compares a cell value to INSERTPT: If Cells(i, 1).Value = INSERTPT Then Is it possible the value in Cells(i, 1).Value never equals INSERTPT? The other thing I notice is this line For i = iLastRow To 1 Step 1 In order to get this structure to run, I had to change it to this: For i = iLastRow To 1 Step -1 Is that a similar problem for you? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add a row
Thank you so much..... I did have INSERTPT looking at the wrong cell...should
have been A2..... (I put the one spot back to a -1...that was just me trying to mess with things to get it to work before.) But.... It is now adding a row directly beneath and filling it with what looks like a paste (but not as values) leaving formulas and pasting as values over the original record number. Any ideas????? "Dave O" wrote: Your code has this line that assigns a value to INSERTPT: INSERTPT = ActiveCell.Value .... and later, an IF statement that compares a cell value to INSERTPT: If Cells(i, 1).Value = INSERTPT Then Is it possible the value in Cells(i, 1).Value never equals INSERTPT? The other thing I notice is this line For i = iLastRow To 1 Step 1 In order to get this structure to run, I had to change it to this: For i = iLastRow To 1 Step -1 Is that a similar problem for you? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add a row
It is now adding a row directly beneath and filling it with what looks like
a paste (but not as values) leaving formulas and pasting as values over the original record number. <<< I'm not sure I follow this: but I notice your code has the paste special instruction OUTSIDE the "if you find the right value then insert a line" structure. Try this instead: For i = iLastRow To 1 Step 1 If Cells(i, 1).Value = INSERTPT Then Rows(i + 1).EntireRow.Insert Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End If Next i |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |