How would you calculate the amount of days of a billing period for a consulting engagement by quarter when the engagement could span multiple quarters, be within a quarter or not be in a quarter at all? Project based billing
I have just been passed a new VBA project and there is one component I would like help with.
Column J lists a start date Column K lists an end date Column N lists a dollar amount. Columns P through the last column has headers of month-year in text form(for example May-08)
The user enters contract information in the spreadsheet including the above cells. What they want to automate is once the user enters the contract information on a given row, she runs a macro to determine the billing schedule by calculating how many months there are between the start and end dates, divide the dollar amount in column N by that number and then prompt the user to enter what month the first payment is due, once that is entered, the macro should place the payment amount in that column and all applicable subsequent columns.
For example, Column J = 6/1/2008 Column K = 9/1/2008 Column N = 3,000 The macro should calculate 3 months, the monthly payment amount is $1000. A starting month prompt appears and the user enters July-08. The macro then places 1,000 in the columns for July-08, August-08 and September-08.
The data from the Data sheet is copied over to the Report sheet with formula.
Basically, what I need to do is If statement: -when there is no any data in csv file (so the Data sheet will be empty); -the range (A7:N1000) to be hidden (on the Report Sheet) -the Report sheet to be saved
I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report.
Sub Update_Report() ' ' Macro1
Dim extract1 As String Dim dReport As String Dim rSheet As String Dim dSheet As String
X Y Z Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep A B C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open if workbook.name "inventorysummary.csv" then application.run "personal.xlsb!capacity" end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.
I have a single workbook which has a single datasheet, and further sheets representing different months of the year.
Sheet 1 ("Data") contains the following columns and data:
DateStart MileageEnd MileageBusiness TripBusiness Mileage 01/05/2008013 get 13 02/05/200813260 03/05/200826100 euro 74 04/05/2008100113 adf 13 05/05/20081131130 06/05/20081131130 07/05/20081131238 alex begg1125 08/05/2008123812510 09/05/200812511288 service 37 10/05/2008128812980 11/05/2008129813110 12/05/2008131113780 13/05/2008137814000 14/05/2008140014560 15/05/200814561490.10
I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.
Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".
To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.
I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR PAGE 1 01201010 CONNECT SUMMARY CPU 046E16 REPORT DATED: JUN 12, 2012 17:53 INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED: DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm' 1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
Is It Possible To Auto Save To A File Via A Macro?
What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
In this attached example, this is a portion of a actual work procedure. The yellow highlighted cells are the same work element within the master procedure but one is for one type of machine while the other is for same model but added options. This is a pattern throughout the entire worksheet. Basically 1 model with several different types of options. I don't want to have unnecessary elements in a work procedure.
So what I am trying to accomplish, I want to create a userform or dashboard which will allow selection of model #, model type #, then based off those selections have excel populate a worksheet with all correct procedures based off model type choice.
In this example there is 6 model variations, all share same basic procedure, but all have their own work procedure based on options, so my above approach will allow me to make changes to the master procedure only instead of changing 6 procedures.
I am trying to create a transfer of data from one worksheet to another, where Column G is where new data will be entered. I have a macro to sort the data (works great!!), but it is just getting it from one worksheet to another without having formulas in the cells - which can be deleted by my co-workers - to pull the data from one sheet to another.
I do not know if this can be done automatically or not, or if info is updated on one worksheet vs another - if then it will transfer the text.
(A2)LAST NAME (B2)1ST NAME (C2)RES# (D2)HOUSE (E2)DOB (F2)JOB (G2)SPECIAL (A3)DOE (B3)JOHN (C3)112233 (D3)1 A2 L (E3)10/10/75 (F3)YES (G3)DR APPT
There are over 300 entries - one row new record. The CommandButton1 works great that I have for compiling and deleting unused rows.
But now I need something to get the data to the blank worksheet to compile data from Column G without formulas in the cells. This was one I found online, tried to adjust it to fit what I need, but I cannot get it to work.
Sub CopyRow(ByRef Target As Range) Dim DstEnd As Range Dim DstRng As Range
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
I'm looking for a formula that will paste an entire row from sheet 2 into sheet 1 of the same workbook when only 1 cell is typed.
I am trying to come up with a property access log for my guards so when they enter a tag number from(sheet 2 column B) into (sheet1 column D) , it will auto fill sheet1 column C and E with the entries from sheet 2 column A and C.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on. from row 2 onwards the data has been populated for roughly 200 rows.
i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet.
my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'. how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row? i know this is possible i just dont know how to do it.
1) How can I create 2 lists dependent on the same primary list?
2) Once I get number1 resolved, I have a data sheet of 30 col and over 6k rows. I need to create a report that when a user select the 3 options from the dropdown lists (Category, Location, Product) the Report sheet will get populated with data from my data sheet based on those selections. I'm looking to have somewhere between 10 and 13 columns (numerical, general, text data type). Is there a way to automate this process vs. writing 10s of lookup formulas? to bring the data in? My workbook is already over 25MB!
Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.
Below is the code.
'Description: Combines all files in a specific folder to Format File for Upload.xls Sub MergeMultipleFiles() Dim path As String, ThisWB As String, lngFilecounter As Long Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet Dim filename As String, Wkb As Workbook Dim CopyRng As Range, Dest As Range Dim RowofCopySheet As Integer
I have three cells checked to make sure data is in them and then the code is meant to save the file with some of the data from those three cells. The checks work but now the save part doesn't! If I comment out two of the checks the save does work.
Linked to http://www.excelforum.com/excel-programming/625320-auto-save-a-new-file-with-data-from-3-cells.html
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB: Sub CopyFromFile() Dim fPath As String Dim lRow As Long
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate.
So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined.
Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.
*Inserts an existing worksheet template and places it at the end (or after all) the existing worksheets.
What I would like the macro to also do is:
*Automatically rename the inserted worksheet (via my worksheet template) in sequence to the existing worksheets
Example: So let's say I have 3 existing worksheets titled Sheet1, Sheet2, Sheet3 (that's basically the default naming Excel uses). But would when I insert a new worksheet (via my worksheet template) it is shown as Sheet. And if I insert another one, it would be Sheet (2). So basically it is showing as such:
Sheet1, Sheet2, Sheet3, Sheet, Sheet (2)
What I would like the macro to do is automatically rename those last 2 worksheets (and any subsequent worksheets thereafter) in sequential order. Basically displaying it as such:
Sheet1, Sheet2, Sheet3, Sheet4, Sheet5...(and so on and so forth)
I am running a auto filter macro to I need to change it to only copy column A:B and past in column C:D in the sheet called "Diary". How to change my current macro to only select column A:B and paste it. It is currently pasting the entire row.
I'm fairly new to macros but somehow i managed to create all the required macros for my project through googling and the like. The problem is that i created my macro enabled excel file in XP and when i transferred it to Windows 7 the auto save to PDF macro stopped working with the Runtime Error '5' Invalid Procedure, call or Argument. The highlighted error is as follows.
I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.
Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:
VB: Sub EXPORT_ALL() ' ' EXPORT_ALL Macro ' This Macro saves the sheet then exports all tabs as a PRN.
So im setting up a big list of stuff, and basically i have multiple sheets.
One of them is a "master sheet" so to speak, and the rest are sub-sets of data.
Is it possible to have the Master sheet updated automatically every time data is entered on one of the sub sheets?
Basically its an alphabetical list of names, and my sheets are "Master sheet" "sheet A, Sheet B" etc .... so If I enter a name on Sheet B, or format the cells on sheet B, is it possible to have that automatically copied to the master sheet?
its becoming a pain to have to enter the same name on two sheets manually.