I'm having an excel sheet with addresses. I need to copy some lines of the first sheet to the other sheet. Between the lines I want to copy there are 7 lines which shouldn't be copied.
I created the following macro which does the job: ...
I'm looking for a way to write a macro to insert 5 lines at the end of the data in column A. Then I want to copy a range into the newly inserted lines. I would press a button anytime I need this to occur.
Current last line of data A39
Need to insert 5 rows after A39
Then copy range BA30:CB34 into the newly inserted rows.
I have two sheets.
sheet 1 has a list of invoices.
on sheet 2 I just want to list certain invoices.
so sheet 1 may be like this:
001 rmb $25.00 1356
002 ght $35.02 4568
003 ght $45.23 5689
004 rmb $12.25 4568
005 bnj $1.25 4568
006 sdr $12.54 4568
007 rmb $65.25 4568
009 bnj $56.54 4568
On sheet 2 I need it to pull out all the rmb lines.
001 rmb $25.00 1356
004 rmb $12.25 4568
007 rmb $65.25 4568
I could make sheet 2 in to a list and sort it that way. but what I
need to do is pull the information into sheet 2, it's no good being
able to sort it on sheet 1.
I am calling sub B from sub A and want to skip some lines in sub B (only when it is called from sub A).
View 3 Replies View RelatedI am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows:
--------------------------------------------------------------------------------------------------------------------------------
I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search
Sub loopy()
Dim rngTemp As Range ' the range variable declaration
Dim intCounter As Integer 'counter as integer
' the following loop goes through all the cells in column a up until the last used cell in the column
For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp))
'increment the counter if the cell has value fridge
If rngTemp = "Fridge" Then intCounter = intCounter + 1
Next rngTemp
MsgBox "There were " & intCounter & " cells equal to Fridge"
End Sub
I have a file that has simple stats for multiple days. At the end of each day is a "Summary" line. I can't figure out how to find the lines that have the word Summary in them and copy all the values in that line to another sheet. I've made a mock up of my data. I have minimal experience with Macros, but am learning quickly.
Period Start
Calls
AHT
Sch
Net
SL Calc
12:00 AM
15
299
5.5
1.24
93.33
[Code] .....
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.
Here is an example of the work sheet where the data is originaly.
[Removed]
Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.
[Removed]
I am having trouble writing a macro that will draw a line relative to what cell is currently selected. I can only get the macro to draw a line in one specific spot.
View 9 Replies View RelatedI have 3 different sheets named:
USA
UK
DK
and 1 sheet named:
I would like to have a macro written that when runned will delete all data in line 2 to 1000 in the first three sheets and line 2 through 1500 in the compilation sheets
Is there a possibility to automate a grouping process i have to do every month? The rows that have to be grouped change every month.
Grouping is done based on the contents in column C. In here i have the following values: Category, Segment, Type, Brand.
For example, column C looks like this:
Category
Segment
Type
Brand
Brand
Type
Brand
Brand
The grouping needs to start from row 100 down. Usually the maximum number of lines to group is 500...
I always do the grouping for Brands first, then Type, then Segment and then Category.
I want to draw a (time) line from the top left corner of a cell (say C3) to top left corner of a cell (say i10) in a Macro. Both start and end cells (column & rows) will vary depending on certain input values which I can convert into a cell reference. Line must overlap on both ends by two column widths.
View 9 Replies View RelatedI have another report which needs to be sorted using a macro. However, before I can sort the data, I need to delete rows at the bottom of each report that have "0" values in column A and B. Again, each report I run is of different length so I need to write a code that will take that into consideration. Here is an example of what I need to do:
Sub second_step()
'
' second_step Macro
'
'
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("7876:7876").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
End Sub
Now, the thing is, not every report will start having "0" values in row 7876. How do I write a code to reflect this?
I have a problem with multiple, similar formulas in a macro. Each of these formulas, depending on the active cell, selects a certain range of cells and prints it.
The problem is, no matter which active cell I'm on, the macro activates all lines of the formula and separately prints each range. Is there a way to "separate" these lines in the formula so each range will print separately, depending on the active cell?
Here's the
I have a question for you that can be much helpful for me. I prepared a macro which is collecting many datas from different pages and entering them on a sheet. Some lines of this sheet are with data and some are empty. I would like to write a code, which will delete the lines in which there is no data. But I would like the code to do this between line 5 and line 225.
View 9 Replies View Relatedi am completely new to writing macros. have recorded a macro but have just found out the each of the new sheets that the macro creates needs a line at the top of the sheet with a title in it
View 2 Replies View RelatedI need a macro that duplicates lines based on the value of a Column. So in Attached excel D1 is 10 - I need it to create 9 entries for that line, so there are 10 total duplicate lines.
I had a Macro that worked that is in the document, I even had everything set up the same, which it is not now, but could not get it to work.
A Macro or a Finished document is fine, Just need this to produce raffle tickets for the church
Raffle Tickets.xls
I am trying to write a formula to insert in a macro for multiple lines of data.
Column B contains the date that I am comparing the date in Column C against. I want to be able to highlight the content on that row if the date in column C is greater than or equal to column B. how to write this?
Sample data:
3/12/20123/12/2012
3/12/20123/1/2012
3/6/20123/6/2012
2/29/20123/2/2012
I would need row 1 and 4 to highlight in red. this is part of a long Macro that is written and includes many other steps, but I cannot seem to make this step work correctly.
I have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.
I have managed to insert blank lines on change in values in column B, but am not sure about the best approach for inserting the SUM formula or the IF statement in columns G and J respectively.
I need to paste the first 10 lines of every page with a header.
I do not want to use a header, I want to designate every page to copy the first 10 lines from the first page and add those 10 lines. The "header" can not overwrite current data but needs to push it down 10 lines.
Basically I want to copy and paste all data in a row if the value in a cell is equal to something
I am currently using the below formula
The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).
But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.
So I am looking for a way i can add multiple values each going to separate sheets.
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.
I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.
I have a sheet which looks a bit like this
Day
A
B
Day
Cost A
Cost B
[Code]....
For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.
how would i go about eliminating the duplicate strings of text quickly?
I need a msg to pop up when a worksheet opens. Pretty simple:
Private Sub Workbook_Open()
If MsgBox("My msg goes here!") Then
Exit Sub
End If
End Sub
But I need to be able to have multiple lines, maybe a short paragraph.
How do you get it to allow additional lines?
In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.
I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.
Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.
Sheet1
*
A
B
G
H
I
J
2
Batch No.
Viscosity
*
*
Min
Max
3
4028634
26.7
*
Viscosity
21
27
[Code] .......
There are four lines of numbers, in all line 8 numbers.
1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2
I would like to find a function that will examine, if there are similar lines according to my definition, and several.
my definition is 7
For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.