+ Reply to Thread
Results 1 to 2 of 2

Using Input Box to Copy and Insert Rows

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Israel
    MS-Off Ver
    Excel 2010
    Posts
    2

    Using Input Box to Copy and Insert Rows

    I'm looking for a way to use an InputBox to ask the user how many of X objects they have. If the answer is 4 for example - the macro should take action and select a certain range like A15:A20, copy those rows and then insert them as many times as chosen in the InputBox below that range. In this case it would insert 20 Rows starting from A:21 and preferably leave a space between each one.

    Anyone can help me with this?

    Thanks!

  2. #2
    Registered User
    Join Date
    05-30-2013
    Location
    Israel
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Using Input Box to Copy and Insert Rows

    Someone helped me out. Hope this helps someone.

    Sub SetMoreObjects()

    Dim InptRng As Range 'is a range to be copied
    Dim AddObj As Variant 'is a number of InptRng copies
    Dim LastRwNo As Double 'is last row in InptRng
    Dim InptRwCnt As Double 'is a number of rows in InptRng
    Dim i As Double

    On Error Resume Next
    Application.ScreenUpdating = False

    Set InptRng = Range("8:20")
    AddObj = InputBox("How many object portfolios to add?", , 1)
    Range("B7").Value = Application.UserName & " added " & AddObj & " objects"
    InptRwCnt = InptRng.Rows.Count
    LastRwNo = InptRng.Row + InptRwCnt - 1

    If Not IsNumeric(AddObj) Then
    MsgBox "Entered value must be a number.", vbCritical
    Range("B7").ClearContents
    Exit Sub
    Else
    If AddObj <= 0 Then
    MsgBox "Number must be positive., vbCritical"
    Range("B7").ClearContents
    Exit Sub
    Else
    For i = 1 To AddObj
    InptRng.Copy
    Rows(LastRwNo + 10).Select
    Selection.Insert
    Application.CutCopyMode = False
    Next i
    End If
    End If

    Range("A8").Select
    Set InptRng = Nothing
    Application.ScreenUpdating = True

    MsgBox AddObj & " object portfolios successfully added below. Please fill in relevant information", vbInformation

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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