 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008insert rows in Multiple worksheet
Recently I’m working on creating an Excel worksheet which users need to insert row(s) under each record (row) in order to enter further information. The number of row(s) inserted equals to the round up value of, for example, column C (i.e. 2.1 becomes 3, 2.6 becomes 3, etc). Column C contains formulate calculates based on the value from column A and B in the same row. There is another worksheet in the same workbook extracts part of the info from the first sheet. Each row in this 2nd sheet associates with the row in the first sheet (each record from both sheets has and keeps the same row number). I’m trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! Create a command button from the Control Toolbox and paste this code in the button code window by right clicking the button and selecting View Code from the drop down menu. You will need to edit the code to ensure sheet names and sheet index numbers are correct. I suggest you create a copy of your official workbook to test this on. Sub CommandButton1_Click() lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set varRng = ActiveSheet.Range("C2:C" & lastRow) For i = lastRow To 2 Step -1 x = Cells(i, 3).Value If x > 0 Then With ActiveSheet .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With With Sheets(2) '<<<Change to suit .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With End If Next End Sub > Recently I’m working on creating an Excel worksheet which users need to > insert row(s) under each record (row) in order to enter further information. [quoted text clipped - 11 lines] > place but still not sure if this is feasible. Hope someone here can offer > some comments, thanks! thx for your response. My challengers are: 1. add row(s) at the right place with the right amount. Row(s) are added right beneath the active cell. The amount of row equals the round up value (i.e. 2.1 becomes 3; 2.6 becomes 3) of the active cell. 2. both worksheet have row(s) added at same place with the same amount I tried your code in my spreadsheet but cannot get the right result. > Create a command button from the Control Toolbox and paste this code in the > button code window by right clicking the button and selecting View Code from [quoted text clipped - 36 lines] > > place but still not sure if this is feasible. Hope someone here can offer > > some comments, thanks! > I’m trying to figure out an easy way for user to do this, like push a > button, to insert the required amount of row(s) in both sheets at the right > place but still not sure if this is feasible. Hope someone here can offer > some comments, thanks! HI, see if this helps: Private Sub CommandButton1_Click() Dim iRow1 As Integer, _ iRow2 As Integer, _ sRows As String iRow1 = 14 iRow2 = 17 sRows = iRow1 & ":" & iRow2 Sheets(Array("Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate Rows(sRows).Select Selection.Insert Shift:=xlDown Sheets("Sheet3").Select Range(Cells(iRow1, 1), Cells(iRow1, 1)).Select End Sub Saludos Jaime Vasquez thx for your response. Maybe I didn't make myself clear. My challengers are: 1. add row(s) at the right place with the right amount. Row(s) are added right beneath the active cell. The amount of row equals the round up value (i.e. 2.1 becomes 3; 2.6 becomes 3) of the active cell. 2. both worksheet have row(s) added at same place with the same amount > > I’m trying to figure out an easy way for user to do this, like push a > > button, to insert the required amount of row(s) in both sheets at the right [quoted text clipped - 22 lines] > Saludos > Jaime Vasquez thx Jaime, I figured it out. > > I’m trying to figure out an easy way for user to do this, like push a > > button, to insert the required amount of row(s) in both sheets at the right [quoted text clipped - 22 lines] > Saludos > Jaime Vasquez |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.