I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)
I want to be able to run this macro from any cell on the line above. How to modify it?
ABCDEFGHIJKLMNO 1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip 2XLinkData112/21/2011BobSmithData2Data3Data4123 MainMooresvilleNC28117 3XLinkData112/22/2011LarryJonesData2bData3Data4456 MainMooresvilleNC28117 4XLinkData112/23/2011MaryAkinData2Data3Data4789 MainMooresvilleNC28117 An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3() Dim Prompt As String Dim Caption As String
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
Not sure how to say that, but I'm trying to write a simple macro to copy some data and pastespecial (Transpose) it somewhere else on the same sheet. But I don't want to see the data "moving".
Here is an example:
Code: Sub Macro1() Set SCOPE = ActiveSheet.Range("D2:G6") Set THERE = ActiveSheet.Range("M240")
I have a spreadsheet that is finally working great. I made a custom tool bar with 5 buttons and each tied to a seperate macro. On my laptop ( where I developed it) it works great.
I put the whole sheet on on a thumb drive and moved it to another computer. When I load the sheet on the new computer , the tool bars are there but they error saying something about they can not find it.
I went into each button and changed it to put the macros in this workbook, saved it and when I put it on the other computer I had the same issue. I reloaded the original sheet and it went went back to all open workbooks.
I think what I did is saved them to all workbooks and they must be stored on the original computer. I thought just changing the button link would do it but I guess I am wrong.
My question is how do I get the 5 macros to follow the workbook? I need to move this to another computer in the morning.
I'd like to write some code to do the following: When the first cell of the row contains an "Y", move the entire row to another sheet (in the same file), keeping in mind that this sheet already contains some rows (so add the row on the first empty row in the sheet). Afterwards the original sheet contains no longer any rows in which the first cell is filled with an "Y".
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
I'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table.
I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.
I am looking to take the information from a selected cell in workbook a, and on closing the spreadsheet - moving that piece of data and adding it to the bottom of a list in column d of workbook b....
I have a list of diagnostic procedures, some of which are for the same patient. I need a macro that does the following: move every second procedure (a couple of cells per row) of one patient number (column B) next to (11 cells to the right and 1 row up) the first procedure. But only if there's less than 3 months between the procedures (dates are in column G) and if both procedures were on the same side of the body (left or right, column L) Is there a macro that can do this for me? Is there a macro that just moves a couple of cells in a row 11 cells to the right and 1 row up?
The following macro does what I need when run from the active workbook however I need macro to exist in 'personal.xls' so it can be easily applied to raw reports received in daily e-mails.
Sub Add_Sundays_Data() Dim bk As Workbook Dim bSave As Boolean Dim lRow As Long
On Error Resume Next Set bk = Workbooks("C:Template.xls") On Error GoTo 0 If bk Is Nothing Then bSave = True Set bk = Workbooks.Open("C:Template.xls") End If
I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))
When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.
I want to search a word in my Data sheet and want to know what is the column position. Example: I have a word "LastName" as a Column header in my Datasheet whose column position can be anywhere in the datasheet based on the table selected. So i want to write a generic code which give me COLUMN position for this word in the datasheet.
So if "LASTNAME" header is coming as 7th column it return me 7 if "LASTNAME" header is coming as 9th column it return me 9.
I was given a spread sheet with a number of payments on it, I was asked to take the "four" potential payments and only show one total payment. There are 2900 lines in my file and as you can see from the example the scenario repeats it self with the "four" payments all the way to the bottom. I would like to be able to add H2 to H5, total that number in J2 and delete row 3,4 and 5. This then has to be continued all the way down to line 2878 where I could then run a macro I have to delete blank rows cleaning my spreadsheet up. I would take a looping or user controlled macro (ctrl +) so I don't have to do this manually.
Could some one amend the VBA below to allow a continous loop on moving to the previous sheet within a workbook. Currently when I move to the first sheet if I use the shortcut key I get a debug error.
I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.
I have this macro listed below that finds all these files, copies cells from stat sheet and places them in sheet1 in another file. It keeps looping till all is found and done.
My problem is when it goes to paste in sheet 1 I want it to shift over a row each time.
Right now during the first pass it pastes in column B, I want it to start in column D
The issue seems to lie right under where it says "Put data into workbook"
Public Sub PullData() Dim wkb As Workbook Dim lngStore As Long Dim strDate As String Dim strName As String Dim Book As String Dim Sheet As String Dim week As String Dim Store As String
Is it possible to move rows of data in a spreadsheet to multiple spreadsheet accordingly? I had lists of tasks in a single spreadsheet and i need to segregate the tasks for all my staff in serial while no duplication among all of them. For instance, i got 4 personals in my department and i need the 1st 4 tasks to be distribute to each of them and next 4 tasks accordingly. This is due to all tasks are equip with due date and i need to calculate how much time i need to accomplishing them. i'm used to manually move it and found it time consuming, so i was wondering if someone would instruct me where or how to achieve it by using a simple macro.
Sub Moveit2() Dim Quantity As Range Dim Cell As Range Dim Cell2 Dim Breaks As Range Set Quantity = Range("a2", "a21") Set Breaks = Range("g1", "s1") For Each Cell In Quantity For Each Cell2 In Breaks If Cell2 = Cell Then Intersect(Cell2.EntireColumn, Cell.EntireRow) = Cell.Offset(0, 1) End If Next Cell2 Next Cell
End Sub
I have this macro to move things to the right. Sort of like a vlookup in a way. However, it runs using one range @ a time. For example it will look up the quantity in A2 versus what's in Row 1 and if they match, data from B2 would be moved into correct column. However, I'd like to run for multiple columns at the offset so I can don't have to keep changing data in the macro. Like want to run for these ranges at the same time as running the first range.
My command buttons are moving location after I run a macro. I have a worksheet with 5 command buttons from the forms toolbar. When the macro is run, another command button is moved into the cell that the macro ends in, how can I stop this?
I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:
Sub Create_Kicbacks_Sheet() ' Create_Kicbacks_Sheet Macro ' Creates "Kickbacks" sheet for invalid information. Sheets.Add After:=Sheets(Sheets.Count) Sheets("Sheet4").Select Sheets("Sheet4").Name = "Kickbacks" Sheets("Kickbacks").Select End Sub
This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:
if Column A = Y and Column B = Mandatory -> Leave Alone if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks if Column A = Empty Cell and Column B = Best Efforts -> Delete Row
Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:
Sub Moveto_Kickbacks() Dim r As Range, LR As Long With Sheets("Data") LR = .Range("A" & Rows.Count).End(xlUp).Row Set r = .Range("A2").Resize(LR - 1) .Range("A1").AutoFilter field:=1, Criteria1:="" .Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"
I have 3 columns Z, AA, AB. The heading for Z1 is “A”, AA1 is “B” and AB1 is “C” In column Z2:Z2000, there is a mix of A, B’s and C’s. I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button
Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls” The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this
Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.
Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).
The commas are not necessary, but is there any way to get this done other than manually entering them?
I was able to input barcode reader data into A1, and automatically get the date inserted into B1. I wish the next activecell to be C1, instead of A2 as the above code is doing at the moment - so how do I alter the code above to make this happen?
I have cut and pasted a large amount of data onto a spreadsheet. The problem is that I have two rows of data that is associated with one another, so lets say A1 has a name and A2 contains that person's age. This process repeats in the same way in A3 and A4 and so on.
I need to move the data from A2 to B1 without using a simple function like =A2 in B1. The reason is that I will need to sort just the Names in column A once I get the the data moved. I am not sure how to do this.
I need to move data within a cell. Is there a formula that can accomplish this? Here is an example of what I'm trying to do..
Column A / TN14YP244f / TN15YP245j / TN16YP246h
What it would need to do is take the last character in each cell and move it to replace the P in that cell. It should also capitalize the letter. ie: / TN14YP244f would become / TN14YF244
The letters at the end of the cell are always different, however the letter they replace is always the same (P).
My direction keys currently move the screen instead of move from one cell to another. For instance, I will have cell A1 highlighted and then press the down key. Instead of going to A2, it stays at A1 but the screen moves down one cell
