Capture Future Changes To The Sheet As The Same Saved Name But To Add And Extention Like (-Rev1).

Jul 21, 2006

I am using the following save as function on a template to save it as a workbook:

Sub SaveFileAsDate()
Dim WSName As String, CName As String, Directory As String, savename As String
WSName = "TMG Work Order"
CName = "C4"
Directory = "Maintenance Service Agreements"
savename = Sheets(WSName). Range(CName).Text & Sheets(WSName).Range("F3").Text & Sheets(WSName).Range("K4").Text & Sheets(WSName).Range("K8").Text
If Directory = "" Then Directory = CurDir & ""

On Error Goto errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Exit Sub

errorsub:
Beep
MsgBox " Changes not saved!", vbExclamation, Title:=savename & ".xls"
End Sub

It works fine but I need to be able to capture future changes to the sheet as the same saved name but to add and extention like (-Rev1). Once it has been saved as -Rev1, each additional time it saves, the -Rev number would increase. I searched the forums but didn't find an exact match.

View 2 Replies


ADVERTISEMENT

Set File Extention As Variant

Jul 1, 2009

I have a button the runs Application.GetOpenFilename And puts the output filepath into a label. I then have another button that opens a inputbox asking to change the file name and then copys the file from above path to my destenation.

when setting the destination i use ThisWorkbook.Path & Application.PathSeparator & "Images" & Application.PathSeparator and the imputbox value as the filename. doing it like this it wont alocate a extention for the file ".jpg,ect". The label.caption has the .ext and i would like to be able to store everythink after the . as a variant.

View 2 Replies View Related

Vlookup Using 2007 And Extention .xlsm

Feb 19, 2010

Im currently using excel 2007 and have my workbook saved with the extention .xlsm. I have put a vlookup formula in a cell and have placed $ constant sign se the formula will look in the same column but different rows. However, when I copy and paste the formula down the rows, the formula captures the same data.

View 9 Replies View Related

Formula To Capture Values From One Sheet To Another

Nov 30, 2009

I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.

I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).

I tried using a SUMPRODUCT but my formula didn't work.

Is there another formula that will give me this calculation?

View 14 Replies View Related

Capture Sheet Delete Event

Jan 23, 2007

I wonder how to capture de event when one choose to delete a sheet. Similar to vbyes or vbno?? Now it inmediatley goes to my sheet_deactivate_event, but then I don't know if it is beiing deleted? I mean capture if you do righclick sheettab

View 2 Replies View Related

Load Saved Csv In The New Sheet?

Aug 30, 2013

I created the csv file from excel sheet. Now I have created a copy of that excel sheet in a new sheet, and I am trying to load that saved csv in the new sheet. But when I do that I see some cells formatting mismatch (can be seen in attached picture) though I clicked on preserve cell formatting at the time of import.

View 2 Replies View Related

How To Get Last Saved Data Into Sheet

Aug 30, 2007

I have an excel sheet which is shared, i need to get the date and time when the sheet is last saved in one Particular cell.

Example: In A1 = 16/082007 09.00am (in whatever format)

View 9 Replies View Related

Saved Sheet To Create Hyperlink

Nov 3, 2009

I have a workbook for Purchase orders.

how it works at the moment is when I fill in the Purchase prder sheet then save it, I have a macro that

Copies Sheet "Purchase Order" creates a new sheet to paste it to then also copies some details to A Sheet called "Register.

I would like to be able to (if possible)

1/ automaticly create a hyperlink between the P/o Nomber stored on the register as it is storing the new sheet &

2/ automaticly change the sheet tab to reflectthe P/order number in Cell G8

I have very little knowlage of VBA & am learning how to record macros so please be gentle.

here is the macro I recorded ....

View 6 Replies View Related

Rewrite Sheet With Some Saved Values

Aug 11, 2006

I previously had a query resolved in this forum when I was trying to write data to multiple sheets but the requirements have now changed and I need to update the script.

I have a multi-sheet workbook that calls a routine to rewrite sheet4 based on values in sheet1 each time sheet4 becomes the active sheet. This script makes single line entries or double line entries in sheet4 depending on whether data appears in one or both of a two specific data cells (col:J,K) in sheet1. However I now need to manually input extra data items into blank cells (col:B,C,J) in sheet4 and retain those data items after input, but each time I make sheet4 the active one it clears the contents of all the cells including the data that I have entered into cols B,C,J. Is there a way of fixing cols B,C,J on sheet4 so that they are not cleared when sheet4 is activated. Or is there a way of writing just the the additional lines added to sheet1 since the sheet4 was previously activated rather than clearing all the data lines written previously into sheet4 and rewriting them all from scratch each time the sheet4 becomes the active one.

I think you will need to see the workbook that I am working on to see how the sheets link and a zipped blank version is attached.

View 8 Replies View Related

Capture Cells From One Sheet Into Another. Hide/Unhide Sheets

Oct 9, 2009

There is 2 parts to my question and I will explain them as best as i can.

1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.

2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.

View 10 Replies View Related

Future Dates ..?

Nov 14, 2007

I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?

View 9 Replies View Related

Lookup Future Forecast?

May 16, 2014

I have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.

View 4 Replies View Related

Calculating Age In Future Using EXCEL?

Jul 1, 2014

I am trying to find a formula which will show the date of a potential student on a certain date e.g 1st August 2014

Cell A2 has the students date of birth

Cell B2 has the students current age

I would like cell C2 to show the students date of birth on the 1st August 2014.

View 3 Replies View Related

Calculating Future Day And Date

Nov 24, 2012

I think I may have over engineered this formula.

$F$40 = Cell with Event Date

=IF(WEEKDAY($F$40,2)

View 2 Replies View Related

Future Value Of Unequal Payments

Jun 13, 2008

Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.

View 9 Replies View Related

Add Future Date To Cell

Aug 2, 2006

I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date.

If Me.Cells(.Row, "N").Value = "O" And Me.Cells(.Row, "A").Value = "" Then
Me.Cells(.Row, "A").Value = "1"
End If

View 2 Replies View Related

NETWORKDAYS And Producing Future Dates

May 5, 2014

I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.

I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.

For example, column A reads:

0
1
2
3
5
7
10
15
20

Estimated completion times for various projects.

So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.

View 2 Replies View Related

Predicting Sales Data In Future

Jul 28, 2009

I need to predict sales data in future using multiple independent varaibles.I used FORECAST function to predict sales value for single independent varaiables.But i dont know how to predict sales using multiple varaiables.

View 10 Replies View Related

Disable Program At A Future Date?

Dec 6, 2012

I am working as a private consultant for a company, and have put together a series of Excel applications, producing charts and providing some statistical guidelines. I will move on from here soon, and will take my work with me. I want to leave this product with the company, but it will become obsolete without some formula maintenance. Therefore, I would like to be able to have the program basically stop working at a future date. I don't really care if it freezes up, deletes itself or implodes. If not I am also afraid many copies of this program will be made and sent to others in my small community.

View 3 Replies View Related

How To Calculate Date In The Future And Subtract One Day

Nov 9, 2011

I'm trying to calculate a date in the future and subtract one day.

The date entered in N38 is 11/09/11.

The term entered in months in C8 is 24.

I want N39 to return the date 24 months in the future minus one day.

So N39 should dispay 11/08/13

How the heck do I do that?

View 5 Replies View Related

Automatically Calculate Date In The Future

Jul 25, 2007

I am trying to create a spreadsheet that will automatically calculate a date in the future.

The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.

If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.

It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd

Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.

View 9 Replies View Related

Variable Dates In The Future - Formula

Jul 15, 2008

How can I create a formula to calculate a future date, when it is not a “specific day” in the future? I mean, I have today's date (Jul 15/08) and I need to calculate from that, when it will be the first Monday of November in 2009.

View 9 Replies View Related

Formula To Referencing Future Worksheet

Aug 19, 2009

Reference a worksheet that has not yet been created, avoiding the #REF! result?

The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.

View 9 Replies View Related

Input Future Date And Time Into One Cell

Oct 20, 2012

I need to input a future date and time into one cell. For example, if I have a member ship that ends on October 20th at 3:00 pm exactly the kind of format you get when you enter the function "NOW". I would like to put that into one cell and then be able to add and subtract from it.

For example I would like to do something like this, if I had inputed the date and time in A1:

"=Now-A1", it would be cool if I can get the remaining hours or minutes along with how many days are left but not completely needed.

It would also be beneficial if I can drill down further and do it by time zone as well.

View 2 Replies View Related

How To Change Row Color Depending On Future Date

Dec 31, 2013

I have some real estate rental so what I did 7 columns and its includes: name, phone number, ....., contract start date, and contract expiry date.

my question is how to make the row color changes depends on the expiry date

for example, the expiry date is 12/10/2014. I want the the row to be red when 12/10/2014 comes.

View 5 Replies View Related

OR Or IF Adding: Display A Date 3 Months In Future?

Nov 19, 2009

Three columns.

A - Date last checked
B - Due Date
C - Actual Date checked

Currently column B simply has =A+84 and will display a date 3 months in future.. Great..

However now i want column B to still display the 3 month date - unless there is a date in C, whereby i want B to then display OK. SO what im sort of needing is =A+84 (or if somethings in C then "OK")

View 3 Replies View Related

How To Translate Number Of Months Into Future Date

Dec 9, 2009

I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).

I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.

For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?

It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.

View 9 Replies View Related

12 Month Rolling With Future Cells Already In Place

Jul 14, 2014

Trying to set up a 12 month rolling sum. However, the only issue with it is that there a re future date cells already in place (august through December of this year). Is there a way to have a formula automatically ignore these by using todays date or something? That was the person using the workbook doesn't have to delete these columns and re-add in the future?

View 1 Replies View Related

Function To Automatically Insert Future Date

Mar 14, 2013

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]...

View 1 Replies View Related

VBA AutoFilter For Past And Future Variable Dates

Aug 15, 2013

Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.

I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.

Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$HQ$1000").AutoFilter Field:=4, Criteria1:="TBD"
ActiveSheet.Range("$A$4:$FQ$1000").AutoFilter Field:=12, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved