I am creating a time sheet for a supplier & when the supplier enters a date into column A I would like a new line to be inserted below & include the formatting, formulas & data validation from the row above.
View 7 RepliesBasically, I am curious if I were to insert a value into say cell A1, is there any way B1 could display a picture I've saved. To be a bit more specific, A1's value would be 777, and B1's inserted picture would be 777.png saved on my PC. This would need to apply for the whole of the sheet for all the values in column A.
View 5 Replies View RelatedI 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 6 Replies View RelatedI have a sheet(Sample.xlsx) in which weekly data is collected and this is done by using the VBA code(Present in Copy 1 and Copy 2 button in Master US.xlsm) written by me. But in my code, it inserts a new after asking two parameters, i.e. line range(i.e. row no for eg A64) and date. I want it to automatically search row containing last date and inserts row below it and this needs to be implemented in "Insert Automatically" and "Insert Date" button using VBA code in Master US.xlsm sheet.I dont have problem with second paramenter.
Logic i want to use :
Itergation of whole page
{
If (col A<>Date)&& col A=Average(i.e. contains average word)
then
inserts row above that row
And then ask for date to enter using date variable.
then inserts row above that row containing last week data in col A.
}
"Insert Automatically" in Master US.xlsm sheet is the button which takes system date and implements the above logic.
"Insert Date" in Master US.xlsm sheet is the button which ask for only date and implements the above logic.
Sheets attached.
Master US.xlsm
Sample.xlsx
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.
I need to create a spreadsheet which will have a start date and duration on programme. Is there any way I am able to automatically insert the End dates based on the weeks on the programme?
Additionally, can a function insert a date for the reviews which should be every two weeks into seperate cells?
Name
start date
weeks on programme
End date
1st review
[Code]...
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.
Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights):
r.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)"
And here's the code in it's entirety:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
For Each r In Intersect(Target, Columns("d"))
If r.Row 1 Then
If Not IsEmpty(r.Value) Then...........
I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....
View 6 Replies View RelatedWriting a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
I was searching through this site and found this macro ...
View 9 Replies View RelatedI 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 8 Replies View Relatedi got a problem with date range.actually i wanna to insert date range automatically which is referring current week.
View 2 Replies View RelatedI'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
I have created an attendance tracker for a team of people at work. It simply involves a grid of the days of the month and on each 'day' (or cell) I have set up the Validation command to prompt the the user to select whether they were at work, on holiday, off sick etc. etc from a drop down box. If they do not change the cell, its defaulted value is that they are in work.
When a user selects the days that they are planning a holiday in the future, they are meant to write a comment on the cells they have changed, to advise when they had 'requested' the holiday.
What I would like is (a macro?) which makes the comment box automatically 'pop up' when they change a cell from its default value prompting them to fill in the details rather than relying on them to 'add comments' manually.
In addition, what would be the best way to 'restrict' them from booking holiday within the next 7 days - we have a problem with people booking holiday on 'lastminute.com' for 2 days time, booking off the holiday on the spreadsheet and saying that they had it planned for weeks!
Any way to have the worksheet name on a tab inserted automatically in a cell on the spreadsheet? I have a file used to record orders and I have to keep inserting new worksheets for each order. I want the name on the tab of each worksheet to show in cell A1 so when I copy in a new worksheet A1 will show Sheet 1, for example. However, when I rename the worksheet I want "Sheet 1" in A1 to change to the new name.
View 2 Replies View RelatedThis may be an easy answer, but I haven't been able to figure it out. I have a cumulative page of formulas that reads "SHEET1!B8+SHEET!B9". I need the next row formula to reference "SHEET1!B47+SHEET1!B48", and then "SHEET1!B86+SHEET1!B87". Each row the formula moves 39 rows on sheet 1. Is there a way to get excel to automatically do this, so I don't have to go line by line?
View 9 Replies View RelatedCan anyone help? I need to achieve the following involving the insertion of rows from a specified value reference within the worksheet to which the rows are being added.
The original data would look like:
ABCDEFG1PeterABC, DEF, GHI32DavidABC, DEF23SamABC, DEF, GHI, JKL44TomABC, DEF25
The number of rows to be inserted under each entry is listed in column "C" (which is a count of the separate entries in column "B".
The output data following the application of the "solution" would need to look as follows:
ABCDE1PeterABC, DEF, GHI3ABC2DEF3GHI4DavidABC, DEF2ABC5DEF6SamABC, DEF, GHI, JKL4ABC7DEF8GHI9JKL10TomABC, DEF2ABC11DEF
Where the specified number of rows have been inserted underneath the original entry and the separate values in column "B" have been listed in consequtive cells, relating to their original entry in column "D".
Can anyone suggest code to achieve this outcome?
I need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
View 1 Replies View RelatedI 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 7 Replies View RelatedI am needing a macro that will ask for a month and If I type May. It will put 5/31/07 in H1 and then the same date but the prior year (5/31/06) in H2. It would be a real time save if the macro could also ask if the quarter needs to be updated and if yes, ask"input quarter", if 2 is typed then it puts 2nd in H3.
View 20 Replies View RelatedI'm working on a macro code that would create a Purchase Order number based on that day's date. So it's pretty simple since all you need to do is have a macro insert the formula:
[Code] .......
However, i don't want in the PO number any "/" or "-", is there a way to omit these? And is it possible to also make the current year in two digits and not four?
So if the PO number were to be today's date then it would look like this "6614".
Each worksheet has 4 days on it.. and there are 4 worksheets per month ( Jan1, Jan2, Jan3, Jan4 ). I have this macro which runs ( dependant on password ) to lock all cells so they cannot be edited after. On this macro I have to enter the month to lock and it adds on the 1,2,3,4.
Sub LockCell()
Dim i As Integer
Dim ws As Worksheet
Const Pass = "password"
Dim InptPass As String
Dim Mnth As String
On Error Resume Next
InptPass = InputBox("Please Enter Password")
If InptPass <> Pass Then
MsgBox "Incorrect Password", vbCritical
Exit Sub
Else...
I am looking forward to creating a macro that would fill in my planning as per a project name (Cell B11)
Then when I enter the start and end date (range C14:D20). The planning (G5:AH9) would be filled in automatically as per the format in the cell in A14:A20.
refer the attach file. I entered manually an example in for the project "example1". I entered the project "example3" in B11 and starting and end dates. After running the macro button, The row 7:7 should look like the row 23:23.
Is it possible to have a macro run when a date is changed each month instead of clicking on the button. I have a few macros that are ran each month in a spreadsheet (manually) but would I would like to have them run automatically when the date is changed in cell a4 each month. The date in cell a4 is always the last day of the month. Also the name of the workbook changes each month
View 9 Replies View RelatedI would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
I would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.
View 3 Replies View RelatedAm following the below steps to add a function that saves the last saved time of the sheet continuously:
1- Click on ALT + F11.
2- Choose This Workbook.
3- Insert a module.
4- Paste the following code:
[Code] ........
The I insert the defined function LSDate() into the cell i want the last saved date to appear in, but what happens after is that whenever i save the sheet this date doesnt update and when i click enter on the same cell it shows the error message #NAME?. So how can i solve this error and get the last save date updated instantly.
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 9 Replies View RelatedKind 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 2 Replies View Related