+ Reply to Thread
Results 1 to 1 of 1

Insert rows fill formulas but not pictures

Hybrid View

  1. #1
    Registered User
    Join Date
    11-12-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Insert rows fill formulas but not pictures

    I foun this macro to insert rows and fill formulas, but it copies a picture that is on the previous row , and i do not want copy the pic. i appreciate if some one can help modifyng the following code to get worked

    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 <markhill@charm.net.noSpam>
       ' 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).SelectedSheets
        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
    Last edited by pike; 11-12-2011 at 02:20 AM. Reason: add code tags for newbie

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1