I work with alot of reports that are pulled directly from an application.
On each report i need to 'Alt=' at each cost center so that all manual changes are picked up.
However as you can imagine with thousands of rows this could take a while and be very tedious. Is there a quick way to do it?
Subtotals will not work because the costcenters will appear in the same column as the employee numbers.
I import lots of data into a spreadsheet then have to manually format it which takes hours, I want code to automate it.
Example: I have data such as:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
12-----Bino------etc-----4--------6
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
I want it looking like this:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
shaded cells to here ishh 4-------7
No-----Name-----Date---Price----Cost
12-----Bino------etc-----4--------6
shaded cells to here ishh 4---------6
No-----Name-----Date---Price----Cost
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
shaded cells to here ishh 11-------11
The difficulty is due to the fact that the number in No column varies, for example there may be 2 rows of 10 or 4 rows of 10. What is the quickest way to format it all too, so have a border and font size etc all the same?
At present I have to manually insert blank rows between the different numbers in the No column, copy column headings, then manually sum up Price and Cost, then insert borders. But for hundreds of records.
I found this code to insert 3 blank rows manually for me when the numbers in No column are different:
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
Is this possible and if so what VBA code
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
View 9 Replies View RelatedI have created a userform which is initiated off a button in her spreadsheet, where if she enters an order number, it will display the values from the row containing that order number. I have made it so she can load from the excel sheet the values into textboxes, where she can change values then save them back into the excel sheet.
I have also include the ability to insert a new row at the end for a new order.
Everything works fine except the date values and I'm a bit stumped, I have searched but can't find anything that helps (I thought DateSerial helped but it didn't).
I've had two problems. the first was that the day and month swapped, I could load 1st Mar 2007 from the excel sheet and when stored back it changed to 3rd Jan 2007. This I presume to be due to US and UK date formats, I am in the UK.
I thought I had fixed this by changing:
oDisplay.Value = vStoreArea .............................
how do i use worksheetfunction save to save a particular sheet everyday at a certain time e.g. 5.30pm to a particular folder.
Also the name of the file will change with the day.. e.g. 22_Apr_07.xls for today.
And to also refresh the page / recalc before saving at 5.30pm...
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
View 3 Replies View RelatedIs there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist:
this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
View 9 Replies View RelatedI have a workbook with several worsheets in it. I wish to have the first worksheet as a list that will be hyperlinked to the other worksheets within the documant.
Is it possible to save the workbook so that it will open at the first worksheet every time irrespective of what worksheet you were on prior to clicking save.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View Relatedthe vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.
Here is a scenario:
- i request for a product information from a vendor on 3-Feb-14 8:00am (Monday)
- he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)
can you find the time in above scenario consideration the working window?
Here is another scenario:
- i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday)
- he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday)
- Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.
I'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedI have been making and so i thought saving a module however every time i close the spreadsheet that module is associated with it deletes. is there any way to prevent this from happening.
Also when trying to edit on PERSONAL.XLSB!Module1 i get a particular error stating
"cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command."
The aim of the macro is to save all the sheets in .txt files named as the original sheets.
View 3 Replies View RelatedThe dialog box comes up with file name formatted correctly and shows .xls as file type but when you choose save the dialog box disappears as if the file was saved but the file never gets saved.
View 3 Replies View RelatedI am trying to write a script to import, reformat and save sales files for upload into our master server and had a question.
how to make it store a range as a dim to use later. I would use
Code:
Range(ActiveCell,ActiveCell.End(x1 Down)).Select
on all the columns, but there are often blanks in many of the rows and I am worried about potential errors.
There is a column which always has all of the rows populated, so I was hoping to could store the range of that column and use it for all the others, so they all match length.
I have a workbook with several users. I'd like to present the user with a message when he/she hits the save or save as button, such as "Have all entries been verified?" with a yes or no option.Need to allow save when either button (yes or No) is entered.
View 9 Replies View RelatedIs there a way to save a formula in Excel, or do I need to use the macro function. I would rather not use a macro due to how simple the formula is but I use it often enough that I do not want to retype it each time.
View 9 Replies View RelatedI'm rather new at VBA
I have a small workbook with a userform and some buttons. Each time I press a button a counter increases and the workbook is saved.
This saving seems much too long for such a small file.
I use
Activeworkbook. save
I have two worksheets in a spreadsheet but after I use it I want to be able to click a button and just save Sheet2 Alone. Can I do this.
I have this that works but would need to change it up to just save sheet2
Public FilePath As String
Sub SaveAs()
Dim strSaveAsFile As String, fp As String
FilePath = ""
' Change the FilePath to suit
fp = "S:Depot Outgoing"
Call MakeFolders(fp)
Call MakeFolders(Format(Date, "yyyy") & "")
Call MakeFolders(Format(Date, "mmm yyyy") & "")
Call MakeFolders(Format(Date, "mmm dd") & "")
strSaveAsFile = UCase(ActiveSheet.[B8].Value) & ".xls"
ActiveWorkbook.SaveAs FilePath & strSaveAsFile, xlWorkbookNormal
FilePath = ""
End Sub
I have 2 sheets in a spreadsheet I would like to Save Sheet2 ("Packing Slip") ONLY!
If I can I am trying something like this.
Public FilePath As String
Sub SaveAs()
Dim strSaveAsFile As String, fp As String
FilePath = ""
' Change the FilePath to suit
fp = "S:Projects"
Call MakeFolders(fp)
Call MakeFolders("PCAR " & Format(Date, "yyyy") & "")
Call MakeFolders("PCAR " & Format(Date, "yyyy") & " OUTGOING" & "")
Call MakeFolders(UCase(Format(Date, "mmm yyyy") & "")).........................
I have excel 2003 for XP and I have a excel file that is a csv. I used easyfilter to filter my results. (I wanted to delete complete rows that had no information in a certain field in column) Everytime I complete the task and try to save it, it tells me file.csv may contain features that are not compatible with csv do you want to keep the workbook is this format? -to keep this format, which leaves out any incompatible features, click yes. -to preserve the features, click no. then save a copy in the lastest excel format.
My question is this...is there a way to bypass this? Is there a simpler way to delete those rows without using easyfilter so that I can save it after the rows have been deleted.
I have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to:
- keep this workbook with the 20 tabs when open by the user;
- let the user select and fill in whatever tab he needs (just one at a time);
- have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
I tried to implement many other Saveas macros into it. from other post and members. I can't seem to get it to work. As the macro is, It saves the copy to My Documents. But, I need it to save to the dierctory below. Any help will be appreciated.
',//Tom's-srv01sharedQC DocumentsCustomer Inspection Reports
Sub myBuildDupWB1() 'Static Inprocess
ThisWorkbook.Unprotect
Dim sht As Object
Dim strMyNewWB$, strMyOldWB$
On Error GoTo myErr
strMyOldWB = ThisWorkbook.Name
Workbooks.Add
strMyNewWB = InputBox("New Customer Part Name, below:", "Name Workbook!", "Rename Blank InProcess Op")...........
code for saving individual sheets from a list box, i have the code to print individual sheets but i need to be able to save a selection of sheets at one time to a given file.
View 9 Replies View RelatedI have a form which has col A Surname Col D Post Code Col E The month (for example)
through a formular this gives the unic customer reference number surnamepostcodemonth given in col G but when I save the document I have to copy and paste the value in G1 for the file name is there away of pressing Save As and it auto fills the file name?
the actual value in the field G1 is
='Raw Data'!D2&" "&'Raw Data'!E2&" "&TEXT(('Raw Data'!F2),"MMMYY")&" "&'Raw Data'!G2
I obviously want the result "Smith TW6 Jul09" to be the file name when I save it..