Insert rows and paste without clearing constants

I need to Insert Rows and Paste the selected row a specified number of
times per an input box value.  I am using the following code as a start
and have commented out the lines to clear the constants and that works
fine except the autofill portion increments the constant values.
Thanks in advance for any help.

This is how I need the result to be:
Room		Part#
6		WN1B-24
6		WN1B-24
6		WN1B-24
6		WN1B-24
6		WN1B-24
6		WN1B-24

This is what I am getting:
Room		Part#
6		WN1B-24
7		WN1B-25
8		WN1B-26
9		WN1B-27
10		WN1B-28
11		WN1B-29

Sub CopyRows()
' 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
    Range("A65536").End(xlUp).Offset(0, 0).Select
   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 Integer
   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

0
plantechbl (18)
11/24/2006 11:49:56 PM
excel 39879 articles. 2 followers. Follow

1 Replies
486 Views

Similar Articles

[PageSpeed] 2

I think I got it:
I changed the Autofill from:
xlFillDefault to xlFillCopy

Thanks for the original code, it works great!


plantechbl@earthlink.net wrote:
> I need to Insert Rows and Paste the selected row a specified number of
> times per an input box value.  I am using the following code as a start
> and have commented out the lines to clear the constants and that works
> fine except the autofill portion increments the constant values.
> Thanks in advance for any help.
>
> This is how I need the result to be:
> Room		Part#
> 6		WN1B-24
> 6		WN1B-24
> 6		WN1B-24
> 6		WN1B-24
> 6		WN1B-24
> 6		WN1B-24
>
> This is what I am getting:
> Room		Part#
> 6		WN1B-24
> 7		WN1B-25
> 8		WN1B-26
> 9		WN1B-27
> 10		WN1B-28
> 11		WN1B-29
>
> Sub CopyRows()
> ' 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
>     Range("A65536").End(xlUp).Offset(0, 0).Select
>    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 Integer
>    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

0
plantechbl (18)
11/25/2006 12:06:18 AM
Reply:

Similar Artilces:

Autofit Row Height #3
Hello, I have a column on sheet 1 that is set to wrap text so that the row height increases and decreases as more text is entered into the cell. This works fine. Users enter a number on sheet two where there is a lookup function that returns the appropriate text from sheet 1. The problem is that when the text is returned by the function, the row height does not adjust to fully display all of the text in the cell. Is there a way of automating this? Any help would be appreciated. -- Thanks, MarkN ...

Deleting Non-Duplicate Rows
Have done tihs is the past, but can't remember how: Have a sheet with 9500+ rows. Column C contains a storage bin number. Want to delete all rows that DO NOT have a duplicate (trying to resolve items that have a duplicate bin number.) Have sorted the sheet on Column C. THX. . . -- BillW ------------------------------------------------------------------------ BillW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27516 View this thread: http://www.excelforum.com/showthread.php?threadid=470299 You could use a helper column of formulas: =countif(c:c,c1)...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

inserting
How do you insert more than one row at a time? Hi just select some rows before inserting rows -- Regards Frank Kabel Frankfurt, Germany "aSussertown" <DROPsussertown@adelphia.net> schrieb im Newsbeitrag news:roednbCg9PBJpA_cRVn-tg@adelphia.com... > How do you insert more than one row at a time? > > Select as many rows as you wish to insert by clicking on the row number and dragging down. It doesn't matter if the rows have data or not. XL will count the selection and insert that number of rows, starting at the row number you first clicked on. -- HTH, RD =...

Excel
Excel is the only application that I use in Windows that wipes out the clipboard after I paste. I often need to past the same information multiple times and I wishthere was an option box that allowed me to keep the clipboard after every past ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&quo...

copy/paste
I used to be able to right click to copy paste highlited cells, now nothing comes up when right clicking See your other thread -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Garland" <MichaelGarland@discussions.microsoft.com> wrote in message news:1F256097-EB66-4C95-A42A-EF567E30143E@microsoft.com... >I used to be able to right click to copy paste highlited cells, now nothing > comes up when right clicking ...

How to only "paste values" of cells that are not "hidden"?
Hello, In my document, many columns are hidden. Say column B is hidden, and I need to copy columns A and C and paste values into another Excel document. How can I do that? When I select columns A and C, copy and paste values, the other document contains columns A,B and C, instead of just columns A and C. Thank you! Don't drag-select, control-click A and then C, for scattered-selection. Danny On Sun, 17 Jul 2005 16:33:01 -0700, Sam <Sam@discussions.microsoft.com> wrote: > Hello, > > In my document, many columns are hidden. Say column B is hidden, &g...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

can it be done without notice?
Hi, The below code (which was given in this NG + With some minor alterations by me) intends to check if anything is changed on column J then email that changed info to a spesific email address without the notice of the workbook user... But it has two problems: 1. If Outlook Express is not opened beforehand, starting of OE is visible and the user recognizes that something without his/her control is going on. 2. If Outlook Express is active before the execution of the macro, yet again a small instance of mail being produced is visible on the screen. Can anyone suggest solutions for the 2 proble...

Problem with copying and pasting tasks and keeping predecessors in
I am doing a consulting project for an organization that has a bit of an unusual request. We are doing a conversion from an old mail system to Microsoft Exchange 2007. We have developed a checklist for the technicians to follow with about 50 detailed tasks (things like, "Open up Exchange Management Console", "Assure the 'Forward' Button is unchecked", etc.). The CIO expects me to enter each of the tasks from the checklist into the project plan. Instead of having a line on the project plan with a task, "Covert Leroy Smith's Mailbox" wi...

Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...

conditional formating: ifs and highlighting rows
my spreadsheet documents error incidents, with each row showing the date the incident was discovered (column A) and the date it was resolved (column I). it also calculates networkdays (column J) -- unless column I=0 -- and references an array of holiday dates on another sheet. i would like to create a conditional format that will identify rows with an incident, but no resolution date, then highlight the row and possibly even show "unresolved" in column J cell of that row. Thanks! Well, you have to decide which way you want to go with this. If you leave column J ...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr´┐Żm Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

Is there a way to send cc or bcc without atachment?
Is there a way to send cc or bcc without atachment? Aloha diter, I'm not clear on what your question is. You don't have to add an attachment to any message. What version of Outlook are you using? What, exactly, are you trying to do? -Ben- Ben M. Schorr - MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm > Is there a way to send cc or bcc without atachment? > "diter" <diter@discussions.microsoft.com> wrote in message news:39727460-BD7C-4ACD-B988-27DE76CD7268@microsoft.com... > Is...

inserting object
i try to insert pdf file to my excel file in Insert>Object>Create from file>Browse the pdf file there an message appear when opening the inserted file-"Cannot start the source application for this object" What does it means? Do you have Adobe Reader install on your machine. If not that is wha the message is taking about. The insert object needs the applicatio that created or that can read the file you are attempting to insert. HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.ph...

Pasting "Character Spacing" adjusted text
Our church office secretary has the Bible on a CD in a Word document. They have used Format>Font>Character Spacing>Position >Raised>by 2.5 rather than "Suberscript" for all the verse numbers. Is there a way to Copy the text and paste it into Publisher to maintain the appearance of the raised verse numbers? I do not see the Character Spacing formatting features in Publisher. We are using Office 2002. Jim wrote: > Our church office secretary has the Bible on a CD in a Word document. > They have used Format>Font>Character Spacing>Position >Raised>...

insert a link/shortcut to a file on comuter in a contact window
I am trying to add a link or shortcut to a file on my computer in a contact window, in the lower window of the contact When I drag a file in that box it actualy creats a copy in a certain folder in Microsoft, but then its hard to find the file I want to Insert only a Link or shrtcut to the file. Can anyone have an idea? pl;ace your cursor in the white window of the Contact. Then Insert | File, single click the file you want and use the tiny drop down arrow, bottom right corner to select: Insert as Hyperlink. "Jacob" <info@kohn.ca> wrote in message news:O5g%23hYD2G...

Inserting word 'merge field' into Excel
I have data in Word merge fields that I need to put in an Excel table. Each Word file is exactly the same and there are a few hundred files. If I can't do this direct to a spreadsheet, can I do it through Access? ...

cut and paste #2
Working in v.2003 when I cut a phrase or sentence from another doc either word or a web page, many times when I paste it comes with the text boxes. How do cut and paste without these boxes being transfered to the publisher doc? Place the cursor in the text box, then paste. If you simply paste a new text box is created. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "misskitty" <misskitty@discussions.microsoft.com> wrote in message news:92D49CEA-A891-4320-9867-88C9004A17D6@microsoft.com... > Working in v.2003 ...

Using SqlDataAdapter for Insert but not Update?
I'm having trouble getting my SqlDataAdapter to NOT overwrite existing records in my SQL table. I'd like it to perform the Insert on rows that don't exist, but not update the record if it already exists. I have it set to ContinueUpdateOnError because I don't want it to throw an exception if a row already exists, I just want to ignore it. I'm clearly missing something... On Dec 11, 4:46=A0pm, Stu <stumor...@gmail.com> wrote: > I'm having trouble getting my SqlDataAdapter to NOT overwrite existing > records in my SQL table. =A0I'd like it to pe...

Pasting subtotals only
How do I paste subtotals only of a list into another worksheet without the background data? Adjust the subtotals to your liking, select the range, press F5 and select special and visible cells only copy and paste -- Regards, Peo Sjoblom "Greg glenn" <Greg glenn@discussions.microsoft.com> wrote in message news:C55190AD-4F52-4132-9588-615DA1F19C66@microsoft.com... > How do I paste subtotals only of a list into another worksheet without the > background data? ...

copy/paste into excel changes numbers into date format
I need to copy a lot of raw data from html format into excel. Most of the data consists of a min/max figure (eg. 31/45) Excel (2003) always converts this number to date format no matter what I format the cells as. I have been unsuccesful in converting back to the same format. Any help or advice is appreciated. Thanks Hi try formating the cell s as 'Text' before pasting the values -- Regards Frank Kabel Frankfurt, Germany Rufus wrote: > I need to copy a lot of raw data from html format into > excel. > > Most of the data consists of a min/max figure (eg. 31/45) &...

how do i clear the Mailroot/queue?
Am running an Exchange 5.5 system on a Win NT 4.0 box, and have been experiencing a problem with the mailroot/queue folder that involves large quantities of email sitting "dormant" in the folder. Most of the email is spam, however, about 10% of the messages are legitimate, so it's a little frustrating to see them just sit there and not be processed by the exchange server. How can the exchange server be "coaxed" to deal with these emails? There are about 750 emails in this folder. Thanks in advance for any advice on how to fix this issue. wayne Hi Wayne, Exchange...

How to stop window constantly moving?
Does anyone know if there is a way to stop the window in SO manager moving. For example, if you search and find 10 items, when you view the properties of the item, click OK, then view the next one the window actually moves. It seems the window moves in a pattern and after about 10 times it repeats. I wonder why Microsoft made it do this, as I've never seen this another program. The reason I'm asking is that I'm trying to set up a macro to create new items from an excel spreadsheet and because the window keeps moving I can't loop the macro as the mouse clicks on the wrong ...

Pasted part of what I copied
I copied text from word, pasted it into a text box, but only one line will paste. DeborahKB wrote: > I copied text from word, pasted it into a text box, but only one line will > paste. Never heard of such a thing! Be sure that when you paste, go to Edit > Paste Special > Unformatted. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. ...