Simple VBA editing.. need some insight!

[Pyrex238]

Moving this over to the proper forum...

Alright, I'm poked about in VBA for excel, but I'm having trouble learning how to reference a particular column to reproduce

currently this is the original code:

Sub InsertRowsAndFillFormulas_caller()
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog 
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]>
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x as long 
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
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

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.name).UsedRange.Rows.Count 'lastcell fixup

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

What I need to do is force data from columns B, H, J on the current selected row to populate on the new row. The current code only copies the 2 existing formulas. How do you reference data from those columns on the selected row to copy down to the new row below? And lasty, how do I remove the prompt to ask how many rows to reproduce? I just want it to default to reproduce 1 single additional row.

thanks very much for any help!

And I appreciate your help, I'm excited to learn more about excel and using macros.

[DCSwearingen]

I have a spreadsheet I use for taking minutes and assigning tasks.

Frequent copy and insert row activities so I now have included a right-click context menu item to copy and insert a copy of the entire row where the right-click takes place.

In ThisWorkbook I have

Private Sub Workbook_Deactivate()
'  This sub-routine deactivates the command from
'  the context menu each time the workbook is
'  de-selected or closed.
    On Error Resume Next
    With Application
        .CommandBars("Cell").Controls("Copy and Insert Entire Row").Delete
    End With
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'  This routine creates the context menu command
'  each time the workbook becomes the Active workbook
Dim cBut As CommandBarButton
    On Error Resume Next
        With Application
            .CommandBars("Cell").Controls("Copy and Insert Entire Row").Delete
            Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
        End With
        
        With cBut
           .Caption = "Copy and Insert Entire Row"
           .Style = msoButtonCaption
           .OnAction = "Copy_Insert_Row"
        End With
    On Error GoTo 0
End Sub

In a regular module I have

Sub Copy_Insert_Row()
'  This routine identifies the row for the cell,
'  copies the row, and inserts a copy
    ActiveCell.EntireRow.Copy
    ActiveCell.EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

[Pyrex238]

hmm.. I couldn't get either macro to work for some reason. Are you familiar with how to specify a particular column? All I really need to do is specify column so and so needs to be copied to the new row.

[DCSwearingen]

First, I missed the copy a column and insert as a row.

Is this what you really want to accomplish? A column contains 65,536 cells and a row only contains 256 cells, at least in Excel up through 2003. This is not reasonable, at least to me.

Next, the code I show works for me, I have sent it to people in different companies with different versions of Excel and it works for them.

Did you place the first two routines into ThisWorkbook via the VBE Window? And you put the Third Routine into a normal module?

Let's try a little trouble shooting:

• Insert a new sheet into your workbook that you can delete when done.
• In cell B5 and B6 enter something different for each.
• Select Cell B6 as the active cell.
• Press the key combination <Alt><F8> to bring up the macro dialog
• Select Copy_Insert_Row
• Run the macro

Do you now have three cells with entries(B5, B6, & B7?) Cells B6 and B7 should have the same data.

[DCSwearingen]

I applogize, I just read the entire thread again.

I will modify my routine to insert a single new row, and copy the formula from the previous row cells in columns B, H, & J into the same columns in the new row.

[Pyrex238]

I applogize, I just read the entire thread again.

I will modify my routine to insert a single new row, and copy the formula from the previous row cells in columns B, H, & J into the same columns in the new row.

I really appreciate your time. I'm still getting accustomed to programming macros, and learning all of the excel triggers and syntax.

How exactly do you reference a column within a row? - that is my major hang up .. recording macros only grabs a specific cell, but not a column within the row... allowing it to be unique for each row.

[Pyrex238]

Okay, sorry for my negligence... don't know what I did... was busy earlier.

Sub Copy_Insert_Row()
'  This routine identifies the row for the cell,
'  copies the row, and inserts a copy
    ActiveCell.EntireRow.Copy
    ActiveCell.EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

this section works great, it simply copies the row. However, I'm looking to copy only a few cells down.

I need to copy the following cells in the currently selected row ... cells in column B, F&G - these are formulas specific to that exact row so I need it to copy the formula down to the new row, but change the row number in the formula - much like the fill function does, and H.

I can't figure out how to do this.. been reading for a while.

[DCSwearingen]

This all depends on relative referencing in formulas and not ansolute referencing.

Also, formulas in the row pushed down will still reference the original row and do not change to reference the new row.

If that is what you want then this should work.

Sub Insert_Row_Copy_Formulas()
Dim myRow As Long, colB As String, colH  As String, ColJ As String
' Column B is column 2, H is column 8, and J is column 10 below - hard coded.
    myRow = ActiveCell.Row
    colB = Cells(myRow, 2).Address
    colH = Cells(myRow, 8).Address
    ColJ = Cells(myRow, 10).Address
    Cells(myRow + 1, 2).EntireRow.Insert Shift:=xlDown
    Range(colB).Copy
    Cells(myRow + 1, 2).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range(colH).Copy
    Cells(myRow + 1, 8).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range(ColJ).Copy
    Cells(myRow + 1, 10).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

[Pyrex238]

This all depends on relative referencing in formulas and not ansolute referencing.

Also, formulas in the row pushed down will still reference the original row and do not change to reference the new row.

If that is what you want then this should work.

Sub Insert_Row_Copy_Formulas()
Dim myRow As Long, colB As String, colH  As String, ColJ As String
' Column B is column 2, H is column 8, and J is column 10 below - hard coded.
    myRow = ActiveCell.Row
    colB = Cells(myRow, 2).Address
    colH = Cells(myRow, 8).Address
    ColJ = Cells(myRow, 10).Address
    Cells(myRow + 1, 2).EntireRow.Insert Shift:=xlDown
    Range(colB).Copy
    Cells(myRow + 1, 2).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range(colH).Copy
    Cells(myRow + 1, 8).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range(ColJ).Copy
    Cells(myRow + 1, 10).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Thanks for all the great info. That code does work for me.. what I don't understand is the digits being used... colH = Cells(myRow, 8).Address for example - column H is the 7th column, why is it referred to as 8? - pardon my novice. The formula does work! I just need to get my formulas from column F&G to pull down as well and I finally have my dream realized! If I use the code above referenced, it doesnt pull a formula unique to those cells, just copies the exact formula above... what would bet he process for that?