Modify Macro To Use File That Changes Name-monthly
Apr 20, 2007
I have this macro that I am using but want to automate the file it uses. This file has the last 5 text/digits change each month, e.g.
Internet_GB_Report_Feb07
Internet_GB_Report_Mar07
Clearly the next file would be Apr07. I would like the macro to identify this automatically and use the correct file without having to go into the macro every month and change the last 5 text/digits. If you have any ideas I would be very grateful to hear from you. The Macro is below:
[/code]strPath = "c:Internet"
strFilename = "INTERNET_GB_FEB07"
strThisWkb = ActiveWorkbook.Name
intReports = Workbooks(strThisWkb).Sheets("Lookups").Cells(1, 2)
Code:
View 9 Replies
ADVERTISEMENT
Nov 14, 2013
I have an excel 2007 script that downloads daily stock data and prices. I do analysis and graph the data.
I would like to keep the download the same, but modify my analysis so as to obtain weekly and/or monthly data.
I am sure this is commonly done, and is not rocket science, but so far I am baffled as to the procedure / algorithm to do this.
View 1 Replies
View Related
Jun 9, 2008
I need to modify/edit the properties of a CSV File. (Ex: Title, Subject, Author, Category, Keywords, Comments) through VBA
View 5 Replies
View Related
Dec 13, 2006
I need to touch(modify) my file stamp for test, is it possible using vba?
View 3 Replies
View Related
Oct 24, 2005
I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the
first two rows. (I need to skip the first row, which is the headers.)
Kinda clumsy to have such a wide column, but the application this file
is to be fed after I change the contents requires all the data
separated by double-quotes to be in the one column....
View 11 Replies
View Related
Apr 29, 2013
I'm having a macro for search and all within the same file as folow:
Sheet"Search" has this vba
Code:
Option Explicit
Private Sub Worksheet_Activate()
[c3] = "Type your search here."
[c3].Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[Code] ........
Sheet"Data" is my data (which is refer to above vba as Const SEARCH_SHEET = "data")
My needs here, i need to move Sheet"Data" to another file then how can I linke above vba to Sheet"Data" that moved to another file?
Infact, that file will be as addins file (.xlam) .
View 2 Replies
View Related
Apr 8, 2008
I want to amend the ' Date Modified' for a spreadsheet. I have just made changes to it - so the 'Date Modified' shows today's date and time. Is there a way of changing the 'Date Modified' so it shows as yesterday's date?
View 3 Replies
View Related
Nov 11, 2011
The file name will change on a monthly basis. Is there a code that will open the file without making modifications to the macro itself? For example, October Budget.xls, next month the file will be November Budget.xls, etc.
View 4 Replies
View Related
Feb 9, 2009
I need to run two macro when I modify a value in a cell
Here is the code for the macros:
View 14 Replies
View Related
Jun 21, 2013
Due to the lack of time and not being able to make it work otherwise this is what I have right now:
If Range("g59") = 1 Then
Sheet1.Activate
Application.Run "Edit_view"
Sheet3.Activate
Application.Run "Edit_view"
[Code] ...
There was a formula to enter sheets names in a cell range (ie: A1 would have value SHEET1, A2 SHEET2, etc) and use a FOR loop to cycle through the named sheets.
View 1 Replies
View Related
Oct 4, 2012
I have this macro that I use for taking information based column B grouping it together and putting in a separate worksheet.
What I would like to do is in the master sheet (sheet1) to create a hyperlink for those listed in column B so I can click on it and it takes me right to that worksheet.
Sub exporttoWS()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3
[code].......
View 3 Replies
View Related
Aug 20, 2008
i have this macro made by another member here that looks for "T" in column 10 and deletes entire row if in that row has a "T". I need it modified to also delete if it contains "FT" and also erase if cells is blank.
Sub Macro8()
Sheets("FORMERS").Select
'deletes entire rows based on a text on one row (this case errases all that have a "T" in column 10 (column J)
Dim DeleteValue1 As String
Dim rng1 As Range
Dim calcmode1 As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With...........................................
View 9 Replies
View Related
Dec 17, 2009
I use the merge button a lot for a group of cells in a column. However then I have to edit the formula in the merged cells.
I'm hoping there is a macro that can be made to bring these together.
Here's an example of what I do:
select range C2:C4. Cell C2 contains the formula =sum(B2)
merge these cells.
edit formula in new merged cell to be =sum(B2:B4)
Can I group this process into one macro?
View 9 Replies
View Related
Mar 6, 2013
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
View 1 Replies
View Related
Dec 9, 2008
Is it possible to modify this macro below to concatenate what is in cell B4 (myfilename), with B5 ( =now() )and retain the correct time date format that I have specified (I formatted the cell so that there were no / or : in the file name i.e.ddmmyy_hhmmss)?
When I try this the file name always reverts to the microsoft date "serial number:
I would like myfilename 09122008_1851
but instead get myfilename 39792
(or would it be better to include the date and time in the macro rather than in the cell?)
View 6 Replies
View Related
May 8, 2007
The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications
Option Explicit
Function SortConcat(Rng As Range) As Variant
'Rng —The range of data to be sorted and concatenated.
Dim MySum As String, arr1() As String
Dim j As Integer, i As Integer
Dim cl As Range
Dim concat As Variant
On Error GoTo FuncFail:
'initialize output
SortConcat = 0#
'avoid user issues
If Rng.Count = 0 Then Exit Function
'get range into variant variable holding array
ReDim arr1(1 To Rng.Count)
'fill array
i = 1
1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells
2) These cells contain dates and numbes---all i want to sort and/or display is the numbers
Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis)
3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary
4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature
View 9 Replies
View Related
May 24, 2008
I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?
View 4 Replies
View Related
Aug 3, 2009
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
View 9 Replies
View Related
Feb 15, 2014
I have a workbook that has 2 worksheets. Sheet1 has a data table that is filled from an SQL database. I would like the first column of the data table to have each cell be a hyperlink. The destination of the hyperlink is Sheet2, cell A2. I would like to modify the hyperlink, so that when the user clicks on the hyperlink, the text-display of the hyperlink is copied into sheet2 cell A2. Initially, I thought this was simple. I added the event code shown below:
[Code] ........
This worked, however there was a problem. I manually added the hyperlink to all cells in column1 of sheet1. However, this made the TextToDisplay property the value of the first cell for all hyperlinks. So, solution to add hyperlinks to all cells in the first column, with TextToDisplay property set for each individual cell. What is the best way to do this with the fact that the number of rows in column1 will vary as the size of the SQL query changes?
View 1 Replies
View Related
Feb 21, 2014
I have this macro and at the present time, can only compare two worksheets. One of my worksheets contains column headers with data, the other worksheet just contains column headers. The macro will match the data against the column header and paste below. I'm doing it this way because I have several tables I export from a MySQL database, but some of them have more fields than others so I was trying to get all of the data to line up in under the correct column header from each table. I need editing the macro to allow for
1) More than one worksheet to be analyzed at a time
2) If the first request is not possible, I would like for the macro to paste results at the next empty cell. As of now, it will only copy one at a time (which requires me to house the data in another worksheet everytime I run the macro).
Below is the code:
Dim ws1 As Worksheet, ws2 As Worksheet
Dim HeaderCells As Range, Hdr As Range, hdrFIND As Range
Application.ScreenUpdating = False 'speed things up
Set ws1 = ThisWorkbook.Worksheets("HEADERS") 'Define name of worksheets
Set ws2 = ThisWorkbook.Worksheets("B")
[Code] .......
View 1 Replies
View Related
Dec 14, 2009
I am using the macro below to import every spreadsheet into a folder. I would like to modify it to check the sheets it is importing for sheet protection. If the sheet protection is turned on, I would like it to turn it off. The password for the sheet protection will be "PIR".
View 3 Replies
View Related
May 6, 2008
I have the following situation. I work off-site most of the time in another office. I would like to have a macro on my main office PC to run on a monthly basis at a set time.
For instance on the 15th of every month at 9:00 am the macro would go into a shared drive and pull an updated copy of a report, redo the report and send it out to various offices. I have VB code that performs all the tasks except for setting a timer to tell it too run on the 15th of every month at 9:00 am.
View 9 Replies
View Related
Jul 14, 2008
I want to do is set up a macro to autofill into a new column every month. My data is linked to another excel document so that is where it is pulling the data from. Currently I have data in columns monthly from Dec 2001 until June 2008. I just want a macro that will automatically add in the next month, so in this case July then August the following month ect.
View 10 Replies
View Related
Mar 7, 2013
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
View 5 Replies
View Related
Mar 7, 2014
i have a workbook that is doing a copy and paste between 2 workbooks.
the trouble i am having is every month workbook a changes to be the name of the current month and year workbook b never changes. so when a change in month happens the macro stops working due to new workbook name.
how could i have the macro reed the name of the work book and input that name into the necessary locations withing the macro.
View 2 Replies
View Related
Sep 29, 2013
I am trying to create a macro to insert monthly P&L figures into a spreadsheet.
I am needing the macro to insert a column to the left of a column titled 'movement' each month and copy&paste my monthly figures in.
I have recorded the below macro but my inserted column is always moving into column E and pushing the prior month to the right, where I need it to always be situated to the immediate left of the movement column, e.g. if the macro is inserting September P&L data I need it to be situated between August data (currently in column E and movement data currently in row F and so on.
Column C D E FItem
July
August
Movement
Fees
26,500
28,000
2,000
Assets Ongoing
150,000
160,000
10,000
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("P&L Current Month").Select
Range("D11:D127").Select
[Code] ...
View 1 Replies
View Related
Feb 10, 2009
I am trying to get a function (sum) of several cells to be automated in a monthly spreadsheet. I can get the function to work, but how do I get it to Autofil? The syntex seems to call for a range, which will be different every month. I can't figure out how to loop it, so I thought autofill would work.
View 3 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
Dec 18, 2012
I 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.
View 1 Replies
View Related
Feb 23, 2014
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
View 1 Replies
View Related
