Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Insert Date In Cell In Each Visible Row


I have a spreadsheet from which i run monthly reports for aother business area. I have filters in row 2 (which is my header row) and what i want to do is filter column CH to show blanks and filter column CE to show non blanks. Then in every visible cell in column CH below the header row (row 2) i want to enter todays date - this is so a record is kept of when each row was detailed in the report. I have tried the code below (which i tried to amend (unsucessfully) from code i got here to clear some cells when i ran another filter for another report).

Sub FilterSheets_Monthly_OFMDFM()
If ActiveWorkbook.ReadOnly = True Then
MsgBox ("This filter must add the date on each row included in this return." & vbLf & vbLf & "It must be run while the workbook status is not Read Only!" & vbLf & vbLf & "Please close this spreadsheet and re-open using the password. Thank you.")
Exit Sub
End If
Dim i As Integer
Dim rng As Range
Application.EnableEvents = False
Application.ScreenUpdating = False........................


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Automatically Insert Static Date In Different Columns Of Same Row
I recently found on this site the macro to auto insert a static date in one column when an entry was made in the previous column.

What I have in my spreadsheet and what I would like to be able to do is if I select a particular Order Status from a drop down list, that it auto inserts the date into the respective columns.

Column F contains the various Order Status indicators ie Order Submitted, PO Raised, Delivered, and Invoice Received.
The date columns that I would like populated and which correspond to those status indicators (in order) are Column G, Column I, Column L, and Column K.
So if I pick Order Submitted, the status date should be auto populated in Column G. If I pick PO Raised, the status date should be auto populated in Column I etc.

View Replies!   View Related
Find Date & Copy Row Insert Formulas Only
I have a macro that searches a column for a date and then selects the appropriate date. The next step is to then copy the row and insert a new one below the copied one and paste only the formulas and none of the data. I have tried several iterations but all of the seem to paste the data too.

View Replies!   View Related
Check Cell Has Date & Insert Current Static Date
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos

View Replies!   View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time.

Example:

Sheet 1 – is current jobs and sheet 2 is old jobs.

My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2.

I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.

View Replies!   View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1).
The inserted row is changeable and is determined by whichever is the current active cell.

Eg:

Active cell is something random like E16

I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1

View Replies!   View Related
Insert Row And Copy Cell From New Row
Attached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?

View Replies!   View Related
LastRow Needs To Select Next VISIBLE Row (not Hidden Row)
I'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?

The code I use to select the LastRow is:

LastRow = Cells(4000, 8).End(xlUp).Row
I move down 2 more rows using:

Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row


View Replies!   View Related
Macro To Insert Date In Cell When Text Is Inserted In Another Cell
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.

Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.

ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.

I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.


View Replies!   View Related
Auto-insert Date In A Cell
I would like the current date inserted automatically into a blank cell when I click on it. It will always be in the same column, but a different date on each row for that particular entry. I used to use datepicker.xla--loved it, worked great. But now I get an erroe message when I installed and tried to run it on a new pc. Does excel come with a drop-down calendar you can pick a date?

View Replies!   View Related
Insert Date In Cell Via Calendar
Kind of like what they do in airline booking web sites, where you have the option "show calendar" and you select the date from there, to avoid any possible typing mistakes. When you select the cell, a calendar or a date drop-down list should pop-up.

View Replies!   View Related
Macro: Insert A Row A Cell = Particular Value
I need a macro in Excel sheet.
What I need is If Cell Value A4 = India then it should insert one row automatically after Row A10 & Value of Cell A 11 should be "VAT".


View Replies!   View Related
Row Insert By Selecting One Cell
I wish to use an InputBox to select one cell in a row and then insert an entire row at that position. What is the VBA code to do this?

View Replies!   View Related
Insert User Name In Adjacent Cell To Date
Is there a really easy code which will insert the current users ID into a cell adacent to the one previously completed automatically? For example if a date was entered in A2, then B2 would automatically be completed with the user identity?

View Replies!   View Related
Insert Next Row Based On Previous Cell Value
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.


View Replies!   View Related
Add/Insert Row Below Cell If Value Matches
I have a column O with the heading "Return Visit Required". Each cell, starting in row 5, has data regarding a job. So each row represents a different job.

If the cell in a certain row in column O has a value="Yes" then I need 1 row added below that cell. If the value="No" or "" then do nothing.

I need the value "Yes" to be non- case sensitive.

View Replies!   View Related
Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.

Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.

Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.

There could be 100 or 1000 transactions generated based on the day.

View Replies!   View Related
Vba To Copy And Insert Row Based On Cell Value
vba to copy and insert row based on cell value

I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example,

Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet.

I would want to copy and insert the entire row.

LABELAB110

2
1

View Replies!   View Related
How To Insert Blank Row Below A Cell Containing Specific Text
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:


- All of my data is in column A

-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

View Replies!   View Related
Insert Cell To Columns Having X Blank Cells In Row
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).

View Replies!   View Related
Insert Row Based On Cell Value :: Give Input Box On Click
when a cell in column "K" is clicked, an input box gives the user the choice of entering a number between 1 and 12. That value then inserts row(s) based on input value ..

View Replies!   View Related
Identify Button (shape) Row To Insert Row And Delete Row
I have a button (group containing and add and delete button).

I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row).

Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row.

This will allow me to add/insert rows by the button located at that row

The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp)

I also note that when a group is copied, it has the same shape name as that copied.
I want to keep the add and delete shape within the group (BtnGrp).
I do not want to select a cell or row or enter a row number to delete etc.

refer to sample workbook attached. Currently only has one record row.

View Replies!   View Related
Last Visible Row With Autofilter
I have an autofiltered sheet, on which I need to find the last visible row (all columns have the same # of rows in use). I am trying to use:

View Replies!   View Related
Visible Row Number In Autofiltered List
I have a list of numbered items and what I would like to do is display that items position in the list should an autofilter be applied

For example, with no filter applied the list could look like this:-

Col A Col B Col C
1 1 Armadillo
2 2 Platypus
3 3 One-legged Schornztickler
4 4 Armadillo

And when the autofilter is applied to Col C with a criteria of Armadillo the results should display thus:-

Col A Col B Col C
1 1 Armadillo
4 2 Armadillo

I'd really like a non VBA solution, but as I can't work one out myself I guess that's not an option?

View Replies!   View Related
Determine If Filtered Data Has Date Visible In 1 Column
I have a database that holds thousands of names and the columns are of trinings that they have completed. I can filter by the criteria of name and of ID. ID number is unique so what I want is to know if Column F has a date in it once filtered. It maybe line 789 but that is all it shows because the filter is on.

View Replies!   View Related
Highlight Cells Based On Row/Column Being Visible
I am attempting to have a cell highlighted if the adjacent cells are visible, but no highlighting when the cells are hidden. For example, Cell D1 is highlighted when cells A1:C1 are visible, but not when cells A1:C1 are hidden. (this is for the group/outline feature)

View Replies!   View Related
Resize Visible Rows Based Only On Visible Columns Text
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.

View Replies!   View Related
Insert Row Syntax - Resize Row VBA Error
I need to insert rows in row A44 depending how many cells exits in a range i named "ALL_C"

I saw this code that inserts 10 rows on A44 so i thought by modifying it the way i did would work but it didn't. I'm still new at this stuff. What am i doing wrong?

Original

Sub Insertinrow43()
Range("A44").Resize(10, 1).EntireRow.Insert
End Sub
Modified (doesn't work)

Sub Insertinrow43()
Range("A44").Resize(Count(All_C), 1).EntireRow.Insert
End Sub
Thank you!

View Replies!   View Related
Insert Range-named Row At User-selected Row
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet.

If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position).

This is what I have tried so far:

View Replies!   View Related
Insert Row- Macro Pasting Format And Formulas Under Last Row
I need to paste the format and formulas of the last row of data into a new row beneath it. I've seen a bunch of different end row functions, but which one is best and how do I only paste the formulas and formatting?

View Replies!   View Related
Auto Insert A Row With Info Populated From The Row Above
to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g.

We have 10,000 X Pens

Customer 1 buys 1,000
Customer 2 buys 6,000
Customer 3 buys 3,000

i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row

If this doesnt make sense then i am happy to answer your questions?

i can upload an example excel sheet if required if someone can explain how i do this?

View Replies!   View Related
Insert A Row And Copy The Value Or Formula Of The Upper Row
I'm having trouble with my self designed "program" in excel.

Is there a way to insert a row and copy the value or formula of the upper row? withouy using macro.

View Replies!   View Related
Insert A Blank New Row If Check The Different Content In The Next Row
i attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:

View Replies!   View Related
Duplicating Insert Row And Delete Row On Another Sheet
I have two sheets, they basically contain shifts. They are laid out identically the only thing that changes is the date along row 1. In order not to have to recreate the workers from sheet1 onto sheet2 I use the following

View Replies!   View Related
Automatically Insert Row When Row Value In Column Changes
I'm unable to find VBA code to insert a blank row when the value in Column L changes. For example if cell L2 = 400 and cell L3=500 I need to insert a blank row between L2 and L3. I need the macro to search the entire sheet which will have variable numbers of rows but Column L will always have data.

View Replies!   View Related
Find The First Cell In A Row With A Date
I have a table which, across the top, has different cell titles, say "Category" in A1, "Budget" in B1, and C1 through F1 are date values. I need a formula which will return "C1", because it's the left-most cell in the first row which contains a date.

I know I can find a date with ISNUMBER or the TYPE functions. But I cannot get TYPE to work on an array. Honestly, I can't even understand the behavior of TYPE. Let's say B1 contains a string, and B2:B8 contain numbers.

If I select one cell and do TYPE(B1:B8) and press enter, I get "2", because B1 contains text. If I change B1 to a number, the TYPE cell returns "16", which means error. Wha....?

If I instead press CTRL+SHIFT+ENTER on the TYPE cell, I get "64", which is the array type. So obviously this is not doing what I want it to do (which is to run the function TYPE on each of the cells B1 through B8). With ISNUMBER, I can get the array functionality, but I want to find the *first* numerical cell, not if each cell is a number.

View Replies!   View Related
Scroll Over A Cell That Can Display The Name From The Row Along With The Date From The Column
Is there a function/routine/etc. that when I scroll over a cell that I can display the name from the row along with the date from the column. (I know I can do it if I click the cell.)

Objective: Once I zero in on the desired cell I click it to schedule the person to work, count their hours, add to weekly and monthly totals, etc. etc. etc.

View Replies!   View Related
Copy Row If Cell In Column Contains Todays Date
I was wondering if there is any way of copying multiple, entire rows if they contain todays date from one sheet into a new one. I have a sheet (sheet1) with data such as names, reasons, and also a "next in" column, which displays the date when the person returns to work. This is column G. This sheet contains all the records ever put into the workbook. I then have a sheet (sheet2) which is for the records relevant only to 'today', it has the same layout and cell amounts as sheet1.

I'm trying to find a way to copy all of the rows from sheet1 which have 'todays' date (eg =TODAY) in their cell in column G and paste it into sheet2 to form a report of who returns to work 'today'. By 'today' I mean the day which the workbook is being used on. Ideally I'd like for the macro to be applicable to a button which can be clicked by the user, although this doesn't matter too much.

View Replies!   View Related
Insert Row Then Copy Row Below
The posts I have found regarding inserting rows and copying formulas etc. and their answers do not seem to work in my worksheet.

All I would like to do is insert a row then copy the row below into the new row.

I have tried to modify the VBA given in similar posts and cannot seem to make it work.

View Replies!   View Related
Insert Row And Copy From Above Row
i have a excel spreadsheet named "ECN Number". in this sheet i have a row in which column5 marked with value "x",column22 is empty " " and column23 has "some value".
if the above statement is true,i want to insert an entire blank row and i want to insert another row below this row below the empty row with column6 marked as "x",column22 empty " " and column23 with the samevalue from the above row.
after inserting this row with these values, i want to insert another entire blank row. i have attached the workbook with the specified sheet.

View Replies!   View Related
Find Last Row Containing Value, Then Insert Row After
I am creating a worksheet of contacts and the date and time that I phone them, along with their current "status" in our company. I have created a form and have got it to do the insert of new data that I input, BUT What I would really like is enter a "client number" (which is unique) and have it search my table for any record(s) with that client number, and then show me the last record that matches the client number, (this can all be done from within my form) then If I click on the "insert" command button, I want it to insert a new row after the current row and move my form data in the form into the new row which will include a date and time. So, To summarize.....I need 2 similar subroutines.

LOOKUP ----- I need to have my form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches, then display the data or display nothing is there is no match. INSERT ----- I need to have the form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches and then insert a blank row here and move my data into it.

View Replies!   View Related
Insert A Row With Formatting From Above Row
How would I insert a row that exactly matches the formatting, but not the contents of, the row above it? This new row would include borders, fonts, conditional formatting, data validating and listboxes.

View Replies!   View Related
Cell Border Non-visible?
I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.

View Replies!   View Related
Button Visible Based On Cell Value
I need to make my button CommandButton1 visible if I have a good value (0-200) in cell G30, and invisible if the resulting cell value is no good #N/A.

View Replies!   View Related
Auto Filter With First Visible Cell
What I am doing is using Column "Z" to determine if there is a value in Column "Q" by using this formula pasted down the entire column "Q3:Q65536"

View Replies!   View Related
Get Value Of First Visible Cell Using Auto Filter
I'm using auto filter to gather information from a giant list. I'm able to use Subtotal function in Row(1) )to gather all the numbers for a person.What I havent been able to do is figue out a way to get the value of the person that I am auto filtering.

Example
Row 1 contains my subtotal formula's
Row 2 Header Row
Row 3 Auto Filter Buttons
Row 4 All The Data

Column(A4:A65000)has the names of the people So when I use the Auto filter by Name how can I get the first visible cell name to show in (A1)?

View Replies!   View Related
Next Visible Cell In AutoFilter Range
I am looking for a code which can jump to a next cell from the activecell. I use the code

Activecell.offset[rowno][columnno]

This would take me to the next cell. However, this is a problem when the filter is on. I am not able to go to the next visible cell. Suppose if the row increase is 1, then cell selection goes to the hidden cell. I need to bypass the hidden cell and go to the next cell. Can anybody provide a code which does it? I tried searching the forum but could not get anything closer to this as I need something which works with offset and not cells.row.visible...etc.,

View Replies!   View Related
Cell Visible Char Limit
how I could split a cell's contents into two cells when it contains more than 1024 chars? The code would need to be clever enough to split the data after the nearest full stop before the 1024 char limit.

View Replies!   View Related
Insert Date
I'd like to inset a date automatically when ever a cell within a single
column is selected.


View Replies!   View Related
Select Cell Content From Visible Rows.
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

View Replies!   View Related
1st Visible Cell In Filtered Range
I'm using a named range called "VFILTER". This range is my filter range. Once the filter does it's thing, I want to set the first visible cell in column a to a variable...I can't get this right for the life of me!

With Range("VFILTER")
.AutoFilter
.AutoFilter field:=25, Criteria1:="DELETE"
.Offset(1, 0).Resize(. CurrentRegion.Rows.Count - 4, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.ClearContents
.AutoFilter

JPhelper.ClearContents
JPhelper = "=IF(ISNA(MATCH(B5,J46DATA,0)),FALSE,TRUE)"

.AutoFilter
.AutoFilter field:=25, Criteria1:="TRUE"
.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 4, 9).SpecialCells(xlCellTypeVisible).ClearContents
'set the first visible cell in column a to variable
Set OutRange = .Offset(1).Resize(.CurrentRegion.Rows.Count - 4, 1).SpecialCells(xlCellTypeVisible)
End With

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved