i 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
c5 shows today() and I'd like c5.value to be the headline of a chart. Hence, in sample file the headline be changed to 05/03/2014 (and tomorrow to 06/03/2014..).
View 2 Replies View RelatedI'm trying to copy a huge list of formulas and formats from one work book to another almost identical, each time it adds the old workbook name title into the formulas, this is easy to do in the formula bar for individual formulas but can this be done for many?
View 3 Replies View RelatedCode:
Sub Final()
Dim NameCell As Range
For Each NameCell In Sheets("! Names").Range("B1", Sheets("! Names").Cells(Rows.Count, "B").End(xlUp))
[Code]....
I want to pull data from ! Names. This code already Renames a new sheet from the ! Names sheet and also puts there name in cell C2 and phone number into cell C3 of the created sheet. I want to put more data from the ! Names sheet. How would i go about adjusting current code to do that?
I have only recently started playing around with macros, and am slowly getting into them. I do however still struggle a bit to ‘read’ them. Having played with formulas for years I can generally translate a string of formula text into English, like ‘if this cell value is greater than that cell value, then do this, if it is not then if it is equal to that cell value, do that, if not return 0’. Babbling like a child basically.
With visual basic I have managed to record some handy macros and then tweak them a little manually but I am still struggling to follow it going through it step by step reading it like a formula. So I hope you won’t mind me asking some very stupid questions. I mean well; I’m just a bit slow.
At present I am trying to do two different things on two different sheets, and I was wondering if perhaps one of you could nudge me in the right direction.
1:
I am trying to insert a blank row above every row that has a certain word in column B.
So basically ‘find value “Example” in column B, and when you find it, insert an entirely blank row directly above it’.
2:
Is a bit more challenging. I want to change the colour of a cell if the value in the cell corresponds with the value of another cell in the same column.
For example, I have a long list of surnames in column A. When I add ‘McNeil’ at the bottom, I would like to be able to run a macro that checks if the name McNeil appears anywhere else in column A, and if it does, that it changes the colour of the cell.
Preferably both of the cells that say McNeil, but one would do very nicely indeed.
If that is possible, I wonder if it is possible to do the same with the first name in column B, but only if there was a match for the surname in column A on the same row. So, if McNeil does not appear in column A, don’t bother, but if it does, does the corresponding first name appear in column B?
If both of that is possible, the next step would obviously be if McNeil appears in column A (say twice, once in A123 and once in A678), do cells B123 and B678 match as well?
See the code below. It works fine but in the report that gets printed off, it doesn't display grid lines and line numbers .
[Code] .........
I have a chart with sales data on one axis and inventory on a secondary axis. I have the average of each columns of data and want to add a reference line to each axis with it's average. I have added the averages to my data set, but it only adds the reference line to my primary axis. I actually began by manually inserting two lines to the chart (which seems asinine). They actually didn't print because they weren't actually on the chart, but rather just on the spreadsheet behind.
View 5 Replies View RelatedI want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies View RelatedI want to use a macro to save an .xls file as an .mht file. I have:
ActiveWorkbook.SaveAs Filename:="C:ReportsBook2.mht", FileFormat:= _
xlWebArchive, CreateBackup:=False
which works for saving, but how can I Change the resulting web Page Title using VBA? I tried recording the macro as I changed the title and saved but nothing recorded for that. I know I can always just go in after the macro runs and manually change it but it seems like there must be another way.
I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.
Sub Chart_Updater()
Sheets(Chart1var).Select
With ActiveChart
.HasTitle = True
If Chart1var = "COLI VUL 1 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _
The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.
Is there a way to count the total number of rows with data (excluding title row at top and Auto-Sum row at bottom) and put the result in L2?
View 14 Replies View RelatedSometimes one employee may appear under more than one group (bartender, barback, waitstaff, etc.). I need to insert a formula that will check the other groups to see if this employee worked any hours in another group that week (I would assume a VLOOKUP would work), and then distribute the overtime hours accordingly.
If you view the attached worksheet, you can see I've entered a name and some hours. The formulas in cells M2 and N2 are my attempt to fix this problem. Anyway, the way the overtime hours are distributed is based on the group they worked under after they reached 40 hours. So looking at the example on the worksheet, Bob should have all of his overtime (13 hours) appear under O/T in row 2 because the 9 hours he worked on the waitstaff was before he was "officially" earning the overtime.
So I am needing the formula to check all of the groups other than it's own for a duplicate, add the total hours, and distribute the overtime accordingly.
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'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 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 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'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 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 have a Macro that I use often - It cuts a Designated Row from my AMZ-GM Open Sheet and moves it to the first open row of AMZ-GM Sold and leaves Cell U in that row Copied to the clipboard.
I would like to add two more jobs to that macro but am having a hard time.
1. I currently start the process in AMZ-GM Open.xls by Control F to find the SKU Number for the item I want to move ( it is always in Column N ) - I then manually highlight the entire row and run the macro below.
I would like to add code to my macro to highlight the row that my SKU cell found is in.
SO.... I want to start my macro from a single chosen cell instead of a chosen highlighted row.
I need to start my macro from the chosen cell in Column N. I need to add code to Highlight the row that my active cell is in and then proceed with the rest of the macro.
I recorded a macro starting with a cell -highlighting the row but it records as a specific cell / row number. I dont understand how to make it based on what ever cell is shown as active.
2. I would like to take the copied cell text ( U ) that remains from the last command and insert it into an open Word Document ( Amazon Sale.doc) at the position of the cursor in the that open Word Document. I'm not finding much info on moving that cells text to Word.
Heres my current Macro
--------------------------------------------------------------
Sub OpentoSold()
'
' OpentoSold Macro
' Macro recorded 2/1/2008 by Mike
'
' Keyboard Shortcut: Ctrl+q
'
Dim objLastRow As Range
Dim lastRow As Integer
Selection.Cut
Windows("AMZ-GM-Combine.Xls").Activate
Windows("AMZ-GM Sold.xls").Activate '
Set objLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell)
lastRow = objLastRow.Row + 1
Rows(lastRow).Select
ActiveSheet.Paste
Range("U" & lastRow).Select
Selection.Copy
End Sub
------------------------------------------------------------
I have the following code i want to add to the code at that bottom:
With combobox1Data
If .ComboBox1 = "" Then
MsgBox "All location must be selected"
Current code is:
Private Sub cmdDone_Click()
End Sub
Private Sub CommandButton1_Click()
Dim TheName As String, Response As VbMsgBoxResult
TheName = ComboBox1.Value
Response = MsgBox("Are you sure you want to remove " & TheName, vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Range(TheName).Offset(-2).Resize(Range(TheName).Rows.Count + 2).Delete Shift:=xlUp
Names(TheName).Delete.....................
