Excel 2010 :: Macro To Insert Rows Based On Cell Value Using Button
Nov 10, 2013
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
I have a sheet that contains the following columns: Invoice, Document #, Date, PO #, Part #, Part Description, Quantity, Net Amount
Based on the quantity in the row I need to copy the row, and insert it n-1 times. So if the quantity is 5, I need to copy and insert the data below the original row 4 times for a total of 5 rows of data.
I plan on firing the macro with a button as the data will change month to month.
I have a massive spreadsheet (ca.110 000 rows [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.
Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:
"mytextascriteria*", where * somehow stands for the rest of the string.
Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.
I found the thread about deleting entire row based on a cell value here: Delete entire row based on one cell?
but I couldn't quite work out how to apply it in my scenario....
Otherwise is completely pointless to do it manualy with these amout of records.
I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
We have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
I am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.
Option Explicit Sub alex_it_saving_lives()
'get the image folder path Dim strImagePath As String strImagePath = GetImageFolderPath If (Len(strImagePath) = 0) Then
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet [URL] ..... Windows 7 (32bit) MS EXCEL 2010
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry() ' ' NEW_ENTRY Macro Dim rowNo As Integer
using excel 2010, I would like a macro that will insert a page break every time a row is blank. There are two blank rows between each data row which I need to be separated.
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID Name Program PMT SI ID AC Milestone TCP Level [Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.
calculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.
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 frequently work with a data set that has about 35,000 rows and have to split it up into smaller sets. I manually insert a row after every 8,000 rows and then copy each smaller set into a new CSV file. Is it possible to automate this with a macro?
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.
I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.
What I am trying to implement. I would have a drop down box in cell AD-4 with the current list
Select Week 1 Week 2 Week 3 Week 4
What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6
also
If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5
so basically hide the weeks that are after the current selected week.
In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).
(the first row is the current Row labels, I have a lot of current hidden columns).
A B J K O
Supervisor Name Agent Name Needs Complete Completed w/o Turn in Needs Turn In
I setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"
I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.
Example:
Rule1: [@Type]="Section" -> fat red line on top of cell. Rule2: [@Type]="SubSection" -> thin black line on top of cell. Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.
I can't seem to make this work.
How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B- 0--123 <-delete 0--123 <-delete 0--123 <-delete 1--123 <-delete based on this the value of column A 0--123 <-delete 0--123 <-delete 1--321 1--321 1--321 1--321 1--321
or
A---B- 0--123 <-delete 0--123 <-delete 1--123 <-delete 1--123 <-delete based on this the value of column A 0--123 <-delete 0--123 <-delete 1--321 1--321 1--321 1--321 1--321
I am looking for a macro that will remove any rows exceeding 12 months. Each month new data is added in and I am looking to remove anything over 12 months with the new month is added.
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
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.
