What would a macro look like that would find the value in the cell of the last row in the A column, then set that as the selection criteria so macro could copy all rows that match that to another workbook?
View 9 RepliesIn a particular sheet the data (with formulae) are present from rows E5:Q5000. I require a macro which automatically does the following:
- When user enters “submitted” in the row Column N, entire rows above that cell should be selected, and copy-pasted as values to remove all formulas. So if user enters “submitted”, at N31, then data set E5:Q31 should be selected and copy pasted special so as to retain only values.
-Finally the copy pasted data should be sorted as per the status of Column N.
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.
H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.
Below is what my limited understanding of VB came up with.
Sub Date_Range()
Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
[Code].....
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.
View 1 Replies View RelatedI want a macro to hid rows based on a specific value of a cell compared to another.
Example, In a cell I have the current date and time which updates when the the sheet is opened... On a row I have various info including another date in the past. When the date in the past goes over 6 months older than the current date, I want to hide that whole row.
Is this possible? Do I use an IF function in the macro I've seen to remove rows based on cells with specific values?
I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.
View 9 Replies View RelatedLike to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
I need to create a new macro that will select all rows that have data and create a (3) pivot tables.
I have tried to create macros that will create a new tab and populate the pivot table. I keep getting an error that says "subscript out of range", so instead of creating the tab during the macro I have created the tabs as part of the template.
Again the problem is when running the macro with more rows, the macro doesn't recognize the additional rows. Ctrl + A is used during the macro but is hard coated for only the number of rows it selects, Ctrl + A twice is giving me "blank" data in the pivot tables. I have attached a sample sheet with 14 rows. Next month there may be only 10 rows or 50 rows. I have macros created for the pivot tables to view.
1: How can I create a macro to select all rows with data?
2: Can I create 1 Macro to create all 3 of the pivot tables needed?
3: Can the macro also create the tabs during the execution of the macro?
macro to select the header and rows below till the next header is reached if column L is less than column J for the whole sheet which can be up to 5000 lines or more. Then copy all of those to a new sheet. Here is part of the spreadsheet for a visual:
Product number
Name
Name
Finish date
Planned Qty
Mfg U/M
Lvl
Component
Name
[code].....
(Excel 2010): Hide row if cell C in this row is empty.
I've just started using macros and I'm sure there is one for this problem.
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable?
I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.
I'm in charge of taking a report; streamlining its functions; and automating future reporting.
I'm using a database and some of the reporting function Access has. For another variation I'm forced to export the query to excel and transpose the vertical layout horizontally.
I'm making the entire process automated via a macro. The code is too long to post in its entirety but here is an example of what I'm trying to accomplish:
I am trying out with a code which checks for cell value as "Select" in column IU and then checks for corresponding column IV for value as "0". Please note that "Select" and "0" are populated by formulas. I need the select "Select" and "0" till the next "Select" occurs in column IU and delete the selected range and continue the process until last non empty cell based on column C.
I have written the below code but it doesn't work.
Code:
Public Sub Test()
Dim nRow As Long
Dim nStart As Long
[Code]....
I could have uploaded the excel file that I am working on but did not find any upload attachment option.
I have 2 workbooks. The workbooks have 36 tabs for each department. Workbook 1 are current charges for the departments and workbook 2 is the revenue for each department. I would like to take the top 2 rows from workbook 2 and add them to workbook 1 at the bottom of each tab. I was able to get it to work to a specific row but the workbook 1 has variable rows for each tab.
View 3 Replies View RelatedI'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document.
If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:
I am thinking of commanding macro to select worksheet tab based on the system date to do some pasting.
My worksheet tab is as follow:
Nov-12, Dec-12, Jan-13, Feb-13 and .........
What should be the macro script required to select the worksheet tab based on today's date?
I have a sheet with hundreds of rows of customer information; including a 7 digit customer number. I need a macro to delete all the rows where the customer number sequence start with 1 thru 8 (that would be the first digit of the customer numbers).
View 9 Replies View RelatedThis is what I am trying to achieve:
1) Prompt user to select a file (or multiple files)
2) Copy the files that meet certain criteria to a folder
My attempt (fail):
VB:
Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....
I get error 424 object not found in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
I have a matrix with numbers that each cell is composed by a function of two parameters (two columns). The formula is
"=COUNTIFS(T2:T99,"15",V2:V99,"14")",
So it's counts when in one cell column I receive 15 and in the other 14. For instance, I receive the number 3 - so I have three rows that match (the first column with 15 and the second with 14).
I want, when I select the cell from the matrix (table) with the number 3 (that I receive from the formula) it will highlight the relevant rows..
I would like to select rows of a range (eg A7:D11) but only those rows where the cell in column C is not empty
View 5 Replies View RelatedI need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A
B
C
D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
I have a worksheet whereby many of the rows are hidden. These rows were initialy hidden by means of scipts ( I have numerous scripts to hide data under various criteria) eg: Scripts which hide rows based on cell count, cell color, cell data information etc...
Now that I am able to veiw only the information that i want to see.. is it possible to create a script which selects cells only from rows which are visible? I have created a Named range begining from E:12 to G:500 called "Select_EFG"
Uing the example below, how could I select the cell data from the visible rows (12, 30, 34, 35, 50 etc... up to row 500.) from this Named Range (columns E:12, G:500) .... and paste this information in Sheet 2 Row5 columnC
........A,B,C,D,E,F,G,
Row
12
30
34
35
50
57
59
100
140
400
490
500
I have a worksheet with a block of data. The size of this block changes.
I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet.
So far I have everything working fine.
Now I want to create a button to trigger a macro to sort that block.
I can figure out the button and make it trigger a macro.
One of the lines in the macro is
I have many tables in separate sheet but in the same workbook. I need in sheet1 to call the specific table based on the value in D3 which it has the table name, how to do this?
View 9 Replies View Related