Macro to insert rows based on user selection

I have a macro that allows a user to insert one or more rows based on
a user input box.  This allows me to control which formulae get copied
into the new cells.

The macro works fine UNLESS the user scrolls around the screen before
making their selection.  How can I resolve this?

This is the key part of the script.



Sub Row_Insertion()
' This macro inserts a user-specified number of rows
' and ensures that the relevant formulae are copied
' into the new rows.

    Range("I3").Select  ' Makes I3 the active cell and
    Set rng = Nothing   ' clears any selection made by the user

    Application.ScreenUpdating = True   ' Allows the screen to refresh
while the user is selecting a range

    On Error Resume Next    ' This prevents the macro from stopping if
an error occurs

    'This Input Box requires the user to select the row(s) where they
want rows to be inserted

    Set rng = Application.InputBox(prompt:="Select the row number(s)
at the point at which you wish to insert rows. " & vbNewLine &
vbNewLine & _
    "Click on OK and the rows will be inserted " & _
    "immediately above that point.", Title:="Inserting a row",

    Application.ScreenUpdating = False  ' Stops the screen refreshing
while the macro is running

    ' If no range is selected by the user protect the worksheet and
end the macro

    If rng Is Nothing Then
        Exit Sub

    End If

    rng.Select  ' select the range chosen by the user

    If Not Intersect(ActiveCell, Range("A1:IV5")) Is Nothing Then   '
Check to see if the user has selected in the
        MsgBox "You cannot insert a row in this area!"              '
header area (rows 1-6) and end macro if so.
        Exit Sub


    ' If a valid selection has been made insert the appropriate number
of rows and then
    ' copy the relevant formulae into the inserted rows.  The formulae
copying is done one column
    ' at a time.  Hence the multiple copy/paste commands below.

    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow

    Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select

1/21/2010 11:48:46 AM
excel 39881 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 56


Similar Artilces:

HOW: Click Formula Hyperlink To Trigger Macro
I have a column (Column 6) where every row contains an IF() formula that results in a HYPERLINK() formula if the previous cells in that row each contain data. In other words, a hyperlink shows up if the row is filled out. If the hyperlink shows up, and the user clicks it, I want a macro to be triggered. I want to trigger a macro that draws upon the data from the other cells in that row. My first discovery was the Worksheet_FollowHyperlink event. It seemed perfect. It triggers when a Hyperlink Object is click and captures that location of the cell where the hyperlink was clicked....

shape remains selected
Based upon user selections, I delete and copy in flags and logo's in a chart. Somehow, in some charts, the shape I copied in remains selected. This is really no problem in Excel 2003 running under Vista, neither in Excel 2007 running under Vista, but it is in Excel 2007 running under XP. Charts become totaly corrupted and unpresentable. Code used : ActiveSheet.ChartObjects("Graph02Base").Activate ActiveChart.ChartArea.Select ActiveChart.Shapes(LogoOld).Select Selection.Delete Sheets("Data availability").Shapes(Log...

Moving totals of rows in sort mode
I am to prepare the accounts ledger in my database of vouchers by sorting on account code. When I drag the formula of totals of + debit - credit column with the same coloumn total in immediately DISPLAYED ROW ABOVE THE FORMULA CELL row, it do not show the desired result as the cell refernce of DISPLAYED SORTED ROWS is not automatically linked while dragging the formula downward. Any solution to make the ledger totals on right side of every transaction (row). Thanks Riaz "rz" wrote: > I am to prepare the accounts ledger in my database of vouchers by > sorting on account code...

Macro Help needed #2
I'm trying to record a macro but I'm having difficulty with a particular action. I'm trying to copy text from a particular cell, Lets say Cell A1, and then go to the "Sheet Tabs", currently labeled Sheet 1, and paste the text to rename the sheet. Can Someone tell me what I'm missing from the following macro to make this happen: Sub Macro() ' ' Macro Macro ' Macro recorded 8/18/2005 by Anthony Amato ' ' Range("A1").Select Selection.Copy Sheets("Sheet 1").Select Sheets("Sheet 1").Name = " &qu...

Link row to Column
I have a spreadsheet that has multiple tabs. I want to have a row of data be linked to a column on the other tab. For example: A1 linked to A1 B1 linked to A2 C1 linked to A3 D1 linked to A4 Is there an easy way to transpose the link so that I don't have to manually create the link for each one? I have about 70 or so and growing. Try: Cell A1: =INDIRECT("Sheet2!A" & COLUMN(A1)) and drag across the columns Regards Trevor "Aaronous" <> wrote in message

macros run twice
Hi - I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems. 1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops. 2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice. My specific question is about no. 2 and the following are the individual macros and the macro that runs them. Sub Store_Data_to_ValueSheets_Part1() '''''This macro copies values from sheets 1, 2, 3, 4...

Selecting from previous customer purchases in SOP
Hi When entering sales transaction line items, I would like to open a window and select from the Items previously sold to the selected customer. This would show Tran Date, Order No, Item ID, Description and qty sold. Is anyone aware of a module or utility to provide this or would I need to programme this in Dexterity of Modifier with VBA. Many thanks Rod Hi It looks like the Trinity Myridas Customer Templates module will do the job very well. Thanks Rod "rodk" wrote: > Hi > > When entering sales transaction line items, I would like to open a window > and...

macro with wildcards
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I'm trying to create a macro that will fatnd all characters,words, lines or paragraphs the are grouped between two brackets. I've tried [*], but get no results. In article <59bb65ef.-1@webcrossing.JaKIaxP2ac0>, <> wrote: >Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel >I'm trying to >create a macro that will fatnd all characters,words, lines or paragraphs >the are grouped between two brackets. I've tried [*], but get no resu...

Looking for function to pick out names and at values from the same row
Say I have 3 Rows: Income | Expanses | Memo | 10.00 | Food | 5.00 | Gas | 15.00| Food Now I'm looking for a function that searches for a string such as "Food" and then add all Values of the Expanses Tab of this certain tab, so that all values of Food will be added automatically so that the result of this would be 25.00... I would really appreciate any help -- johnsvolition ------------------------------------------------------------------------ johnsvolition's Profile: View this ...

Delete Macros Macro
I have some xl files that have many macros in them. For example 20 xl files with 10 macros in each file. I have the following macro in it's own xl file that I use to delete the macros in the other files before I save the xl files in another directory. Sub zzzDeleteMacros() Dim vStop As Double vStop = 0 Do Until vStop = 50 Application.SendKeys Keys:="%tmm", Wait:=False Application.SendKeys Keys:="%D", Wait:=False Application.SendKeys Keys:="Y", Wait:=False vStop = vStop + 1 Loop End Sub This works fine exce...

If I delete/remove a macro after the formula's have used the macro to tabulate calculations, the next time a go into the file will the amounts still be there? Or does the macro need to be in place at all times? If it's a macro that places values (or builtin formulas) into cells, then you don't need the macros. But if it's a UserDefinedFunction macro (and there are cells that use that formula), then you'd need that code. (Or convert those formulas to values.) And when you're cleaning up the macros... Debra Dalgleish has some instructions: http://www.contexture...

multipe users, sees all emails
I have a Windows 98 computer with Outlook 2000 running and I have three different users setup on that computer under control panel-users. But when one receives a message, they all can see it. Example (user #1 is logged in, gets a mail message, he goes to lunch, user #2 logs user #1 out and logs user #2 in and checks his mail, he sees user #1 message) Why is this? please help. Thanks. Candy <> wrote: > I have a Windows 98 computer with Outlook 2000 running and I have > three different users setup on that computer under control > panel-us...

Address Book selection closes Outlook? Please Help
I'm using Outlook 2000 Pro with Windows XT. When you open the address book, then select u,v,w,x,y,or z, Outlook immediately closes. PLEASE HELP! Thank you. .. ...

Transfer Macro in Excel
I am trying to transfer informtion in one sheet to another sheet in the same workbook. I have a macro to process this information from the first sheet to the second. I keep getting an error in this Process macro at the line. Application.Goto REFERENCE:=Range("END") The first sheet is called ProblemSheet and the second sheet is called Processed. The line above is taken from the process macro on the ProblemSheet page. Do I have to name a certain cell as "END" on either sheet? Any help with this would be really appreciated. --- Message posted from

workbook macro help
Hello there, I would like some help with a macro that I need to run in a workbook. this is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Columns("A:Z").EntireColumn.AutoFit End Sub what or how do I get it to work for every worksheet in the workbook, I currently place the macro in each worksheet which is very time consuming. Is there a way to place the macro in the workbook so all worksheets have the autofit macro? and can I single out a worksheet so as not to have the macro. regards Ditchy Ditchy, Probably better to use the Change event than the Sel...

Changing settings on several user-defined table styles in one go
Dear Experts: below macro alters the Background color settings for odd rows on a user-defined table style (Word 2003). I got two (2) other user-defined table styles (User-Defined-Table- Style-normal and User-Defined-Table-Style_academic) which also should acquire this altered background color settings. The stlyes have been created independently from each other, that is, no style is based upon another. So, my QUESTION is: how do I have to alter the code so that all the user-defined table styles get the different background pattern color in one go? I could copy the macro code...

Macros #12
How do I get a mcro to run at a certain time during the day? Take a look at Chip Pearson's notes: Davo1 wrote: > > How do I get a mcro to run at a certain time during the day? -- Dave Peterson See replies to your other similar post in Excel.Misc news group. Gord Dibben MS Excel MVP On Thu, 20 Aug 2009 13:08:01 -0700, Davo1 <> wrote: >How do I get a mcro to run at a certain time during the day? ...

How do I change macro text with another macro?
Hello, I have a set of workbooks that all have a similar macro in them. Each of these macros contains a string I need to change, but to change it manually in each file is time prohibitive - 60+ files. Given I already have one of these files open with a seperate macro, from the seperate macro, how do I access the module - say Module1, search for the line that contains the text needing to be changed, change it and then close the file? I already have the list of files and the loop to go through all of them. I am just unclear on how to change the Module1/Macro text from another macro. Any ide...

Font for column letters and row numbers
Without notice, Excel now displays my column letters and row numbers in Arial Italic font. But worse is the fact that every workbook I open now has most cells displayed in Arial Italic font. If I select an entire worksheet and press the Italic button in the formatting toolbar, nothing happens. If I insert a new worksheet, data entry default ti Arial Italic. The default font on the General tab in the Tools Options dialog box indicates that Arial is the default font. The list of fonts that appears when you click the Font button on the Formatting toolbar includes the word Arial, but it&...

Exchange User Properties on Exchange 5.5
For about 10 minutes none of my client machines could access the properties for the exchange server (i.e. to change the server or client settings for authentication). I do see any errors in the event log. Are there any processes that run that can cause such a problem. At about the same time I did see the database was defragging. Thanks <> wrote: >For about 10 minutes none of my client machines could access the properties >for the exchange server (i.e. to change the server or client settin...

Relocating a macro
I accidentally created a macro in the worksheet I was working on at the time instead of in PERSONAL.XLS. I have tried to move or delete macros before and gotten annoying messages afterward when I opened the original file saying "VBA Error!" etc, etc. In some cases, XL couldn't find the macro after I moved it. What is the correct protocol for moving a macro from a regular spreadsheet to the PERSONAL.XLS spreadsheet without causing complications? TIA Hi Dave, Perhaps you could indicate a bit better what you actually did. If you are running a macro from Alt+F8, even though yo...

Inserting a complete page from one document to another
I am trying to cut a page from one document and pasting it to another document. Any help would be approeciated What problems are you having? Usually it is a straightforward copy/paste. If you want the entire page, edit, select all, copy/paste. -- Mary Sauer MSFT MVP news:// "RK" <> wrote in message >I am trying to cut a page from one document and pasting it to another > document. Any help would be approec...

Run multiple Macros or macros from within other macros ?
I would like to run multiple macros at startup. What code should be used in the Private Sub Application_Startup command in the ThisOutlookSession area ? If the names of my macros were below what would the code to look like ? Macro1 Macro2 Macro3 I have looked at the samples from previous posts and cannot determine how to make them apply specifically to my needs. In Excel you can use the Run "Macro1" line and it would run that macro. Run does not work in Outlook I suppose because I kept getting an error. **** Also - What code is needed to execute a macro from w...

Converting Word macro for the Mac
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi everyone, <br><br>I'm looking for a way to combine multiple Word documents into a single document without copying and pasting or using the &quot;Insert File&quot; command. I work as an editorial assistant at a publisher, and this kind of macro would make creating files for the Library of Congress CIP program much faster! <br><br>I found a macro online (here: <a href="">http://www....

Service Selection Rules ERROR
When I am in the service pane and I try to schedule a new service activity using one of our users I get the error message " Service selection rules for required resourse "User name" cannot be met. To see selection rules open the record for this service". It won't let me save and apply And I am lost as to were to go from here. Please help I am still so new to this. Thanks -- Heather hi Heather You need to go to services and then click the servict activity you are trying to use and make sure the user you are trying to schedule this for exists as a resource in th...