Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Macro To Insert Rows After Every Line

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Here is what I am trying to do, can anyone decipher this for me and tell me what it should be?

I am trying to basically double space over 100 lines of text (i know i only have it set to 10 right now but that's just for testing)

RNI = ROW NUMBER INDEX


Code:

Sub test2()

Dim RNI

RNI = 1

For X = 1 To 10

RNI = RNI + 1

Rows("RNI : RNI").Select
Selection.Insert Shift:=xlDown
    
Next X

End Sub





Similar Excel Video Tutorials

Helpful Excel Macros

Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note

Similar Topics







I would love to simplify this code. It is extremely long and I have to have 3 macros to complete the full need for what I am doing. It is very repetative. I am inserting 6 rows between the information that I have on my data sheet. However, I now have the need for a 7th row to be added and do not want to have to go back through and manually edit every number if I can help it.

Code:

 Sub Input_Lines_1()
'

'
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("25:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("32:32").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("39:39").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("46:46").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("53:53").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("60:60").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("67:67").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("74:74").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("81:81").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("88:88").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("95:95").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("102:102").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("109:109").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("116:116").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("123:123").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub


Thank you so very much.


I have recorded a macro to insert multiple columns throughout a spreadsheet and I got the error: Compile Error: Wrong number of agruments or invalid property assignment.

I deleted all the code for my selecting cells as I scrolled to the right as I was adding columns and it is down to only selecting the applicable columns and inserting the number of columns in to the right of the selected column. I do not see where there is an error in the code. I do not get a highlight or underlining of text to indicate the error.

Thanks for your help.
Rodney Jorgensen

Sub Columns()
'
' Columns Macro
' Inserting Blank Columns
'

'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight
Columns("BL:BL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BX:BX").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CD:CD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Columns("CN:CN").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CT:CT").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("DF:DF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EV:EV").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EZ:EZ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FD:FD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FH:FH").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FL:FL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FP:FP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FW:FW").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GK:GK").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GO:GO").Select
Selection.Insert Shift:=xlToRight
Columns("GS:GS").Select
Selection.Insert Shift:=xlToRight
Columns("GV:GV").Select
Selection.Insert Shift:=xlToRight
Columns("GY:GY").Select
Selection.Insert Shift:=xlToRight
Columns("HB:HB").Select
Selection.Insert Shift:=xlToRight
Columns("HF:HF").Select
Selection.Insert Shift:=xlToRight
Columns("HI:HI").Select
Selection.Insert Shift:=xlToRight
Columns("HL:HL").Select
Selection.Insert Shift:=xlToRight
Columns("HO:HO").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Range("HI1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("11:11").Select
End Sub


Below is the code in which I would like to know how to simplify. I would like to do a loop but I don't know if that will work. I have about 300 rows that this macro would be inserting rows. This is only a small portion of it.


Quote:

Code:

Sub Input_Lines_1()
'

'
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("25:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("32:32").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("39:39").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("46:46").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("53:53").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("60:60").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("67:67").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub







Hi all,

I have a data sheet in which i'd like to insert some lines and add text to the newly added line. I have recorded a somewhat ugly macro to do it but i am wondering if there is a better way of doing this. Also because the data sheet will differ in size and can be quite long.
Here is the code i have now:
Code:

Sheets("Sheet3").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Rows("9:9").Select
    Selection.Insert Shift:=xlDown
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Rows("13:13").Select
    Selection.Insert Shift:=xlDown
    Rows("16:16").Select
    Rows("16:16").Select
    Selection.Insert Shift:=xlDown
    Range("A16").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown
    Range("A18").Select
    ActiveCell.FormulaR1C1 = "Change to"
    Range("A19").Select
    Sheets("Sheet1").Select
End Sub


Basically what it is doing is adding lines in row:
3-4 + 7-8-9 + 11-12 + 15-16-17 + 19-20 + 23-24-25 (when the data sheet is untouched)

How can i write this better?

Thanks for any help!


Hi all. This is my first post to this forum. I am looking forward to hopefully learning something and contributing to the group as well.

My question is this:

I have a need for a macro that will insert anywhere from 1-5 blanks rows b/t selected data in a spreadsheet.

The code I have written so far works... sort of. Ha

The problem is that my current attempt inserts the # of rows between ALL data and not the selection. I do not know the call to make this work so any info would be much appreciated.

Just to let you all know I want the user to be able to enter the # of rows they need into a message/input box. As of now I have it setup as a userform with a textbox, but I am flexible with that.


Code follows:


Code:

Sub InsertRows()

UserForm4.Show

End Sub



Private Sub CommandButton1_Click()

Dim NumRows As Integer

NumRows = TextBox1.Text

    Select Case NumRows
        Case "1"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "2"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "3"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "4"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "5"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
    End Select

Unload Me

End Sub





Hi all. This is my first post to this forum. I am looking forward to hopefully learning something and contributing to the group as well.

My question is this:

I have a need for a macro that will insert anywhere from 1-5 blanks rows b/t selected data in a spreadsheet.

The code I have written so far works... sort of. Ha

The problem is that my current attempt inserts the # of rows between ALL data and not the selection. I do not know the call to make this work so any info would be much appreciated.

Just to let you all know I want the user to be able to enter the # of rows they need into a message/input box. As of now I have it setup as a userform with a textbox, but I am flexible with that.


Code follows:

Code:

Sub InsertRows()

UserForm4.Show

End Sub



Private Sub CommandButton1_Click()

Dim NumRows As Integer

NumRows = TextBox1.Text

    Select Case NumRows
        Case "1"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "2"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "3"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "4"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
        Case "5"
        Selection.End(xlDown).Select
        Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
        Loop
    End Select

Unload Me

End Sub





Hi guys, i couldn't get an answer from another forum for this. i'm doing this macro that will insert rows for those I want to. Basically it's those rows after the subtotals.
My file is in the attachment & I would have a formula which reads "Insert Rows" so I can filter them, select visible cells & insert:

The VBA for the filtering & deleting:
Code:

Selection.AutoFilter Field:=4, Criteria1:="Insert Row"
Rows("4:10").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Insert Shift:=xlDown


The error that appeared:
Run-time error '1004':
Insert method of Range class failed


Hi, I have about 10 tables on a single spreadsheet, and everyday i need to insert a new line to each table, the added lines will never get deleted, it keeps adding on, currently I have a macro like this:

'
Rows("11:11").Select
Selection.Insert Shift:=xlDown
Rows("1064:1064").Select
Selection.Insert Shift:=xlDown
Rows("1615:1615").Select
Selection.Insert Shift:=xlDown
End Sub

It doesn't work exactly how i wanted it, because once I add a line on the first table, my other 9 tables will get shifted also. Is there way I can write a macro for this? Thank you for your time! Please help!


Hey guys,

First time poster here so please bare with me!

I have a table that gets built out of about 100 different scorces. they all have the same format, what i need help with building is a macro that will copy and insert the info from the top equaly formatted area into the growing table below and be able to repeat the function to the same areas of the table as it grows.

I can record a macro that will work once but once the destination cells have been shiffted by the second use of the macro it all goes down hill. How do you get the macro to copy and insert correctly?

Here is the macro that i have recorded and works just once since the rows referanced are shifting as I copy and paste it.

Code:

Sub copy_insert_test()
'
' copy_insert_test Macro
' Macro recorded 1/28/2008 by z18v
'

'
    Rows("5:6").Select
    Range("H5").Activate
    Selection.Copy
    Rows("25:25").Select
    Range("H25").Activate
    Selection.Insert Shift:=xlDown
    Rows("7:8").Select
    Range("H7").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("31:31").Select
    Range("H31").Activate
    Selection.Insert Shift:=xlDown
    Rows("9:10").Select
    Range("H9").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("37:37").Select
    Range("H37").Activate
    Selection.Insert Shift:=xlDown
    Rows("11:12").Select
    Range("H11").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("43:43").Select
    Range("H43").Activate
    Selection.Insert Shift:=xlDown
    Rows("13:14").Select
    Range("H13").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("49:49").Select
    Range("H49").Activate
    Selection.Insert Shift:=xlDown
    Rows("15:16").Select
    Range("H15").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("55:55").Select
    Range("H55").Activate
    Selection.Insert Shift:=xlDown
    Rows("17:18").Select
    Range("H17").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("61:61").Select
    Range("H61").Activate
    Selection.Insert Shift:=xlDown
    Rows("19:20").Select
    Range("H19").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("67:67").Select
    Range("H67").Activate
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=-3
    Rows("21:22").Select
    Range("H21").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Rows("73:73").Select
    Range("H73").Activate
    Selection.Insert Shift:=xlDown
End Sub


I know the reason that it doesn't work is that the macro refrences the cells that have shifted on the second inserting and starts inserting the copyed rows in the wrong locations. How do you referance a shifting area?

I would like to attach the workbook the visual might help.... whats the best way to attach the workbook?

Thanks everyone!


I would like to figure out how to swap multiple rows. I need to start with swapping rows 19 and 20. Then 39 and 40. Then 59 and 60, and so forth. So starting with 19 and 20 and the next rows 20 rows down respectively.
Ive tried recording a macro but is there anyway to get it to keep going and knowing to do it every 20 so I dont have to manually enter the numbers in the macro. It would take forever.
VB:

Sub Swap Rows() 
     '
     ' Swap Rows Macro
     ' '
    Rows("20:20").Select 
    Selection.Cut 
    Rows("19:19").Select 
    Selection.Insert Shift:=xlDown 
    Rows("40:40").Select 
    Selection.Cut 
    Rows("39:39").Select 
    Selection.Insert Shift:=xlDown 
    Rows("60:60").Select 
    Selection.Cut 
    Rows("59:59").Select 
    Selection.Insert Shift:=xlDown 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Excel 2002

I need to insert 10 rows at the top of a page

I have been using the macro recorder to do this and I get this:
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Is there some code I can write to just tell it 10 lines instead of using the recorder. it wont be fun to do when I need more than 10 lines. Thanks!


Good morning all

I've got a macro here that subtotals the number of cases and weight for each store in a list of many stores. Each store will have a different number of lines, based on the number of different products they are getting. After I subtotal, I insert rows 1 & 2 and need to write a couple of things in those lines.

What I want to do is insert rows one and two directly below the subtotal rows. I think that the problem is happening in my "INSTR" line close to the bottom of the macro.

Anyones help is greatly appreciated.


Code:

Sub PickList1()
'
' PickList1 Macro
' Macro recorded 5/26/2010 by Greg Moody
'

'
    Range("J17").Select
    ActiveWindow.SmallScroll Down:=-29
    ActiveWindow.SmallScroll ToRight:=-8
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(13, 14, 15 _
        , 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "skids"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "weight"
    If InStr(1, ActiveSheet.Column("C").Value, "Total") > 0 Then
    End If
    Rows("1:2").Select
    Selection.Copy
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown
    Range("C8").Select
End Sub





Hi, i've got a macro so i can store details from a quote sheet. The stored details page is just each heading as a column.

So i am using this macro at the moment:

Code:

Sub Test()
'Quote Number
Sheets("Quote").Select
Range("E16").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("A2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Customer Number
Sheets("Quote").Select
Range("E13").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("B2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Date
Sheets("Quote").Select
Range("G10").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("C2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'CPU
Sheets("Quote").Select
Range("E25").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("D2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'CPU Price
Sheets("Quote").Select
Range("G25").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("E2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Motherboard
Sheets("Quote").Select
Range("E26").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("F2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Motherboard Price
Sheets("Quote").Select
Range("G26").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("G2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'RAM
Sheets("Quote").Select
Range("E27").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("H2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'RAM Price
Sheets("Quote").Select
Range("G27").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("I2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Case
Sheets("Quote").Select
Range("E28").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("J2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Case Price
Sheets("Quote").Select
Range("G28").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("K2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Graphics Card
Sheets("Quote").Select
Range("E29").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("L2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Graphics Card Price
Sheets("Quote").Select
Range("G29").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("M2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Hard Drive
Sheets("Quote").Select
Range("E30").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("N2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Hard Drive Price
Sheets("Quote").Select
Range("G30").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("O2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'**/**-RW/***/DVDRW
Sheets("Quote").Select
Range("E31").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("P2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'**/**-RW/***/DVDRW Price
Sheets("Quote").Select
Range("G31").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("Q2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'**/**-RW/***/DVDRW1
Sheets("Quote").Select
Range("E32").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("R2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'**/**-RW/***/DVDRW1 Price
Sheets("Quote").Select
Range("G32").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("S2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Monitor
Sheets("Quote").Select
Range("E33").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("T2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Monitor Price
Sheets("Quote").Select
Range("G33").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("U2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Keyboard and Mouse
Sheets("Quote").Select
Range("E34").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("V2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Keyboard and Mouse Price
Sheets("Quote").Select
Range("G34").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("W2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Operating System
Sheets("Quote").Select
Range("E35").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("X2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Operating System Price
Sheets("Quote").Select
Range("G35").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("Y2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Speakers
Sheets("Quote").Select
Range("E36").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("Z2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Speakers Price
Sheets("Quote").Select
Range("G36").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AA2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Extra
Sheets("Quote").Select
Range("E37").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AB2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Extra Price
Sheets("Quote").Select
Range("G37").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AC2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Sub Total
Sheets("Quote").Select
Range("G39").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AD2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'VAT
Sheets("Quote").Select
Range("G41").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AE2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Delivery
Sheets("Quote").Select
Range("T4").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AF2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Discount
Sheets("Quote").Select
Range("G47").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AG2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Quantity
Sheets("Quote").Select
Range("G49").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AH").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown

'Grand Total
Sheets("Quote").Select
Range("G51").Select
Selection.Copy
Sheets("Saved Quote Details").Select
Range("AI2").PasteSpecial Paste:=xlPasteValues
Selection.Insert Shift:=xlDown


End Sub


Right, the code works but i have some problems:

When it saves the quote it enters from row 2 so it pushes all the rest down and puts the new one up the top, now this would be fine, but when trying to use a combo box to recall the quote number it means that for example the Value 1 on the combo box is the top one which could be Quote number 10, which is no use.

Please if anyone can help me solve this i would be sooooooooo happy. It's driving me nuts!

Thanks.

Pete.


Ok guys, I need a macro to run through a spreadsheet. There is a header line, but after that the columns are :
Client number, Name, etc

Client number is the one i need help with, it is a string. I need to go through the spreadsheet and any time the client number changes, insert two lines and total columns F and G.

I think this is code for inserting and totaling:
Code:

Rows("62:63").Select
    Selection.Insert Shift:=xlDown
    Range("F62:G62").Select
    Selection.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"


I need to figure out how to make the macro go through the whole spreadsheet and do this though.

Any help would be great. If you need further explanation let me know.

Thank you.


Hi,

I have inherited a spreadsheet that will open up a raw data file and import the data after removing unwanted header info. I would like to somehow count the number of lines that have been imported so that I can do some further tweeking. I am guessing this is where I would need to add it.

Here is the code that is doing the copying from the input file:

Code:

 Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
         With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Copy


If it's better to count the lines when pasting then here is the code that does that:
Code:

    Rows("5:5").Select
    Selection.Insert Shift:=xlDown
    Rows("4:4").Select
    Selection.Delete Shift:=xlUp


Any help is greatly appreciated.

Thanks


I'm attempting create a macro that will add a new line below the line in which the user is, copies all data and formulas from the line above, except for columns G and H. Ends in Column G, ready for editing. I came up the macro below, which does what I want, if you are in row 3, but how do I generalize it.

Another way to ask the question, how do I change the reference
Rows("4:4").Select
to something that means select the row below the one I'm in. e.g.
Rows("the-one-I'm-in+1").Select

Code:

Sub AddSecondProduct()
'
' AddSecondProduct Macro

'
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("3:3").Select
    Selection.Copy
    Rows("4:4").Select
    ActiveSheet.Paste
    Range("G4:H4").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G4").Select
End Sub


Any help appreciated.
JensPeter


Hello,

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:
Code:

    Rows("30:30").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove


The problem is that I don't have the number of the row in number form. Instead, I have a variable representing the number of the row, as shown in the following code:
Code:

' The user selects a cell. 
' The selected cell's row is assigned to the variable "InsertRowHere"
    InsertRowHere = ActiveWindow.RangeSelection.Row
    ' But now, using this variable, the following line of code will not work
    Rows("InsertRowHe InsertRowHere").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove


As you can see, I need to insert a new row. And I need that new row to be based on the currently selected cell's row.

Any ideas on a better/correct way to achieve this?

Thanks so much!


Hi all,

I have encountered a Run-Time '1004' error which says my selection is not valid on the Insert line(bolded) when my macro tries to insert a cut row into row 2.

Please help!!!

Code:

WS2.Range("H" & d).Rows.Offset(1, 0).Select
WS2.Range("H" & c).EntireRow.Select
Selection.Cut                       
WS2.Rows(d).Select                
Selection.Insert Shift:=xlDown





I need to paste the same copied range 9 times. I have it working, but it's really ugly and I'm trying to learn how to get rid of unnecessary selects in my code. Is there a better way to do this?

Code:

 
Range("b1").Resize(bb, 3).Copy
    Range("b1").Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Selection.Copy
    Selection.Insert Shift:=xlDown


I wou'd have thought I could just paste it without re-copying it every time but that doesn't seem to work.

And just to increase my vba knowledge, could someone tell me if having the word "selection" slows things down like having "select" does?


Below is my code -
The part of the codein blue font is for writting formulas in the cells. The current code is writing formula for every cell in the column until the last cell. I want to write the formula for only the number of rows that actually contain the data.

Can anyone help me please??

Thanks.

Code:

Sub FormatAccessOutput()

    Application.ScreenUpdating = False
    Cells.Select
    Range("D5").Activate
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

'Insert formulas
     Range("L2", Range("L2").End(xlDown)).FormulaR1C1 = "=RC[-5]/RC[-1]"
    Range("O2", Range("O2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-1]"
    Range("Q2", Range("Q2").End(xlDown)).FormulaR1C1 = "=RC[-10]*RC[-1]"
    Range("S2", Range("S2").End(xlDown)).FormulaR1C1 = "=(RC[10])/(RC[6]+RC[7])"
    Range("X2", Range("X2").End(xlDown)).FormulaR1C1 = "=(RC[-4]+RC[-3]+RC[5]+RC[-17])/(RC[1]+RC[2])"
    Range("AH2", Range("AH2").End(xlDown)).FormulaR1C1 = "=RC[-3]-RC[-27]"
    Range("AJ2", Range("AJ2").End(xlDown)).FormulaR1C1 = "=(RC[-5])/RC[-1]"
    Range("AK2", Range("AK2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-2]"
    Range("AM2", Range("AM2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""", ""N"", (RC[-1]*(RC[-14]+RC[-13])-RC[-19]-RC[-18]-RC[-10]))"
    Range("AO2", Range("AO2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""", ""N"", RC[-10]-RC[-1]*RC[-6])"     
'Change the format of columns AI and Z to number with no decimal places

    Range("AI:AI,Z:Z").NumberFormat = "0"

'Change the format of columns L,O,Q,S,X,AJ,AK to number with two decimal places
    Range("L:L,O:O,Q:Q,S:S,X:X,AJ:AJ,AK:AK").NumberFormat = "0.00"

    Columns("G:G").Insert Shift:=xlToRight
    Range("H1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Range("G2", Range("G2").End(xlDown)).FormulaR1C1 = "=(RC[1])"
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("N1").FormulaR1C1 = "=(RC[-1])"
    Range("N2", Range("N2").End(xlDown)).FormulaR1C1 = "=(RC[-7]/RC[-2])"
    Columns("R:R").Insert Shift:=xlToRight
    Range("R1").FormulaR1C1 = "=(RC[-1])"
    Range("R2", Range("R2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-2])"
    Columns("U:U").Insert Shift:=xlToRight
    Range("U1").FormulaR1C1 = "=(RC[-1])"
    Range("U2", Range("U2").End(xlDown)).FormulaR1C1 = "=(RC[-14]*RC[-2])"
    Columns("AC:AC").Insert Shift:=xlToRight
    Range("AC1").FormulaR1C1 = "=(RC[-1])"
    Range("AC2", Range("AC2").End(xlDown)).FormulaR1C1 = "=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
    Columns("AN:AN").Insert Shift:=xlToRight
    Range("AN1").FormulaR1C1 = "=(RC[-1])"
    Range("AN2", Range("AN2").End(xlDown)).FormulaR1C1 = "=(RC[-4]-RC[-33])"
    Columns("AR:AR").Insert Shift:=xlToRight
    Range("AR1").FormulaR1C1 = "=(RC[-1])"
    Range("AR2", Range("AR2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-3])"
    Columns("BB:BD").Select
    Selection.Cut
    Columns("L:L").Insert Shift:=xlToRight
    Columns("M:N").Select
    Selection.Cut
    Columns("R:R").Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("D:D").Insert Shift:=xlToRight
    Range("N2").Select
    Selection.AutoFilter
    ActiveWindow.FreezePanes = True
    
    'Highlight columns H,O,U,X yellow
    Range("H1", Range("H1").End(xlDown)).Interior.ColorIndex = 36
    Range("O1", Range("O1").End(xlDown)).Interior.ColorIndex = 36
    Range("U1", Range("U1").End(xlDown)).Interior.ColorIndex = 36
    Range("X1", Range("X1").End(xlDown)).Interior.ColorIndex = 36
    
    'Highlight columns AM,AQ,AU light green
    Range("AM1", Range("AM1").End(xlDown)).Interior.ColorIndex = 35
    Range("AQ1", Range("AQ1").End(xlDown)).Interior.ColorIndex = 35
    Range("AU1", Range("AU1").End(xlDown)).Interior.ColorIndex = 35
    
    'Highlight column AE light blue
    Range("AE1", Range("AE1").End(xlDown)).Interior.ColorIndex = 33
    
    'Highlight column AF blue
    Range("AF1", Range("AF1").End(xlDown)).Interior.ColorIndex = 34
    
    'Highlight column AD grey
    Range("AD1", Range("AD1").End(xlDown)).Interior.ColorIndex = 15
    Application.ScreenUpdating = True
End Sub





Hello,

I'm looking for the code to insert 3 lines into a worksheet and have the sum line at the bottom include the inserted cells into the sum formula.


For instance; the formula at the bottom of the sheet in cell H12 is now =SUM(H3+H6+H9). After inserting the rows, the formula will now be in cell H15 and would be =SUM(H3+H6+H9+H12).

The code for row insertion is
Code:

     Range("A1").Select
    Do Until ActiveCell.Value = "insert lines here"
    ActiveCell.Offset(1, 0).Select
    Loop
    
    ActiveCell.Offset(-3, 0).Select
    
    ActiveCell.Range("A1:A3").Select
    
    Selection.EntireRow.Select
    
    Selection.Copy
    ActiveCell.Offset(3, 0).Select
    
    Selection.Insert Shift:=xlDown
    
    Application.CutCopyMode = False
    
    ActiveCell.Range("A1").Select


What code would I need to have the new lines included in the formulas?

Thanks in advance.

HBF


Hello all,
I would appreciate any support you could give on a macro solution for an invoice i'm trying to automate. Attached is an invoice with a button to call a marco "Addlines". I would like for the macro to:
1. Insert two lines beneath Row 46
2. Copy down the formula under "Total Cost" Cell K46
3. Have the "Subtotal" currently in K47 sum the additional items under "Total Cost"


I've tried many different types of code without success; however, below is what I've got so far.


Code:

' Insert rows for addtional items
    Rows("47:47").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
'Copies the formula Qty*Unit cost to get the Total Item Cost
    Cells.Find("Total Cost").Offset(1, 0).Select
    Selection.AutoFill Destination:=Range("K46:K48"), Type:=xlFillDefault
       
 'Sums the Total Item Cost for a Sub Total
    Cells.Find("Sub-Total").Offset(, 2).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"


Any assistance would be appreciated.


Hi, I have about 10 tables on a single spreadsheet, and everyday i need to insert a new line to each table, the added lines will never get deleted, it keeps adding on, currently I have a macro like this:

'
Rows("11:11").Select
Selection.Insert Shift:=xlDown
Rows("1064:1064").Select
Selection.Insert Shift:=xlDown
Rows("1615:1615").Select
Selection.Insert Shift:=xlDown
End Sub

It doesn't work exactly how i wanted it, because once I add a line on the first table, my other 9 tables will get shifted also.

For example, the first table's range is from A1 to A10, and the second table's range is A12 to A22. I want to add an extra line at the beginning of each table, so as of now, it's A1 and A12. But once I add an extra line on A1, the first table is now expanded to A1 to A11, so the second table's starting point is no longer, A12, instead it would be A13. Therefore, I would not be adding a line at the beginning of each table. I hope that clarifies my problem, Thank you for your time!

The macro I have will not work because I do not know which rows in Column A the duplicates will appear.

Code:

  Sub Insertrowfornomatch()  
  Rows("3:3").Select  
  Selection.Insert Shift:=xlDown  
  Rows("5:5").Select  
  Selection.Insert Shift:=xlDown  
  Rows("6:6").Select  
  Selection.Insert Shift:=xlDown  
  End Sub  


These are the steps I am trying to execute.

Look at A2
Then select the row below it (A3)
- If the data in A3 does not match the data in A2 then insert a new row above A3
- If the data in A3 does match then go to the next row (A4) and repeat the procedure.
Continue the procedure until every row with data in Column A has been checked.

Can you assist me in adding logic to my macro?


Hello my friends,

I need some help here please.

I have a macro in VBA that is supposed to select a row, copy's the format and uses "Shift:=xlDown" to shift the cells down.

But i got a problem, when the line with "Shift:=xlDown" is executed, a dialog box appears (see attached image), can anyone help me to get rid of the dialog box?

This was working (the dialog box wasn't appearing), and now i don't know why it stopped working.

The code i have is this

Code:

        Rows(p_portagem & ":" & p_portagem).Select
        Selection.Copy
        Selection.Insert Shift:=xlDown