I have a code that copies rows to another sheet if a value in D:D is found. BUT I need to add a "create sheet" IF the value exist.
So if VBA finds the value "TWO" in Column D, then insert a sheet at the end named "Two"
Then the code below will copy all the values of "TWO" over to sheet "TWO"
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "9" Then Rows(i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
In essence, the VBA should:
Search for "ONE" in D:D, not find anything and move on.
Search for "TWO" in D:D, will find it, Create a New Sheet named TWO, then the above Code to copy all matching rows into sheet TWO.
Search for "THREE" D:D, not find anything and move on....
There will be roughly 12 sheets created on average, so if I create all sheets beforehand.
I would like a macro to order the columns as per the hardcoded list. If the column is not found than add the column at the position as per the hardcoded list. Below macro does the first part of re-ordering however if the column is not found how do I add an empty column:
Sub Reorder_Columns() Dim ColOrder As Variant, i As Integer, Found As Range ColOrder = Array("LogicalFileName", "LogicalFilePath", "UploadedDate", "UploadedBy",
I'm having problems with trying to find something in the spreadsheet and inserting a row before it. For example I would like write code that basically goes threw my rows and if it finds the "2800" then it inserts a row before that. I have now bought several books but I have yet to come up with an answer.
I have data in cells B2:E2 and this can go down 100+ rows.
In column B i have invoice numbers but some cells contain the word "Deposit".
I have sorted this data so that the invoice numbers appear first and then all the Deposits.
I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.
I am trying to use a macro to insert a webbrowser on a sheet and then link the webbrowser but it keeps giving an error that it doesn't support what I am trying to do.
I am trying to insert 50 tabs (at different points in time during a macro) that each has a state abbreviation. I know how to get the macro to add a tab, but to name it, it wants to select " Sheet 4". The problem with this is, the tab that was created might be sheet 15 or sheet 1, and I need it to be able to name the tab no matter what sheet # it is.
I have a workbook with single sheet called Fronpage. I want to insert a new sheet and rename the sheet based on the character value user specified in R1C1 in the Frontpage, and also would like the new sheet to be the last sheet in the workbook. I have tried to use DDE, and also Macro recording to do it. It didnt work out. Can anyone have any code to do it ?
I have been able to make a excel sheet which takes inputs from userform for First Name, Last Name, Address etc. I have included a duplicate check for column "B" for "First Name". This checks if any existing data is already which matches the new data input for "First Name" through userform.
The userform only checks for the "First Name" check as required and gives a message that duplicate has been found. Then I have to close the userform and do a Control Key+F ( to find the new name for example, James) in excel sheet and validate that new name is same or different from existing name "James". This I want to do since this new name "James" may be another "James" as his "Last Name" is different. So even though First Name is same, since Last Name is different I know they are two different persons. In that case I will add the entry manually in sheet, instead of userform, since I would not be able to input the new data for "James" since the duplicate check with the current code will not allow me to do so.
What I am current trying is -
1. If the new name say "James" is entered through userform, then excel should point me to the existing row where the record for existing name "James" is there, say row 4.
2. Now without closing userform I should be able to see in the background excel sheet the search results for "James", as excel is pointing to that now. There may be multiple "James" in the existing which should be pointed out.
3. Based on the results that I see in the background excel sheet I can now decide that, this new name "James" is different from old "James" (of row 4) since his Last Name is different. Accordingly excel code should then ask me to add this record or discard this new record.
4. Duplicate check for First Name is enough for me. I would not require "Last Name" duplicate check.
I hope I have been able to explain my problem. I have also attached my current code as I am not able to attach any sample test file.
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
I have several worksheets of data, and one sheet that I wish to serve as a summary. I have the following function on this summary sheet: =MIN(Sheet2:Sheet4!C1)
This displays the minimum value for the given range, however I don't want the value, I want to know which sheet is was found on...so instead of 3 it might say Sheet2 if Sheet2 containded 3. I've spent quite a while trying other solutions to other similar problems but I just can't figure this out; and I don't have the experience to code a solution myself.
I want the code to insert a line under the row in which the code will find the value of the cell from an another sheet (there are two sheets - enter and base). If the code can't find the given value it should go to the last empty row in sheet and then perform some action (probably copy/paste job). Here is the code I came up with, the only thing I don't know is how to set the searched value as the A1 cell value of the sheet "enter" and what procedure to use in case this value is not found.
Sub findme() 'x is the A1 cell value in sheet enter Workseets("base").Activate Columns("B:B").Select 'if the following find procedure is successful Selection.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate 'then ActiveCell.Offset(1, 0).Activate .........
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
With this macro i print nine sheets. If there is one missing, excel gives a warning that the document cannot be found. I want excel to go ahead with the following document.
I have a file that I want a code to look for certain data and when found move the row to another sheet. The code needs to look at the active sheet because the name of the sheet will be different everytime I use it. It will also need to create another sheet named 'Online' that these rows are moved to.
The code will need to look for data that begins with either OAP, MCP, CPP, F4P, VAP, VWP, ITP and MEP. These will be followed by numbers i.e MCP123456. Once these are found I need the entire row removed to the created sheet.
The columns that these numbers will be in will vary on each file I use it on so it needs to look at the entire sheet rather than specific columns.
Also any cell that has data that begins with either MH and JD followed by 6 numbers that row needs deleting completely.
I have 10 worksheets in my workbook. The two that I'm concerned with in this post are "Main" and "Completed". If Column AA in "Main" contains the word "Complete" (which is from a drop down list) I want to copy the entire row from "Main" and paste in the "Complete" sheet without over-writting previous pasted rows.
Back in sheet "Main" I want the data in that row to be cleared with the exception of Column A as it contains a formula for sorting blanks. If I delete that row it will mess up my links on other sheets.
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
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.
I would like to know how is it possible to only run a macro with IF command if a path is found in a computer. My desktop user is example, jc855, and my coworker is jc886.
I would like to start the IF command such as, if C:Usersjc855 & C:Usersjc886 found, then keep going, otherwise, message box : "You are not authorize to run this macro".
By having this path, people will be able to download this macro but the access is only granted if their user is added thru the VBA. (which only me can access to edit it).
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.
However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.
Here is my code for one of the Userforms:
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range Dim strFirstFind As String
With Sheet1.UsedRange Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart) If Not rngFind Is Nothing Then strFirstFind = rngFind.Address Do
I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.
I have a worksheet with several option buttons and until yesterday, I had no problem selecting them. But today, every time, I select a radio button, an error shows up stating "Cannot run Macro "Option Button_click. Macro is not available in this workbook or is disabled". When the user selects a radio button, text appears in Col B depending on what button was selected (using VLOOKUP). That stuff is still working fine. It's just as soon as I click a radio button, the error shows up.
Based on the radio button selected, user can add in information to the columns adjacent to it, and then click a button to store that information in some rows below. The code for that button is stored in the sheet module. I have tried enabling macros and disabling macros and no change has appeared. What I have noticed though is that yesterday, when i opened the workbook, a yellow bar appeared asking if I wanted to "Enable Content". Today, that bar didn't appear.
I need to code an option to overcome the fact that 1 workstation sees all drive letters differently.
I have many macro functions that may refer to "P:My documentsTrucks 2008filename"
However this workstation sees that drive as "T:Trucks 2008filename" and the macro will either not function or cannot find the location to save a copy of action in question.
Can I code an option that will "If filepath not found" ie "P:" go to "T:" etc
I'm trying to write some VBA which will allow me to find certain instances of data across a number of different sheets and copy this into another sheet.
However if the sheet doesn't contain the data I'm searching for an error pops up and the macro dies, is there a way to search where if the macro doesn't find anything it skips to the next line?
I’m working in Excel ’03 with a large table. I’m hoping to run a macro against a cell with value ‘X’ whenever that value is found within a particular column. I’d like this to be relative and not tied to a particular column so I can tweak it as needed down the road and run it against different columns.
In the example below, lets say I’m working with column B. I’d like something that would go through every cell in the column and run a macro when it comes across a particular value. Let’s say this value is x.
__A B C D E F 1 a x a a a x 2 b a b b b a 3 c x c c c c 4 f f f x f f 5 s s s a s s 6 g x g g g x 7 a a a a a a 8 s s s s s s
So, in my example, the macro would be run on B1, B3, and B6. And, if I wanted to go back in later, I could run this on column D and the macro would run on cell D4.
I get a strange error message when using one of my excel sheets containing code. The workbook is quite big, but the essence of the issue is the following. I use a macro to refresh about 30 pivottables. The code is very simple and comes from the macro recorder. (Part of) The code is shown below:
for some strange reason, this code does not work anymore. It used to work fine, but when I run it now I get an error message saying "macro 'mDOM.XlinkOn' cannot be found". Does anyone know what this means?? I tried setting up a new pivot table in the same workbook and record a new macro to refresh. While the recording part works fine, running the macro afterwards gives the same error message.
Strangely enough, when I start a new workbook, add a pivottable and record the same kind of macro, it does work! Therefore, for some reason my workbook has been corrupted in some way. Does anybody know how to fix this? Rebuilding the workbook is not really my preferred option as there are about 20 worksheets and 15 pages of code in there.
You can find attached the workbook I am working on.
In my workbook I have four sheets. Only two sheets are relevant in this case: "Sheet1" and "List". On "Sheet1" there in column A there is big range of codenumbers (highlighted with red). On the "List" worksheet I have a smaller list (highlighted with green).
I have a code also in module1 but the code is not working.
I would like my macro to do the following. Check the code number from the "List" worksheet (green) and search for it in "Sheet1" column A (red). If this codnumber can be found in column A then leave the number on the green list. If it cannot be found in column A then, delete it from the green list. If you open the file, you can see on the "List" worksheet that there are three code numbers in the green range. The upper and the bottom code number can be found on the "Sheet1" column A (red) range, but the middle number cannot be found. So if the macro would work correctly then it would delete the middle number, and leave the other two untouched!
