Copy And Insert Rows From List To Ws Based On A Variable No
Dec 20, 2006
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:
1) Select and Copy entire row 2 from sheet(1)
2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable.
3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3.
4) loop through all records until complete
I have tried writing the code in VBA but am not even getting close!
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A B C D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
I am looking for macro that would do the following: I would run the macro while in a cell in row 7 (the row may vary if I insert more data). It will find all of the zeros in the column and make a single "mailto:" link that includes each corresponding email address in column B.
So if I ran the macro while in cell E7, it would produce a link in that cell such as "mailto:tom@hotmail.com" and if I ran the macro while in cell F7 it would produce the link "mailto:tom@hotmail.com;kate@hotmail.com".
I started tinkering with some code on my own but wasn't sure how to compile the results into a single link.
I have some code that uses a form so you can choose the folder that contains all the spreadsheets that I need to copy. My issue is I have about 20 spreadsheets, some have the tab labled M3 and others Sheet1. Next is each spreadsheet has a different amount of rows in them. My data will always start in cells B4:N4 but may 20, 100, 1000 rows long.
My code works but only copies the first four rows of the last sheet open.
Code:
Private Sub cmdbtnProcess_Click() Check_Path End Sub Sub Check_Path() If txtboxPath.Value = Empty Or txtboxFile.Value = Empty Then
Here is my problem. I have a a workbook with 2 sheets.
Sheet 1= Data sheet; Sheet 2 = Table
On sheet 2 I want to copy down an entire row (A8:AH8) but the problem is that the number of copied rows depends on the number of rows contained in sheet 1 column A (-1 row)
So if i have 101 records (100 +label) in column A sheet 1, in my sheet 2, it must copy down the formulas from (A8:AH8) until (A107:AH107)
I am trying to figure out how to automatically copy and insert rows based on the QTY number in column D. For instance, rows 2 and 3 are already correct since QTY=1, but since row 4 has a QTY of 5 I would want the formula/macro to then insert 4 rows above row 4 and paste the exact data that is in row into the inserted rows. I would want the macro to loop and do this for all the rows in the sheet until there are no rows with a QTY>1...
I am trying to finish off a form that allows a user to insert a row below the selected cell and copy the formula from the line above if one exists. The code inserts the line but does not copy the formulas if they exist.
Application. ScreenUpdating = False
Dim cRow Dim j As Long
cRow = ActiveCell.Row
With ActiveCell .EntireRow.Insert End With
For j = 1 To Cells(1, 255).End(xlToLeft).Column If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j) Next j
I deleted the data and made up some figures but kept the format to protect privacy. Highlighted blue and green because it's possible to have the same name have 2 different types. I put in dashed lines to more clearly divide months.
1. For each UNIQUE "Name" and "Type" add a new row starting from last entry 2. Copy A,B,C to new row 3. Copy LAST (most recent) "End Balance" from column "I" corresponding to last person 4. Copy column "I" formula into new role
So Name, ID#, type, column D and the formula in "I" (excluding "Name" and "Type" duplicates) should be added to the end of the sheet with their new row
Unsure if possible:
5. Make old amounts (columns D,F,H,I) not count towards the totals 6. Totals reflect new amounts only so values aren't counted multiple times
I currently have a spreadsheet with 4 columns that represent Surface corridinates and Bottom Hole Corridnates. Surface X, Surface Y, BH X, BH Y
We need to get the Bottom Hole XY to be directly under the Surface XY.
Attached is some sample Data and What I mocked up for a temp solution is in VBA Module2 RunAll it will complete 399 rows of data.
In Module1 I have the starting Code that I modeled off of a macro that I started to get the base code.
I just need this to loop till it has no more data to copy. and not use module2 at all.
I commented everything for my personal benifit and to help me understand the steps. also know its a monster right now but it does most of what I need it to do.
Sub CopyPasteBHXYs() 'Wrote Based on recorded macro 'Selects second data row and inserts a row above Rows("4:4").Select Selection.Insert Shift:=xlDown
'How to make it loop till the last data is reached?
End Sub
Side Note I'm finally taking a Excel VBA course Apr 8th and 9th.
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.
Exampl of data:
Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".
Column K is where the Data Validation List is located with a in cell drop down.
What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.
This would be so I can keep a historical of programs and dates taught.
And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.
I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.
Here is my problem:
I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)
Example of data (Where "/" delienates column seperation):
Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain
So what I need to do is this: 1) Find which data times are missing 2) Add the appropriate amount of rows in between where the missing data would be 3) Add the correct dates to the new rows 4) Add "N/A" to the columns that have no data
You can see that my date and time are formatted in mm/dd/yy hh:mm
I found this on the website and was trying to modify it to my needs:
I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).
The Data looks like this:
Column B Column C 12543 2 13456 2 19543 1
I want it to look like this:
Column A Column B 12543 12543 12543 13456 13456 13456 19543 19543
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00 X DAVE 43.93 DAVE 92.00 X FRED 4.66 X GREG 23.55 GREG 84.21 GREG 8.69 X MIKE 83.50 X SETH 41.33
Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.
For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example,
Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet.
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
I have a simple command button in my worksheet that will insert a blank row and copy formatting, but not the formulas. How do I adjust the code so that the formulas are copied to the new blank row? This is currently what I have:
Private Sub CommandButton2_Click() Sheets("WAWF Track").Range("A9").Select ActiveCell.EntireRow.Insert Shift:=x1Down End Sub
I need to insert one or two rows depending on the criteria of two different columns. We have two shops (A and B)...and the sales are expresed like this: ....
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time.
For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database() Dim No_Of_Cells As Integer Dim Start_Cell As Range Dim Cell As Range Dim LastRow As Range Dim i As Integer Dim lRows As Long Set LastRow = Range("C65536").End(xlUp) i = 0 Set Start_Cell = Range("C2") Set Cell = Start_Cell Do While Start_Cell.Address <= LastRow.Address Do While Cell.Offset(0, 1) > 0 i = i + 1 Set Cell = Cell.Offset(0, 1) Loop...................................
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................
With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
