[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

insert rows and data from above cell

Posted on 2014-12-15
3
Medium Priority
?
101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 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