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 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 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.
vba to save data in cells into file name? The vba below works for one cell, but I need info from cell “E11” also with the text MARK between them. myFileName = "2009 CROP BALE CUT OUT FORM – INVOICE " & Format(ActiveSheet.Range("F12").Text) & ".xls" The final file name should look like this: 2009 CROP BALE CUT OUT FORM - INVOICE 8D4512 MARK CB34.xls
I have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).
I have a workbook where Sheet A is a Master Order Form. Employees will input the quantities they need in Column C. The sheet will be otherwise protected. Sheet B is a "printable" order form that only contains the rows from Sheet A where the Quantity on Sheet A is not blank and is >=1. This will allow me to print sheet B with no blank rows for the items I don't need.
I found the following formula which claims to be able to do what I want:
I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.
I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.
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
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.
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I have a problem with a very simple Lookup (please see attached). When I copy/drag down the dropdowns in column A and the Lookup formula in column B, the lookup up does not recognise the value in column A till I save the sheet. The same if I select a different value from the dropdown i.e. the lookup only works when I save the sheet. Am using the wrong Function to do this or it something else.
My coworkers and I use a couple of Excel documents to store statistics. The document is on a network share. We all have Office 2010, my coworkers are on Windows XP, I am on Windows 7.
If I open the document and make changes, then save it to the network location I can open the document later and see the changes but my coworkers do not see any changes I have made. Also If my coworkers make changes to the document the 3 of them see the changes but I do not.
If I go straight to the network share the timestamp on the document only changes for changes I made.For example if I go to servernamefoldernamestats.xls and add data to the file. Then save and close it. I can reopen the file and my changes are there.
Tomorrow Coworker 2 opens the same file from the same location but it has data from 2 days ago, with a timestamp of 2 days ago and my data is missing. They then reimput the data I entered, and add todays data. Then they save it and close.I open the file it has my data from yesterday, but not the new data entered by coworker 2.
I am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?
Here is name code:
[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that
If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...
I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.
So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed