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.
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.
I'm trying to rewrite our commission spreadsheet with a new structure and as my excel knowledge is limited, I'm not really getting anywhere.
I'm looking for a few lines of formula however I'll just post one at a time otherwise I'm in danger of scaring you all off!
Firstly, I am trying to work out the formula for the following:
If the value in C7 is up to £14,999 = 1.5% of the whole value is given, if it's over £15,000 = 3.5% of whole value is given. I would like the total amount of commission to show in F7.
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.
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format. That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values. I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value) which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS") On Error Resume Next If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _ Cells(i, "M").Value Int(Cells(i, "M").Value) Then Cells(i, "M").NumberFormat = "# ?/?" On Error GoTo 0 Exit For 'End If End If
i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......
in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.
I am trying find a match from multple "text" values.
The values I'm using are flight numbers from sheet "Indiv case" in column (range H2:H51). The flight number could occur multiple time in the column.
The associated flight number sheet "Code & categories" in column (range H2:H257) are associated with the last port of embarkation (range I2:I257) in "Code & categories" sheet.
I need to copy & past the name of the Last port of embarkation from sheet "Code & categories" into sheet "Indiv case" adjcent to the flight numbers in column (I2:I51).
Example: Sheet "Indiv case" from Column (H2:H51) Fligh number Data: UA863, VA4148, EK432, BA15, BA15, VA98, QF8, AC33 etc Using these value from "Indiv case" from Column (H2:H51) search and match valuse in "Code & categories" in column (range H2:H257)
If match found copy valuse from sheet "Code & categories in column (i2:I257) in to sheet "Indiv case" into column (I2:I51) Last port of embarkation".
H2;H257, I2:I257 Flight, Last Post 3k111, Singapore 3k131, Singapore AC33, Vancouver
Copy and Past "Last Port" into sheet "Indiv case (I2:I51) adjcent to matching flight code.
I am getting the following error message while trying to save an excel file into server location.
Your changes could not be saved to [filename], but were saved to a temporary document named [number]. Close the existing document, then open the temporary document and save it under a new name.
I am using: OS: Windows XP Professional with Service Pack 2. Office 2000 Server: Windows 2003 with Service Pack 1.
The table on Sheet 1 is being used a record system for history of each lifting equipment we mobilized to our client for rental. The Door No. (Column A) is a name for each equipment. The Date Mobilized is the date when equipment left our premises and started working on site and Date Demobilized is the date when equipment went back to our yard.
To explain my requirement, refer to the first 7 rows indicates that the Door No. ATC0005. It was mobilized 7 times but demobilized 6 times. Meaning that the equipment is currently deployed because the last mobilization dated 01-Jul-13 doesnt have an corresponding data of Demobilization. My first requirement is to have a filter where in it will show the list of currently mobilized equipment. In that case I just go to Date Demobilized and filter it by only showing the blanks.
My second requirement is to know how to filter to only show the available equipment. I was able to analyze it this way: For example we take a look at the history of FLD0001, it has 5 Mobilization Date and 5 Demobilization Date. So i need to know a function to filter a Door No. that has the same number of Mobilization Date and Demobilization Date. My first solution is to just filter the Date Demobilized to not show blanks hence it will show all equipment that has the same number of Date Mob. and Date Demob. But my exact requirement is to have a real time report on a separate sheet (refer to Sheet 2).
Sheet 2 is report for equipment status. Under Equipment (Column A) are the list of equipment. Using the data available on Sheet 1, the cells under Client (Column B) should be filled up either "Available" or "Deployed".
Lastly, for all Deployed equipment only the Client Name should also be retrieved on the report on Sheet 2.
I'm trying to copy the cells from a sheet and transfer only the values to other sheet.
I did it via code and it worked fine, except for the dates. In the new sheet the months and days are swapped.
The original date is composed via the concatenate function, since it gets inputs (day, month and year) from the user in different cells. It's in the format D/M/YYYY (examples: 4/2/2014, 10/12/2013). I believe the excel interprets it as Text, even if I formated the cells to Date.
I think it may have something to do with the default format in different countries. Here in Brazil we use DD/MM/YYYY, but my Excel is in english and in US the format is MM/DD/YYYY, am I right?
Below is the code for completing Sheet 1 using various comboxes on a User form. There are only 15 lines to which values can be entered on sheet 1. After the 15 lines have been completed, I would like to begin adding new data to sheet 2. However, I'm not really sure where to begin in terms of setting up a counter and then calling sheet 2 once the counter hits my limit. Sheet 2 is exactly identical to sheet 1 in layout and also has 15 lines.
Private Sub btnAdd_Click() ' This button will add medication info to excel worksheet 'RowCount will help find next empty row
Possible to look up a value from one excel sheet in another sheet and then return multiple values in the first sheet
For example the following is a sample of sheet 1
NSV Code Item
12 OLANZAPINE
54 ROPINIROLE
And the following a sample of sheet 2 :
Nsvcode Item Division Speciality Qty
12 OLANZAPINE CLINICAL CS 10
[Code] .....
I want to lookUp the NSVCode from sheet 1 in sheet 2 and return the speciality and qty values in sheet 1 as sheet 2 contains a lot of info I do not require and sheet one also contains various pricing calculations I also require.
I have to match table column name with the column name in the sheets and get the values from column 6 in sheet2 if "# of values" in sheet1 matches with "Counter" of sheets for the same column.
if ("Table Column Name sheet1"="Column Name sheet2" then if("Counter sheet2"="# of Values sheet1" then return "Total # of rows Sheet2" from same row. I am using Excel 2003.
I took two weeks perfecting an Excel file and was writing them to disc just now to transport and use on other machines. I opened the file to add instructions and the files are now read only? Propteries show read only on the written files but not on the originals???
I have the below formula that pastes data into the worksheet entered in the " " it works great but only when the workbook was last saved on that sheet, how can it locate that worksheet sheet even if the the workbook was last saved and another worksheet. if the workbook was saved on a different sheet name the vba doesn't finish
I have code written to auto save the active worksheet when a button on my user form is pressed. What I would like to know is if there is a way to lock the worksheet when the button that triggers the save event is pressed behind the scenes so the end user has a more difficult time editing the sheet. I know they can just resave the document and the protection will be gone, but for minimizing user manipulation.
I chose to place this in programming, as all the functions for this workbook are either macros or Internet Links. Nothing is ever changed on the worksheet.
I have a workbook that is fully protected with passwords both for the workbook and the worksheet. When I set up the protection, no check boxes are checked.
I have macros and internet links on the worksheet that can be selected, but none of them affect the worksheet.
As a matter of fact, I can open the workbook and close it with only "Application.Quit" in a macro and a message box comes up asking if I want to save the changes or not. If I close it with the upper right corner RED X, it still asks the question.
I have no "On Open Macros" or Links of any kind.
I have other workbooks that this does not happen on.
I have a file that keeps setting it's "Saved" property to False when I change something in another file. I cannot find any external links in the file. The file used to be "Personal.xls", but I renamed it.
I have an excel file in which the date format for a column was in the following format -: 3/14/01 Since I wanted the column to display in 'mm/dd/yyyy' format, I changed the type to -: 03/14/01 and the sheet was saved. I then for a specific purpose converted the xls file to csv and noticed that the date formatting I did is not getting saved. That is, every time I open the csv the date format changes back to 3/14/01.
how to retain the date format in excel? I need to use the date format in mm/dd/yyyy only for the application I'm using.
does anyone have a simnple code that when entered in a cell only returns the last date the file was saved? better yet, could it somehow be specific based on a modification to a specific worksheet modified?
example: i have 8 worksheets. how can i show when one worksheet is updated, say November 27th 2006, and another worksheet on November 28th 2006?
Is there a formula that will get the date & time the workbook was last saved? I am using an earlier posted macro now but would like for the information to be visible constantly in a certain cell. Possible? Macro used: Sub WhenSaved() MsgBox "This file was last saved on " & Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mmmm d, yyyy") & ".", 64, "Date of most recent save:" End Sub
Macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date.
I have a file with code attached and when I try to save it I am told that the file cannot be saved in this format and that I must unprotect the code before saving.
I have no OnEvent macros in the code.
Does anyone have a solution for this problem. The code must be protected, non-protection is not an option.
One of the sheets is a sumamry sheet that get's dulpicated, values pasted, then (the single sheet) exported to a new file. Upon trying to save this file, I keep getting the run time 1004 error message and that my document cannot be saved.
I've struggled for days trying to find out the source to no avail.
What is more frustrating is that when I try to save it (after exiting the macro), it seems to work ok.
I do get the compatability error message (which I don't get on the 'original/parent' workbook) there is a not about format errors, though I'm not sure that's the source of the problem.
Private Sub Workbook_Activate() Dim user As String If Worksheets("Setup"). Range("D6").Value = "" Then user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name") Worksheets("Setup").Range("D6").Value = user MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.") Else user$ = Worksheets("Setup").Range("D6").Value MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.") End If Exit Sub
but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.
