I recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.
I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies View RelatedI am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
View 2 Replies View RelatedVBA 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 RelatedHow do I reset ALL the sorting in a file?
The first worksheet is all formula generated and has filters at the top, which I've variously sorted alphabetical. The second worksheet is raw data the user isn't intended to read.
Now I want to alter one of the formulas. The problem is, if I change it in row 2 and fill down I get all kinds of different information. This is because while the present formulas read
='other worksheet'!A265
='other worksheet'!A934
- and so on at random, when I resort them I just get A2, A3, and so on.
I have an excel file downloaded which has data as per serial number.The serial numbers are like 1,1.1,1.2,1.1.1,1.1.2,1.2.1 etc
I want to sort them in proper order. For eg. 1,1.1,1.1.1,1.1.2,1.2,1.2.1,1.3,1.4,1.4.1,1.4.2,1.5 etc
Normal sorting in excel does not work as the order is not proper.Can I have some kind of VBA script or way by which I can sort these numbers.
im looking for a way to sort some data exported from an old stock recording software. the system outputs the data as a .csv file but each item bought is grouped into a particular heading, a line is only generated if money is spent under that particular heading. i cannot make the software generate lines if no money is spent so the number of lines generated each period changes.
period 1
3202 fuel £3000
3203 PPE £250
3204 major units £5000
3205 planned maint £212
3207 unplanned maint £457
period 2
3202 fuel £2500
3203 PPE £120
3204 major units £1000
3208 storage £212
3210 windows £457
if the data was regular i could simply add the totals each month and divide to get the average.
what i am looking for is a way to auto sort the data onto lines by possibly injecting the missing headings with zero as the total spent.
I have a group of CSV files that I will need to periodically import and sort in Excel.
The CSV files will contain one column I wish to use (It is the first one and the rest can be ignored or imported) and sort the desired data based upon the existance of a particular bit of text. Each entry will either be just a username or a username with a '-label' appended to it so for instance each of the CSV files might contain:
bob
fred
mark-label
ted
angie-label
Basically I want to seperate the names with the -label from the ones that do not have the -label. Doing searches, I was able to get part way using the code from this thread:
[url]
But am running into problems trying to modify it to suit my needs. Is there an easy way to accomplish this? I'm not very good with VB so that hasn't helped me. It would be nice to move the sourted output to seperate columns if that is possible.
I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.
A
B
1
Account
Company Information
2
Account
Company
[Code] ........
I have a list of about 300 addresses that I'd like to be able to put in a sortable state. In column A1-A4 is the following:
Name
Street Address
City, State Zip
Email Address
Then there is a blank row and then the next address. What I would like to do is figure out a way to take the info in columns A1-A4 and move them horizontally to A1, B1, C1, D1, respectively. Sure, I could cut and paste, but it would take me forever. I tried a macro,
How can i change this code to point to a file which is on my desktop which is in a folder called "cell n1" and the file is called "Cell n1"
View 2 Replies View RelatedAnyone up for getting me started on code to open a file in a "dynamic" directory, specifically:
Where
Main directory is G:REPORTS
The file name is "Pivot_xx-xx-xx.xls"
The directory where the file is located is G:REPORTSxx-xx-xx
xx-xx-xx is yesterday's date
I am trying to set up a file name and path in a cell, and then use this from a number of other cells but with a cell location added to it. So for example:
cell A1 contains a file name "c:mydirectoryexcelfile.xls"
cell A2 needs to contain the contents of the cell at location K12 (for example) from the file referred to in A1
cell A3 needs to contain the contents of the cell at location K13 (for example) from the file referred to in A1
This is to save having to put the filename and cell reference in all of the of cells. I would then do the same thing with another file in column B of this file and the same with column C etc.
About 2 weeks ago Reafidy posted a reply to the following thread. Save Individual Sheet Based On A1. As I do not want to hi-jack anyone elses thread so I am starting a new one. The code posted does basicly what I need however I was wondering if it is possible to save only "ONE" specific sheet which could be specified in the code, to a specific folder specified in the code and under a name defined by a cells contents. eg.
Save a single sheet named "MatData" from a workbook, name the saved sheet from a "named range on sheet1" & "Cutlist" & ".xls"
to a Folder on the "D" drive called "Saves"
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
I have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.
the steps to achieve this?
How could i get the below code to work with a variable as the file name?
Or could I use something like "thisfile" to determine where to import to.
[Code] .....
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
This code works:
I am trying to open a external excel document using this code.
View 9 Replies View RelatedI am using the following Macro to load a selected sheet:
Application.Dialogs(xlDialogOpen).Show
Windows("Prototype Worksheet.xls").Activate
Windows("Test Data.xls").Activate
Range("A1:U102").Select
Selection.Copy
Windows("Prototype Worksheet.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Windows("Test Data.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
This works fine as long as I load the file named Test Data, lolz, but on the line"Application.Dialogs(xlDialogOpen).Show", I actually select the file to be loaded. how can and what should I change on the line "Windows("Test Data.xls").Activate" to achieve this
and also
at the very end of the macro I want to close the loaded file without the save box coming up.
I am in a workbook created by the the macro (the variable name is strtempfile)
I then open a new workbook called Missing stocks.xls (this name never changes)
This workbook has vlookup links to the strtempfile.
I cannot work out a way to change these links.
I have tried the following =
Dim R, c, strtempfile
R = 1
c = 1
strtempfile = D_temp_File_5
Do While cells(r, c) <> ""
cells(r, c +3+ = "=Vlookup([rc-3],[strtempfile.xls]sheet1!r1c1:r3000c17,17,false)
I get a false answer, even though the number being looked for does exist.
I have also played with the idea of simply changing the links and having the do while loop simply copying the cells with the updated formulas into any new rows that need them, but I am not sure how do do this either?
I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:
File#, Date, Status, Customer, Etc.
This is accomplished by copying a row of data in the active workbook and pasting as a link on the master. When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again. The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.
What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.
Thus, where the pasted link formula reads:
='C:Active Files[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:Active Files["&$A25&".xls]Home'!$O$1"
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.
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 Replies View RelatedIf I have a file open called test file, How I can find the file path of the file that is open and how can I store that's as a variable?
What I am doing is trying to open a file that excel closes in a save as process!
I wish to import data from another workbook which will always be one directory level up from the target workbook. The problem is that the source workbook's name will change but it can always have the first four characters the same, ie, List_Dublin, List_Kildare, etc.
View 4 Replies View RelatedTrying to copy records from one spreadsheet to multiple spreadsheets located in different directorates. Although I can create multiple Macros to address the need, I want to use ONE MACRO that utilizes two variables to represent:
1) The targeted file directory
2) The targeted file name
Items 1) and 2) are stated in two cells within the main spreadsheet and as you can already imagine, these values change IAW the name of the target file.
This is what I am trying to do:
File cell A1 = "C:ORLOAcquisition"
File cell A2 = "target.xlsx"
Basically replacing the code below
ChDir "C:ORLOAcquisition"
Workbooks.Open Filename:= "C:ORLOAcquisition arget.xlsx" , UpdateLinks:=3
with, ChDir ""A1"" Workbooks.Open Filename:= ""A2"" , UpdateLinks:=3
I was able to do it with target-spreadsheets located under the same workbook but my code does not work when trying to activate workbooks located in other directorates in the network.
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
