I have two sheets, they basically contain shifts. They are laid out identically the only thing that changes is the date along row 1. In order not to have to recreate the workers from sheet1 onto sheet2 I use the following
View 2 RepliesI am attempting to duplicate data from the first cell in each row in Sheet1 to the first cell in 6 rows in Sheet2.
I want to do this for each row in Sheet1. For example, I want to take cell A1 from Sheet1 and copy what was in that into Sheet2 into cells A1-A6. Then I want to take A2 from Sheet1 and copy what was in that into Sheet2 into cells A7-A12.
Is there an easier way to go about doing this? I have too many rows in Sheet1 to do this by hand.
Edit: I know the formula for getting a value from Sheet1 and put into Sheet2 cells A1-A6: =(Sheet1!A1)
I'd like to create a variable list. If a number is entered into B7 (see attachement), I would like that number of rows being inserted below. Starting at A8. But I would like the list to be variable. So if a lower number is entered, the rows would be deleted. And I would be great if the formulas could be maintained.
View 5 Replies View RelatedI've a protected sheet with one table with 6 columns and 1 row (to be filled), and the column headers.
I want to allow the user that will fill the sheet to insert more rows with a button (+) I want also to allow him to delete the last row with a button (-), except the first row, because the table must have at least one row.
How can I do this with vba code?
I have columns 1 to 5 that requires user input. At the beginning of first row, i have created two buttons +R to insert row and -R to delete row. I used macro recording to get this done. It works well but I need both buttons to be copied on for other rows as well. Let me re-explain, when a person clicks on +R in row 10 I need a new row inserted at row 11 which both the buttons +R and -R copied in. When a person clicks on -R at row 10, i want row 10 to be deleted. the first default should never be deleted. All rows should be added before statistic as statistic row will calculate all the inputs from user start to finish. Can this be done?
123456+R
-R
Statistic: PLS refer to this diagram
12345+R,- R
Statistic: ignore this diagram
I even tried http://www.mvps.org/dmcritchie/excel/insrtrow.htm but it is not working. I am not sure where I am doing wrong.
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies View RelatedI need to change the colour of cells depending on contents. The following code works perfectly until I try to insert or delete a row in the worksheet. Then I get Run-time error 13, Type mismatch.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("E5:E100"))
If Not I Is Nothing Then
[Code]....
I need to work out a total of column A7:A39. Even with my lack of excel knowledge, I can do that with the sum formula. But, each week, I need to delete the values in A39 by deleting that row, and inserting a row into A7 and putting different data in.
I have my $ signs in place to make sure the formula relates to that column only, but when I insert the row at A7, the formula moves to A8:A39 not A7:A39. Basically I need to be able to delete and insert but still be able to view the sum of A7:A39 at all times.
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
Account Number
211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.
View 9 Replies View RelatedI have a worksheet that has been created to essentially request information needed to determine a demo system configuration. Within this worksheet there are a series of checkboxes that if checked, will need to have the form extended (i.e. add additional questions to clarify say a model or manufacturer). For example, if the Yes checkbox is checked next to a question I want a macro to copy some rows from a hidden worksheet within the same workbook to the bottom of the non-hidden worksheet. If the No checkbox is clicked, nothing should happen. Furthermore, if a user accidently click Yes but didn't mean to I want the added rows to be removed. This is further complicated by the fact that there are 2 or 3 questions that have check boxes that can add additional questions.
View 9 Replies View RelatedIs there a simple way to duplicate a number according to a given value? For example, i want the input to be 8 and the number is 1 so the output would be 11111111.
View 3 Replies View RelatedI've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.
So in the end it would go from: (sorry for the periods.. extra spacing didn't work!)
A........B................C.......D
AAA...123 Street...City...<blank>
BBB...456 Street...City...<blank>
CCC...789 Street...City...<blank>
To:
A........B................C.......D
AAA...123 Street...City...1
AAA...123 Street...City...2
AAA...123 Street...City...3~
AAA...123 Street...City...150
BBB...456 Street...City...1
BBB...456 Street...City...2
BBB...456 Street...City...3~
BBB...456 Street...City...150
CCC...789 Street...City...1
CCC...789 Street...City...2
CCC...789 Street...City...3~
CCC...789 Street...City...150
I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it
I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.
I am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;
Product
Date
Previous Total
Amount Taken
New Total
and occasionally there is a Re-issue of stock to the previous total.
This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.
The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.
I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.
I have attached a spreadsheet here showing when people have had squash coaching.
What I would like to do is have a summary section at the bottom showing how many sessions each person has had throughout the month, at which club.
So where a club code such as the "C" "N" or "NO" is used I would like to insert the person's name in the summary section at the bottom.
If the same person's name occurs again I would like to simply (that's the hard bit for me!) use one cell to continue to count his number of sessions - rather than duplicating his name over and over in a list, as would be done by using COUNTIF.
I'm well lost on how this might be done but am sure that it must be possible?
I am working on a contact information form for given relationships, and I'm trying to figure out the best way to design a button that copies the "form" (not an actual VBA form but a grouping of formated cells for information collection) and inserts it into the document. I would also like a button that deletes unused and empty forms so that we only print forms that are filled in.
I tried recording a macro to do it, but it's messy and creates issues with hidden rows and pagination.
I have a button (group containing and add and delete button).
I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row).
Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row.
This will allow me to add/insert rows by the button located at that row
The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp)
I also note that when a group is copied, it has the same shape name as that copied.
I want to keep the add and delete shape within the group (BtnGrp).
I do not want to select a cell or row or enter a row number to delete etc.
refer to sample workbook attached. Currently only has one record row.
I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.
If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End
ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If
Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.
I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "= SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.
View 9 Replies View RelatedI have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.
There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.
I am in need of duplicating a current workbook for others use such that when I update information in the original all those who have a copy of the duplicate will get the updates I have made. I have searched all over for linking workbooks together and I am familiar with linking cells and other small bits of information from other workbooks but in this case I want a duplicate workbook that is updateable from my source workbook.
View 5 Replies View RelatedI'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it.
The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names.
Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?
I'm trying to automate some documents we use on a regular basis so that we don't have to re-enter the same information over and over again. Basically I can get some cells to work and some will not. My first sheet is a contract. So I want Customer and Contractor info to show up on the rest of the sheets. I'm assuming something is messed up in the formatting of the cells but I can't figure out what.
So on my Job Book Cover Sheet I first want the GC's name and the code =Contract!G8 works perfectly. In the cell just under this one I want the Customers Name, but =Contract!C8:D8 (the cells on the contract with the Customers Name) returns #VALUE!
That said, the code =Contract!C8:D8 on my project detailer sheet returns the customer name like I want it to.
What exactly do I want to count?
I want to count how many unique serial numbers there are for a particular production error?
For example
Column A = Serial Number (which is too many are same) so won't the duplicated.
Column B = Error Message "Error"
Which formula can count's how many serial with error was encountered on the summary sheets.
The following code was supplied by Bill, but I want to do the same thing in B10:B164, where "x" is entered in B10 and the time stamp is entered in C10 & D10.
I tried copying the same script but had an Compile error message which said Ambiguous name detected: Worksheet_Change. The name of the sheet is Sheet 1 (Main)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("M10:M164 ")) Is Nothing Then 'change your range on this line"
If Target.Value = "x" Then
Target.Offset(0, 1).Value = Now
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
I'm trying to track information pertaining to employees across different worksheets in one excel file. I have one sheet that is the master list of employees. The first column of every worksheet is the same (employees names based on their location) but track different information.
I want to be able to create a macro button that will update all the worksheets if i insert or delete an employee from the master list. When a new row is inserted, the other worksheets should be updated as well with the new name and a blank row to be filled in. And when a name is deleted, the entire row should be deleted as well.
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Why is Excel so back-***wards on this? Is there a VBA solution to having a new sheet inserted after, not before, the current sheet that can be attached to an icon?
View 3 Replies View RelatedI have a worksheet (Morning Reports) that has a running macro on what I need is how do I get this same code to work on a different sheet titled (Afternoon Reports) This afternoon report will be a scaled down version with slightly different range:
How do I modify this code to do the same thing just on a different sheet
[Code] ....
Protection password is "Financial3" and sheet and VBA
Attached File : New Morning Report - Master_Copy-NO DELETE.xlsm