Solved

insert rows and data from above cell

Posted on 2014-12-15
3
90 Views
Last Modified: 2014-12-15
I use the below code provided by Rgonzo to insert cells and the data from above cell including tick boxes which works fine.

However it inserts an entire row and I now need to only insert from A-W and do not want any cells from W >>>> onwards to be impacted.

Can someone provide me with VBA code that will do this please.

Sub Insert()

theRow = InputBox("Enter Row Number where new row is to be inserted ")
Count = InputBox("how many rows ?")

If IsNumeric(Count) And IsNumeric(theRow) And Count > 0 And theRow > 0 And theRow <= Rows.Count Then
    Cells(theRow, 1).Resize(Count, 1).EntireRow.Insert Shift:=xlShiftDown
    a = Cells(CLng(theRow) - 1, 1).Address
    Cells(CLng(theRow) - 1, 1).EntireRow.Copy Destination:=Range(Cells(theRow, 1), Cells(CLng(theRow) + CLng(Count) - 1, 1))
Else
    MsgBox "You didn't enter a valid number!"
End If

End Sub

Thanks
0
Comment
Question by:Jagwarman
  • 2
3 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40500621
Change

Cells(CLng(theRow) - 1, 1).EntireRow.Copy Destination:=Range(Cells(theRow, 1), Cells(CLng(theRow) + CLng(Count) - 1, 1))

to

Range("A" & CLng(theRow) - 1 & ":V" & CLng(theRow) - 1).Copy Destination:=Range(Cells(theRow, 1), Cells(CLng(theRow) + CLng(Count) - 1, 1))
0
 

Author Comment

by:Jagwarman
ID: 40500671
Thank you Martin
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40500679
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 44
VBA Fill Blanks with text from another cell 6 23
sumifs excel 2013 3 16
Excel match value & copy data 2 13
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question