I have a large spreadsheet of demographic data. In column A there is a name, in column B there is Address1 and in column C there is Address2. First I need to insert a new line under each line of data. Then I need the Address2 data (column C) of each line to be copied/pasted to that new inserted line, but in the Address1 column (column B).
View 9 RepliesI have a spreadsheet with the following (example) :
Column A :
alabama
alabama
alabama
arizona
arizona
arizona
arkansas
arkansas
I want to insert a blank line (row) wherever the state name changes.....
I need a formula to insert a line break after every 42 characters in a cell , example if there is 266 characters in a cell there should be 6(266/42)line breaks.
=REPLACE(REPLACE(A1,42,0,CHAR(10)),84,0,CHAR(10))
I tried this but i am not sure how to get the start num to change to 126,168 and so on
I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code
View 2 Replies View RelatedCreating a macro that will identify the last blank line in column J and insert a line in it? The macro will need to look from the "bottom" as there is blanks cells above the last line.
View 5 Replies View RelatedI need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
View 1 Replies View RelatedI have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50
Balloons 12567 Blue Princess balloon .86
Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27
Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
I'm using the following code
Code:
Sub UpdateValues()
' Purpose of this Module is to properly update all the values of the OEE Worksheets.
Dim ws As Worksheet
Dim r As Range
Set r = Range("C30:H77")
For Each ws In Worksheets
[Code] ...........
However, I'm getting a dotted line fragment left over
I suspect it's because I used .Copy in the formula. Is there a better method?
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
My company has an excel file that has our new customer's information (colum A & B are first & last name;Column C is customer email; Colum D is cutomer phone, ect...)
once that information has been entered we have another excel file that will load all of a customer's information, and then be pasted into a welcome letter into MS Word for their confirmation & emailed to them
The problem I'm running into, Is that I do not know how to populate my nex excel sheet with the info I want only from a specific line.
I would like to enter a line / row number (for example '6') and than have excel go to that file & copy/paste the name, phone, email, company name, ect. into my new excel sheet
I have one that works already, but the previous owner PW protected it & hid his formulas/macros, so I'm in a dead end now...
I am having a few problems with dynamic named range in excel 2000.
When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.
I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.
View 5 Replies View RelatedI have the code below in a userform. I would like to add a line that will insert this formula in column B everytime a new row is inserted....
View 8 Replies View RelatedIn sheet colA have dates how to insert lines between months. eg 3/19,4/5
View 9 Replies View RelatedAttached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View 3 Replies View RelatedI currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.
How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.
I have the following vb to insert a row above every row in an autofiltered list (autofiltered for nonblanks).
Problem is that if in the autofiltered list there is not a gap then it inserts a row for each line where there is no gap
SO for instance if in the filtered list cell A500:a505 have no gaps between them (meaning all are nonblanks) the vb will insert 6 rows above A500 and leave no rows inserted above a501, a502, ...etc
I have approx 10,000+ rows and there are approx 550 rows that I need to insert a row above (but want to automate/save time) by having the vb do it.
Code:
Sub InsertRow()
' Insert a row between each Client/Cycle
'Will have some manual cleanup after this step
[Code].....
I currently have a macro that does several thing, i want to add another task.
I need a row inserted everytime there is a change in column H from "0" to "F" only. The infromation will always start in row 8 but the last row is not always the same, it will be different every time.
ABCDEFGHI112345678223456783#N/A#N/A#N/A#N/A#N/A#N/A#N/A45B=Bi-WeeklyID type 2ID Type 308 GAPamount due6HCCompanyS= Semi-MonthlyCO#NAMEFORMER (F) or Current (0)78546WalreBbr55a234amy01009524WalreSn32m56daniel05001041WalreS589653jode0100011945WalreBef343456caryF2001212WalreSg5365M5665mikeF40013Grand Total2,200.00
I have two sheets that i am working with sheet1 and sheet2.
I need a macro that will insert multiple rows in row 8 of sheet 1.
I also need it to copy the formula from row 8 sheet1 to every new row inserted.
the number of rows inserted will depend on colum A from sheet2.
*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.
So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.
I am working a project where I need to copy/insert a Row of data onto Sheet 3 if values from Sheet2 are located on Sheet1. Here is my code so far:
Option Explicit
Sub move_rows()
Dim ws As Worksheet
Dim ws1 As Worksheet
[Code].....
I am trying to insert a row every time a certain value appears in row.
Example attached.Example 1.xlsm
I need to copy the entire row that has the value 2 in column B and insert it in the line above (not fussy). It must be inserted and move all data in other rows accordingly.
vba to copy and insert row based on cell value
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 would want to copy and insert the entire row.
LABELAB110
2
1
i have numbers in column A1:A556, i want to insert a row below every column that has a number so that they can have double spacing.
View 1 Replies View RelatedI have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.
First of all I would like to insert 2 lines under line 6 in the active sheet.
I then need to drag the formulas found in line 6 down over the two new lines.
Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
Worksheet excel with multiple lines and colums. I need a macro to automatically insert a new Line where the cursor is located, and duplicate data from previous cells: let say cursor is located L10, macro will insert L11, and duplicate L10 -C1 data to L11 -C1. I have a macro that does this function, but the Line insertion is always at the same line.May be the macro should "read" first the cursor position, Lx and then move down +1 line prior to duplicate the data.
View 2 Replies View RelatedI need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.
View 6 Replies View RelatedEach cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.
what I am looking to do is when in colum AA a cell changes to "Drawdown" I'd like the cells in column A:D,F,H:L,N:Q,S:Z on the same row to grey out and a line to be insert below the cell that was changed, if it selects "Fee" , cells in A:D,F,H:L,N:Q,S:Z on the same row.
I have writen the following butit seems rather cumbersome and I can not get the insert line to work.
Code:
Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Set WatchRange = Range("AA1:AA500")
Set IntersectRange = Intersect(Target, WatchRange)
[code].....