after macro is finished I want to put file name on in field
Code:
Range("HPU").Value = fileToSave
fileToSave - it's give me whole path, like: C:DBNewV1Testjan.csv
but I want to have just name of the csv (jan.csv).
How can I del the rest?
I have got this in a pivot table
Customer Total sales(USD)
30010 500.00
30041 2000.00
In another column I enter a comment for customer 30010(and leave the comment column for customer 30041 blank) as below
Data 1.xls
Customer Total sales(USD) Comment
30010 500.00 Test 1
30041 2000.00
Later on the content of the file changes as below:
Customer Total sales(USD)
30025 1500.00
30010 500.00
30041 2000.00
When the file is refreshed I would like to have comment as per the former file i.e the comment “Test 1” should appear against customer 30010 and similarly for any other customer that I enter the comment.It should be as below:
Customer Total sales(USD) Comment
30025 1500.00
30010 500.00 Test 1
Customer Total sales(USD) Comment
30041 2000.00
i have a file that needs to have daily postings to it, I would like for it to automatically generate today's date and then if you change figure 1, it carries over to figure 2.
View 3 Replies View Relatedcoding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies View RelatedIn building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedI have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies View RelatedI have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
If a5=15, i would like that c2=d? (but that ? Must be value from field a5)
View 12 Replies View RelatedI have run into a problem I have not found a solution for surprisingly. I need to calculate several fields on the form prior to saving it and thus exporting the data to the worksheet. All I can find is how to calculate the field in a worksheet and then reference it with VB code on the form. Is what I want to do possible?
If I run into many more problems I may want to shop this project out as my schedule barely allows for the work I have let alone attempting to learn VBA on the fly.
If anyone is interested I can post the project so far along with the detail of what I want it do in the end.
Let's say that if I put number 1 in cell a1.
I would like that i can write something in cell a2 (BECAUSE A1 IS 1) and that cell a3 is disabled (blocked) foR any use if cell a1 is 1.
is it possible to use two filters within a the same field.
ie. right now i can filter using the top 10 based on a value field.
what if i want to see the top 10 customers profit and along any drop in revenue from previous years..
something like this.
the information below was copied out of a pivot table that included the % difference.
Code and Name
YearCheck
Profit
Difference
[Code].....
if value of cell A1=1 then add 1 to cell H2 else add 1 to cell H2.
Basically, I want H2 to go up by 1 if a1=1..
This is more of a basic syntax than Excel.. Is there any way to translate that into something Excel can do ??
I have a spreadsheet which has several fields containing data and
what i want to achieve is that when someone comes to close the
spreadsheet , If they have not filled in all the fields required it will
put up a notice to say that field **** must be completed.
Spreadsheet A
Stock Name: This field changes with a drop down box i have created
Depending on which stockl you select it goes to Spreadsheet B to select data from column 2 based on date. In spreadhset B all the stocks are listed, so not only am i trying to lookup data but also return value in co,umn B for that particular stock i selected. So the idea is i can change the stock in teh drop down and then it will return the values i want.
how to write a formula that shows the sum of a row of numbers and then it's percentage of the overall figure in brackets in the same field e.g.25 (75%)
View 9 Replies View RelatedI need to be able to filter out the same field with multiple macros. Like I need to filter out all rows with "A" in them and I then need to be able to filter out all rows with "B" in them but without restoring the "A".
I can make the multiple filter work just fine as long as the Field is a different value but I can't figure out how to make it work with the same field.
I want to write code that will allow me to show the Missing Names in the Old sheet that aren't in the New Sheet.
I want the code to go along these lines:
Dim a As Long
a = 2
Dim o As Long
Dim p As Long
Dim last_row14 As Long
Dim last_row24 As Long
I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .
View 2 Replies View RelatedTake a look at the attached spreadsheet: Value_Lookup.xlsx
We have a pivot table with the "Product" on the Row context and a date ("FullDate") selected on the filter context.
The FactTable is related to the Calendar table by "FullDate" (which is marked as Date Column in Powerpivot)
In the Calendar Table, next to "FullDate" we have 2 more dates in the same row: "1 Month Back" and "1 Quarter Back".
We just need 2 calculated fields that return the "Price" value from the selected "Product" on the Calendar dates "1 Month Back" and "1 Quarter Back".
I have a cell tha't set up as a number format with the validation only accepting 3 whole number characters, however if the number is 098 then it converts it to 98. I need for that cell to be a number type format and to accept 0 as the first character. I thought I tried everything but I'm sure I'm missing something.
View 9 Replies View RelatedI want to know how to take a field with a value and apply it to fields with range. In this case if i had a value of 15 it would enter that value in this field
below. I tried an IF formula but it did not work.
range 31 to 40
range 21 to 30
range 11 to 20 15
range 1 to 10
H2 formula is =OFFSET(A4,MATCH(MAX(D$4:D$1000),D$4:D$1000,0)-1,0)
I2 formula is =MAX(D4:D26)
If either of these result in a ZERO is it possible to leave the field blank?
I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.
I have lots of data, and it would be impractical to do it manually. Here is an example: ...