Macro - Making Extensive Action Items Log For Work
Apr 11, 2012
I am trying to make an extensive action items log for work.
On Sheet 1, rows 1-3 are blank. Row 4 contains column headers.
Column A: Item #
Column B: Description
Column C: Drawing #
Column D: Conforming
Column E: Nonconforming
Columns F, G, H don't really matter
What I am wanting is to be able to put an "x" or check mark in either column D or E (conforming or nonconforming). Then I want to run a macro with a push button (I know how to make these) that when pushed scans the spreadsheet row by row until it finds the "x" or check in Column E. I want it to copy every instance of this and send it to an Action Item Page that I have temporarily called Sheet 2. (This way I can go here to see all nonconforming issues). I do not want it to remove the information from Sheet 1.
Then, I want to be able to change the "x" or check on the Action Item Page to Column D (back to conforming) and the macro (push button preferably) copy and send that information back to the original spreadsheet in the SAME row it was.
So, in short I want this to be a fluid way of checking off a punchlist of items.
To complicate things even further, if possible, I would like to have several worksheets send information to the one Action Item List rather than just the one.
View 2 Replies
ADVERTISEMENT
Jun 6, 2006
I am looking to build a macro that will perform several tasks. This is what I need.
First I want it to look at column D and delete any rows that do not contain the following DEP-CHK, DEP-SAV, or DEP-MMA.
Next I want it to look at column E and delete any rows that do not contain
the following: 000, 503, or 910.
Third I want it to Sort all data by Column I then B then C.
Next I want to insert 2 blank rows each time there is a change in column I.
Finally I want to sum all of the sections in column F.
View 9 Replies
View Related
May 30, 2013
I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.
Code:
Sub UIUIUI()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub
View 3 Replies
View Related
Jun 11, 2013
I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.
View 2 Replies
View Related
Nov 19, 2008
I am trying to loop through an action within a macro and require some assistance.
I have the following
View 2 Replies
View Related
May 30, 2009
I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.
Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.
View 7 Replies
View Related
Nov 23, 2007
I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.
However, I need to repeat the same procedure 1296 times.
Please see below the macro.
Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.
Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.
The other parts of the macro will not change.
Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?
View 9 Replies
View Related
May 13, 2014
I have an excel Workbook made up of sheets that are named after months ("May 2014", "Jun 2014" etc). I want to pull data from each sheet that comes after a user specified date.
View 3 Replies
View Related
Mar 25, 2013
So I have this worksheet full of formulas that I do not want users to accidentally delete. Now, I know I can use the standard "Protect Sheet" option to not trigger any accidental changes but I would like an additional safety check (since management users will have access to unlock the sheet) via a macro to prompt the user with a message box (with Yes / No options) which essentially asks the user if they are sure they want to delete a cell if the "Delete" button is chosen? If the answer is No, nothing will happen. But if the answer is Yes, it will delete the contents in the cell.
View 1 Replies
View Related
Jul 12, 2014
I have some excel files which should be saved in Excel 97-2003 Workbook format . I am trying to write vba code for that i am struggling at starting . problem is i have 6 excel files which should be saved as Excel 97-2003 Workbook format i don't know how i give 6 files path to code .
View 2 Replies
View Related
Jul 26, 2014
I'm using macros to apply some complex sorting criteria. It is also applying some cell formatting specific to the sort criteria being applied.
At the start of each macro, I'm resetting all cell formatting to remove any formerly applied formatting that is not correct for the new sort criteria. Basic stuff.... works fine.
Now I need to ensure all formatting created by any macro-generated sort gets removed if a user applies a sort using any sort function from the Ribbon. (Reset all formatting prior to any 'manually applied' sort).
I can create the macro to reset the formatting, just not sure how to trigger it upon user action.
Is it possible to trigger a short macro to reset cell formatting when a user applies a sort using the Data>Sort, Data>Sort Ascending, or Data>Sort Descending commands from the Ribbon?
View 9 Replies
View Related
Feb 6, 2010
I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.
If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)
Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
View 9 Replies
View Related
Mar 10, 2014
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
View 3 Replies
View Related
Sep 27, 2012
I am about to write a small macro to do a iteration calculation. I would like though for the user to select me a cell so that the macro uses the value in that cell to start calculations. Now, what I think would be the problem is how do I code a "time" or notice an action such as clicking in a cell to continue with the macro.
Would it be by inserting a pause in the code of a certain time so that gives the user time to click and wait for the code to start? If so, how would much time is enough? Is there another thing that could restart the code such as "noticing a click in a cell" command in VBA?
View 1 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Jan 5, 2010
I have an excel spreadsheet with two tabs.One called "menu" and one called "website"
I want to put a button in tab one (menu) and assign a macro. I need the macro to run/output in sheet 2 (website) not in sheet 1 where the button is placed.
No matter what I do I can't figure it out. The macro always runs in the same sheet as the button.
View 5 Replies
View Related
Sep 1, 2007
I have the following code as part of my macro. What I was wondering is a) Can anyone think of a way to shorten this? and b) I currently have this exact code copied 8 times, I did a find and replace to change 'if year = 2003 then' to the year 2010. What I would like is to have 1 code that will automatically change depending on the year the user inputs in a cell. (I should mention that 'Year' is a cell on my input sheet where the user types in the year they are currently working on and the macro automatically runs)
'2003
Sheets("Cash Crops").Select
If Year = 2003 Then
' This copies the cash crop BPU's.
ActiveSheet.Unprotect
Workbooks.Open Filename:="F:xdataCAISCAIS spreadsheetsBPU's.xls"
Application.Goto reference:="Cash03"
Selection.Copy
Windows(Filename).Activate
ActiveWindow.SmallScroll Down:=21
Application.Goto reference:="CashCrops"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-30
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True..............
View 9 Replies
View Related
Dec 17, 2007
in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.
For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.
I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.
Sub Button3_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
View 9 Replies
View Related
May 12, 2006
Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.
View 5 Replies
View Related
May 7, 2014
In my VBA macro, it copies data from this.workbook, and paste to another "destination" workbook. That destination workbook must be opened first before the macro is run.
Within the Macro code, I had to specify the exact name of the workbook which the data is pasted to.
The problem is that the exact name of that workbook which it paste to changes every week.
Is there any method where my macro can be changed and updated with the name of the destination workbook each week without manually opening and editing the code. The user of this.workbook is not technical enough to edit and maintain the macro each week
View 8 Replies
View Related
Sep 17, 2013
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
View 8 Replies
View Related
Jul 4, 2014
I am creating 4 Sheets from a Main Sheet by Filtering Data based on different values in a fixed column, then the Filtered Rows are automatically pasted in the respective sheets.
Then these 4 Sheets are creating another file.
The same thing done manually by Grouping the 4 SHeets and then Move Copy into a New workbook also makes the file heavier.
Everything works fine however though the data is not much the file is very heavy, so what do i do to make it of appropriate size ?
View 1 Replies
View Related
Dec 15, 2008
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View 12 Replies
View Related
Feb 4, 2009
To run a macro from the first tab of the workbook but have it do it work on another tab.
In the workbook in question I have a tab called "input" were certain information in updated and is linked or used in formulas on other tabs. Some of the tabs have macros that do various functions. I would like to set up one macro button on the "input" tab that would run the macros on the other tabs instead of having to run each one individually from its own tab.
Below is on of the macros that I would like to run from the "input" tab and have it update the "AP Accrual" tab
Sub CopyPriorMonthAccruals()
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,-1)"
Columns("L:L").Select
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,0)"................................
View 9 Replies
View Related
Jul 1, 2014
I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??
View 2 Replies
View Related
May 28, 2009
I have a macro which loops through data, deleting those rows that are marked with a "Y". However - it appears to always want to do this in three stages. The first time I run the macro, it delete's a large chunk of those marked rows - but it nearly always leaves two rows. The second time I run it, it deletes one of those two remaining rows. And then I run it again and it deletes the final outstanding row. I'm sure i've done something stupid here - the code is below
View 2 Replies
View Related
Apr 17, 2007
I need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?
View 9 Replies
View Related
Sep 5, 2008
Everytime a cell (say B18) changes value I want the following to happen:
If it changes to "Annual" I want row 20 to ungroup
If it changes to "Quarterly" I want rows 22-25 to ungroup
If it changes to "Monthly" I want rows 27-38 to ungroup
From what I can see on other discussion groups I need to enter a code on the worksheet from view code... but I cant work out the code that I would need to enter.
View 9 Replies
View Related
Oct 22, 2008
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)
A second thought was to copy paste them to a new sheet for each seperated week.
and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week
i thought maybe working with boxname?
View 9 Replies
View Related
Apr 7, 2009
I am sent an email that contains multiple blocks of information.
They're broken down like this:
Title
Link
ID
Department
Date
Status
Value
News
There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.
View 9 Replies
View Related