I have the a file with the below Columnar format:
CoName April May June... till Mar
I have 15 Company Names. I need to create a file for every CoName-Month Combination.
CoName April
CoName May, etc
I do not want to create 15x12=180 files manually.
I have attached one excel sheet. I have programmed this in such a way that it always asks the name of the person who opens this excel. Then the name & date with time autometically noted. I have used password "007" in this excel sheet. The code is below:-
View 14 Replies View RelatedFor each new case they have a spread sheet that they edit - I would like for this spreadsheet to lock a month after it is first edited/updated.
View 2 Replies View RelatedI have a template file that I import data in to, which is then saved with the current date. This works, but I want to automate the import of the data, but ensure I don't duplicate the load.
Therefore, I have a tab 'FileLog' that I record the filename (variable by date) in column A and Creation timestamp in column B.
How do I obtain the filename and creation timestamp of the import data so that they can be entered?
I put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.
View 5 Replies View RelatedI am wondering how you can change Excel's original file creation date. You can find it by right-clicking on a speadsheet icon then going to:
File - Properties
Custom Tab
Click on Advanced
Look for a field called "Date Created"
Some dates you can edit/change, but I haven't figured out how to change this one yet. I'd like to know because I have a need to change the date on a particular spreadsheet.
I need to create 69 charts with 4 different series in every chart displaying information like average stock growth and so on. Is there any smarter way i can do that than manually when i know that the range of these charts will be consistent?
View 14 Replies View RelatedAll my files is in C:UsersmarrecoDownloads All files has name formatt (yyyymmdd) -> 20140814 I need import only last month files
Every day a system plays text files within the (C: Users drake Downloads ) folder.
E.g. I have to import only the files that have the name (yyyymmaa) based on last month.
[Code] ......
I have the following variables in these columns
Column 1: Ship (1064, 1065, 1066 as the field contents)
Column 12: Date (21-Feb-08 as format)
Column 13: Weld Length (1000 as format)
Column 15: Defect Length (1000 as format)
What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned.
There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
I am using the following formula and it is working fine. Is there a better way to do so?
=IFERROR(AVERAGEIFS(INDIRECT("Data!$U"&$B$1&":$U"&$A$1),INDIRECT("Data!$A"&$B$1&":$A"&$A$1),">="&D$4,INDIRECT("Data!$A"&$B$1&":$A"&$A$1),"<="&D$5,INDIRECT("Data!$U"&$B$1&":$U"&$A$1),"<>0"),"-")
I am looking for a better way as I have to replicate the same formula in 100 columns and in 60 rows. Where every row use a different target column (like the above mentioned is using column U.
Indirect function gets the values from A1 and B1 cells (Start and end of column because end value keeps changing with data updates)
AverageIfs get average of a particular value between two dates mentioned on top.
i have attachmed excel file. in that file total 8 colums among them i kept 8th colum empty .
but i want name racks in that file. i knw..it will confuses u. but once u see my attachemts u will realize.
eg: if G2 =XA06-G-001 I2 will be XA only..(XA is rack Name)
if G2 =XB06-G-001 I2 will be XB only
if G2 =XC06-G-001 I2 will be XC only
if G2 =XD06-G-001 I2 will be XD only
i mean to say..i want only first two alpabatics of G2 in I2..
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
How to automatically list down associates if workflow is mentioned in the column? Like in attached sheet, in column B workflow is listed down and in column C name of associates should get populated in sequential manner from column F, G and H. For example A, B, C and D is aligned to workflow X (Treating this as primary source), whenever in column B, workflow X is populated (manually) it should list down list of associates in order - A then B, then C, then D; once cycle completes, it should repeat again A, B, C, and D.
View 1 Replies View RelatedFor Each Ws In Sheets(Array("SHEET101", "SHEET102", "SHEET103", "SHEET104", "SHEET105", "SHEET106", "SHEET107", "SHEET108"))
With Ws
Finalrow = .Range("A65536").End(xlUp).Row
Set CpyRng = .Range("A2", .Cells(Finalrow, "AR"))
If Finalrow > 1 Then
CpyRng.Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp)(2)
End If
End With
Next Ws
Basically what it does is simply combine all the records in the the mentioned sheets to the master sheet.
There is a little problem. When one of the sheets are on a filtered mode, the data copied in the "Master" sheet are only visible cells.
Un-filtering before copying is an option (i.e. putting the code ".ShowAllData") IF I can put the exact filtering back after copying. Reason being that the sheets are owned by other parties and they do not want their own filtering be removed.
I have searched the forum regarding copying data from multiple worksheets into a main workbook, but I did not find anything that tells me how to copy data from multiple csv files into one workbook tab, I can see a thread created by Hiport which is very similar to what I want.
I have a CSV file saved for each day of the month, these files are saved in their monthly folders, so all the CSV files for May will be saved in U:CustodiansInterestYearMonth(May2008), now in this folder will also be a master workbook called “Interest-May2008”.
What I do next is very manual, I have to copy from each CSV file, data from Col 1 and Col2 and paste this to the master workbook- sheet (downloads), all the data will be pasted next to each other, i.e. col 1 and 2 will be data for 01-May-08, and col 3 and 4 will have data for 02-May-08 etc, as you can see it’s a very daunting task having to go through each file and copy and paste.
I need a macro written so it copies data in col 1 and 2 in each csv file and then pastes that data in a master workbook sheet (downloads), the data will be in date order of the CSV files, I want the data pasted in row 3 of the master workbook (downloads).
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).
CSV files are something like that (I need only first 4 columns):
ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"
and my Worksheet is like that (With a command Button):
I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...
ABCDEF1NameMSISDN DatedBlankBlank Location2
3
4
5
6
7
I have the Month and Year in the name of the workbook for eg Sales July 2013.xlsm
I would like a macro to extraxt the month and year from the file name and put this in shewet2 in cell E1
I have a macro that saves a workbook with the month name automatically generated in the filename with the code below (apologies for the scrappy code, but I am rather new to VBA)
ActiveWorkbook.SaveAs Filename:= _
"I: EngineeringCapacity PlanningCapacity Report CONTROLS " & Format( Date, "mmmm") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
In another file I want to re-open this workbook. However, the problem arises because this will not happen till the next month, in order to update the new month's workbook with the old information. Currently I am 'cheating' by modifying the code with the actual month name, like this
Workbooks.Open Filename:="I:EngineeringCapacity PlanningCapacity Report CONTROLS April.xls" I have tried to use the Dateadd function (with a -1 as the add amount) but I have failed to make it work.
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
I want to compare performance data YoY, updated monthly on a YTD basis.
Sheet1: columns of data as follows
Jan11 Feb11 ... Dec11 Jan12 Feb12 ... Dec12
1000 200 ... 500 800 900 700
...
Sheet2: If we have data through July 2012, I want to show a sum of Jan-Jul11 against Jan-Jul12 on a separate sheet.
Cell1 (updated each month) = July
ColumnA: Jan-(cell1) 2011
ColumnB: Jan-(cell1) 2012
Is there a formula I can use in columns A & B that will reference cell1 and update using the data from sheet1 automatically when cell1 is changed each month?
In column (A:A) A1="Dates" (Sorted A - Z). For e.g. 1st date is 27-12-2011 and last date is 18-03-2013. I want to create month wise columns from B:B. Month Should start from the previous month of first date but for same year till the last date in above e.g Jan 11 till Mar 13.
B1= Jan 11 C1=Feb 11 D1=Mar 11 E1=Apr 11 F1=May 11.........................till AB1=Mar 13.
with VBA codes to accomplish above.
I receive a sheet that has forecasts by part number by week in columns. I don't care about the weekly total, I just want to see it by month. I inserted a row using a "=MID" function to remove date and year so all I have left is the month (see attached in row 2). How can I group horizontally by month?
example.xlsx‎
i have a drop down menu listing all the months (as displayed) and once I pick a month, anyone that has an anniversary OR birthday during that month loads into the columns to the right.
I have tried various ways of doing it but for some reason It just wont pull the info right. The previous posts have shown me scripts for Vlookups and a variety of other things. But the end result was not exact.
My table is the equation of time (EoT) (sundialist's jargon). It shows each day of the year if the sun is running fast or slow according to the clock. I need to make (on another page of that file) where I input the month and date. It will then till me the time it is fast or slow
View 5 Replies View RelatedCell C6 contains a drop down from which a month can be selected, range is Oct-12 to Sep-13
Column AB will contain a YTD sum of actuals, Column AC will contain sum of budget.
For example,
Actuals: If Mar-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6
Budget: If Mar-13 is slected in cell C6, I would like call AC10 to sum E6+G6+I+K6+M+O6
2nd scenario
Actuals: If Jun-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6+P6+R6+T6
Budget: If Jun-13 is selected in cell C6, I would like cell AC10 to sum E6+G6+I+K6+M+O6+Q6+S6+U6
I've been reading up about an offset function but can't get to grips with it - should I use that, or an index/match mix?
I have a spreadsheet with all 365 days of the year in row 20 along 365 columns. I have buttons for each month of the year.
The days of the month are signified along row 20 from cell "H2" onwards as 1-Jan, 2-Jan, 3-Jan and so on until 31-Dec.
When I click on the "June" button (or any of the buttons with the month) I would like the columns with all the days from that month to unhide.
I have a couple of Pivot Tables that I create each month summarising my Income and Expenditure. The only problem I have is making the columns appear in the correct order, i.e. where July is month1 through June(the following year) being month 12 to coincide with my financial year. Oddly enough last month (January) was fine, following December.
This month January and February appear in columns 1 & 2.
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx