There is a webcreated excel document that generates dates as the date of such as:
4/1/09 12:39:00 PM
I want to write a macro that will remove the date part and just leave the time. How would I write that? Times are in columns C and D and I want it to scan all the way down to row 600 and replace.
I have a workbook where it is important that users do not delete any information in a particular range. But, I do not want to 100% block users from deleting the info.
What I would like to do is, if a user selects a cell within the range, and tries to overwrite or delete, that a warning box comes up to advise the user against changing the data. Is there a way to do this?
I want to delete the rows if they do not equal "TL" based on certain criteria, but save the info
then delete the rows if they do not equal "Fedex" based on certain criteria, but save the info
then delete the rows if they do not equal "LTL" based on certain criteria
After the information has been filtered it is supposed to load to a template giving monthly information for each of the above, but this is not working
VB:
If Load = "LTL" Then
Range("A2").Select
Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
For x = 2 To TotalRows
If Cells(x, 6).Value > 10000 And Cells(x, 1).Value = "FEDX" Then
[Code] ....
I am pretty sure that my problem lies within the parenthesis where it says Rows("2:" & x-1).Delete. Am I supposed to put something else there since I am deleting rows based on three different loads?
I have a VBA macro that deletes duplicates rows, however I would like to retain the information from the duplicate row, just before deleting it. For instance, If I have these two columns: ....
View 9 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I need to make a macro to get sales information from sheet one to sheet 2 in a different order with a few other things that need to be added in everything I need is in sheet 2.
Sheet 1 is what I work with and I need it to look like sheet 2 using a macro I wrote what I need in sheet 2A13-...
I'd like to build two macros that are slightly different but are both along the same lines.. The first one will take only certain columns of information from the "Master" sheet and dump them into the "Public" sheet. (I want only certain information to be seen by others). I.E. say I only want columns A, H, K, L from the master sheet to go in A, B, C, D in the Public sheet. I hope thats enough detail.
The other macro I want to build will take the A, B, C, D information from the Public worksheet and dump it all into another workbook called "Client Lists". The link to these two documents would be over a network so there shouldn't be any issue there... This way, all the people I work with can have this document and have up-to-date information on clients etc.ed...
my macro is set up to do the following in my book once the user inputs a row of information. the user will go to the opproperate sheet based customer and input all info.
the macro is run (ctrl+z)
the macro takes the last row in the active sheet, and pastes it into the last row of the "ALL" sheet. then it goes and sorts the sheet in order.
i want to adjust it so that the user will input the row of information into the last row of in the "ALL" sheet. then based on the given informaiton, paste the same row in both the corresponding customer and location sheets.
im assuming im going to need to use some variables?
attached is the test spreadsheet.
I need a macro to clear data on 2 worksheets that i can add to a button. lets say sheet1 B55:Y55 and sheet2 b102:y102
View 3 Replies View RelatedI have a template work book that has several worksheets with numerous formulas thorughout.
For example;
=SUMPRODUCT(--ISNUMBER(SEARCH("MCSH",P9:P218)),--ISNUMBER(SEARCH("HWD",G9:G218))).
When I set up another template for a different crew I manually have to go through the entire work book and change the crew identifier (MCSH) in every formula.
I am looking for a formula or Macro if its the only way which does the following:
I have a set of locations in the below table which also has exactly the same name as a tab in the same workbook. So for example I have 1 tab called "summary" which contains the below table and than another tab called "London453" & another tab called "New York45" & another tab called "Paris3232" & last tab called "Vancouver43"
In the "Reconcilation needed" column I would like a forumula/macro to start in Cell B2 and look at the cell next to it in Location column [Cell A2] and then go to the corresponding tab in the workbook and if there is a value in cell G22 in that tab, than I would like the formula/macro to take the first 7 charactors of Location value otherwise put nothing.
Example :
in tab "London453" there was a value in cell G22 --> hence 7 charactors "London4" in below table
No value in Cell G22 in the "New York45" tab --> hence nothing in below table
Value in "Paris3232" tab in cell G22 -> hence "Paris32"
No value in "Vancouver43" tab -> hence nothing
Location
Reconcilation needed?
London453[CellA2]
London4 [Cell B2]
New York45
Paris3232
Paris32
Vancouver43
I have a userform which is designed to get and enter data into one worksheet.
When I call the userform up from a different worksheet it doesnt refer back to the data source worksheet?? How can I do this?
I have used
sheets("dataworksheet").activate
in the user form initialise event but this still doesnt work.
I need a macro thaw sends a mail to a specific address (and CC) based on a critiria in a cell. This is the code I have. It does not send the mail.
[Code] .....
I have to worksheets, the first is filled out and at the end Ive place a button that I would like to open the second sheet and fill in cells that have duplicate info from the first, so just copy & paste but kind of automated for speed.
I have only got as far as opening the second document with the macro, after that im stuck!
The Info I would like to be copied across from the "service Diary" to the "Service Report" would be As follows:
*CUSTOMER
*CONTACT
*MACHINE TYPE
*SERIAL NUMBER
*QUOTE NUMBER
*ORDER NUMBER
What the Macro has to do is this.
It takes an specific word from a certain number of files that you can choose from an add file window.
Then when you choose those files the macro starts to run every file and look for an specific word, in this case is "ROFO March 2013".
When it finds it, The macro creates a new workbook( You create the name of that workbook y the first place of the macro, when you choose the files that you want to look for that word).
So, When the macro finds the word ROFO March 2013, it copys all the columns that has that name, and keeps looking until the file ends.
And paste all that info in the new file, A consolidate File.
I have change all the names of variables of the macro so you can understand it.
Code:
Sub copy_data(ByVal Origin_File_route As String, ByVal destiny file As String)
Dim Origin_File As String, RsBusq As Range, ROFO As String
Dim Rango As Range, Fila As Long, lastrow as Long
origin_file = Dir(origin_file_route)
[Code] .......
I have a table like this:
1/2/14
2345
1/6/14
34665
1/7/14
5473457
For Column A-I would like to create a macro that will copy the info from cell A1 and paste in in A2. This will continue all the way to the last row containing info which will vary. So after the first copy/paste, it well go to A3 and copy paste onto A4, A5 will copy and paste onto A6 etc.
For Column B- The same only it will copy the contents of B2 and paste it on B1, B4 copy/paste to B3 all the way until the last row of data.
When I open my workbook I get a popup that says: Welcome Back...
Using a module with the following
Sub auto_open()
MsgBox "Welcome Back..."
End Sub
I want to have it say: Welcome Back, Phixtit
Where "Phixtit" comes from the cell "C15" on my "INFO" sheet.
Example:
='INFO'!$C$15
I am comfortable with getting a macro to copy & paste where I need it to, however how to get the macro to recognise when a certain value has stopped.
For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies View RelatedI want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
View 2 Replies View RelatedHad 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...
How can I get the recorder to acurrately record and diferentiate printers?
I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data
[Code] .........
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.
My worksheet runs a lot of code, then deletes all macro code and saves itself.
The problem is the next time i open I still get the macro security warning!
I've checked thoroughly and there is definatley no vba left anywhere.
If I open the document, enable macros, and save it, then open it again, I no longer get the warning.
I have date from column a to k
I have a formula on column L
I want macro to delete all rows with #N/A
Need a macro for the following situation, will be incorporated into my other macro.
For each row in column C,
if value for first 4 letters = "abc " or "def " or "ghi " or "jkl " then delete entire row.
I'm looking for code that will prompt a user to select column(s) to be deleted.
- With the exception of columns A,B,C.
- Only entire columns can be deleted
- Rows can not be selected for deletion
I want to delete a row if it contains componenten.
In here i ask to delete componenten not.
Or should i take "*componenten*" or totaly someting else
For MY_ROWS = Range("c65536").End(xlUp).Row To 1 Step -1
If UCase(Range("c" & MY_ROWS).Value) = "COMPONENTEN" Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
Have a sheet that has a $ value in Col J would like to run a macro that would delete the Row if the value is $0.00 and move the rows up.
View 9 Replies View Related