2020. 3. 2. 18:01ㆍ카테고리 없음
Hello,I have a number of different files that I often need to run a macro on. Hi everyone.this is my first post here.and not my last I believe!My question I believe is easily solvable for you cracks of excel.I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.I created an example on a worksheet attached just for you to understand.I apreciate all the help I can get.Thank you so much.RG. I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).What modifications do I make to this to get this to work per above requirements?Sub CopyMeDim SaveMeAs As StringSaveMeAs = Sheets('Sheet1').Range('B2').TextSheets('Sheet3').CopyActiveWorkbook.SaveAs Filename:='C:My Documents' & SaveMeAsEnd Sub. I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?Example User's path: 'C:Documents and SettingsjfarcDesktop'Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.Also, is there a way to pull out of Excel what is the current User's 'Options General Default File Location' entry?
Which may differ from the above directory.I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:Dim wbThis As WorkbookSet wbThis = ThisWorkbookChDir wbThis.Path. I found this solution for 'drop down list with hyperlink' but it did not work.Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:=HYPERLINK(A1, 'Goto Link')The solution directly above provides exactly what I am looking forin the field where I write the formula, but it fails to hyperlink.I have created a drop down list and linked each one of them to aspecific worksheet. When I select them individually they link toappropriate worksheet.
But when I select them in the drop downlist I receive the following error when I select the Hyperlink incell B1 as directed above.' Cannot open the specified file'Any thoughts?Bob. Hi,This is a very basic question, but i have been struggling for hours, so I must ask.I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? And will this code work?Code:Sub Button1ClickSet UserForm1.combobox1.List = Sheets('Sheet1').Range('MyRange')UserForm1.ShowEnd SubI am very new to vba, so please excuse my ignorance,any help would be greatly appreciatedregardsjim. Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes.
All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window 1 (filename), the code that I get in VBA is Windows('filename').Activate. This doesn't translate to when I have two different workbooks with different filenames open.Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.- Michael. Hi Excel experts,I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet.
Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.Basically, I am attempting to construct a 'work in process' file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to 'Complete', it would be helpful to be able to run a macro/click a button that would perform the transfer.Can anyone assist?Thanks!
Hello,Here is my issue:I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below:1) List of SKU's in cells:343539552) text that needs to be added IN FRONT of every number:DFSo the result would be:DF34DF35DF39etc.How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34.I am using Office 2007 for mac.Thank you for the help,Chris.
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e.
AS=2, AM=7, etc.)?In previous years I made a column for each size, and simply placed a '1' in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.Thanks for taking the time to read this post. Any help would be appreciated!
Thanks, doug. How do I get the colors to change automatically when I use a drop down list.Per say here is what I am trying to doIf a certain 'word' is used from the drop down list I want the background ofthat cell to change its color to 'green.' And if later I change the 'word' toanother from the drop down list, it will change it's color to a specifiedcolor.The drop down list that I use was created from cells that have the colorsalready in the 'word', but I do not know how to make the list show the colorsso it puts the word & color automatically in the drop down list to the cellwith the drop down (if that makes any sense).If there is a way, please spell it out simple enough for me to understand,as I don't know fully the capabilities of formulas or vba. This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet.
Macro To Download File From Ftp
Since that's a horrible explanation, let me try explaining it this way:Sheet 1Column A has a long list of code type 1sColumn E has a long list of code type 2sSheet 2Cell C2 has code 1Cell E2 needs code 2I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1'!E:E,0),5)But all I get is no result.
What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine. Hi,I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly.
Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.Sorry to ask such a bizarre question, but I appreciate your help.Thanks.
I am trying to do something so simple (or it seems to me a.NET programmer) in VBA. Not a VBA guy so please give me the answer like I am 10 years old lolAll I need to do is download a file in Excel in a macro to a local directory.I have read countless Google searches and even on here many different posts but cannot get any of them to work.This is for many users all over the globe so we cannot install anything or use a 3rd party tool. We have to achieve this inside the macro. Shell command, anything. Just download a file.Please help!:)Thanks experts. First, I have to assume that you have an FTP program on your computer, which most do.You can check by going to a command prompt (Start / Run / Cmd) and type ftp in the black window (are you familiar with the DOS window?)If you get a prompt of 'ftp' then your good.For info about ftp, you can then type 'ftp /?' An FTP server doesn't do you any good if your users don't have FTP client software.Sounds like your boss gave you a screwdriver and a box of nails to work with on this project.Could you use your.NET experience to write a.NET COM library so the FTP download function is exposed to the VBA via COM?You would need to write it to target.NET 2.0 or however low a.NET version supports the FTPWebRequest library.
The lower version the better or else you just trade one software distrubution problem with another as you have to explain to people how to install the framework.