Is it possible to create a hyperlink for a cell to display a textbox that enables the user to input a specific cell value that will autofilter a worksheet to pull up only the rows that is related to that value?
View 5 RepliesIs it possible that i can execute a Unix C-shell script named "get_summary" and after executing it, it will generate a file named xxx.asc ?
I have added on a statement :
A.writeline "get_summary" 'To execute a script
but i can;t seem to get the csh script to run. Pls help. Thanks again in advance
Dim userinput As Variant
userinput = "xxx.asc"
Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("c:script.txt", True)
A.writeline "open 10.10.10.10"
A.writeline "xxx" 'username
A.writeline "xxxxxx" 'password
A.writeline "cd /export/home/tae/scripts" 'directory on FTP site
A.writeline "get_summary" 'To execute a script
A.writeline "ascii"
A.writeline "get " & userinput 'file to be uploaded
A.writeline "quit"
A.Close
I have created a VB User form in Excel. I have a value in Cell A1. On clicking the cell, the user form has to be invoked. I do not find a way to do this using HYPERLINK function. If I use a label in that cell, I am able to implement it. But I may want to sort the excel cells and in that case the label idea will not work out!!!!
View 3 Replies View RelatedI have a code that looks fetches all names of the worksheets in a workbook and lists in a master sheet.
[Code
Sub Button1_Click()
Dim ws As Worksheet
Dim x As Integer
Dim lr As Long
x = 2
lr = Worksheets("Totaliser").Range("A1").End(xlDown).Row
Worksheets("Totaliser").Range("A2:A" & lr).Clear
[code].....
This works fine collecting all of the named sheets but is there a way of me invoking a 2nd procedure to count the number of rows within each worksheet that contain line items which are updated at the beginning of each month. Placing them into the next column (B) to the right of the sheet names.
All worksheets contain a header Row (1) so the count function would if at all possible count from the 2nd Row to the end.
I have tried different lines of code but just get errors.
The code above places the first sheet into cell A2 and then down respectively
I am new to excel macro. Want to invoke a page and click on submit button. Getting "Object doesnt support this property or method" error on the line "Subm.Click"
My code is,
Sub Macro1()
Dim IE As Object
Dim URL As String
Dim Subm As Object
URL = Range("B3")
[Code] .........
I have a spreadsheet of device names and IP addresses. I want to be able to click on an IP address and have it automatically invoke A SSH program ( putty.exe ) to automatically login to the device.
View 1 Replies View RelatedI want to Hide a range of rows (16:18 in this example) if "no" is in a range
of cells and Unhide the same range of rows if "yes" is in the same range of
cells. The "Hide", "Unhide" macros (below) work if I select one from the Run
Macro list, of course. But, how can I call for it to run from an "IF"
function. I know this statement is wrong, but it may help you know what I am
looking for: =IF (A1:A15="no",Run Hide,IF (A1:A15="yes",Run Unhide),"")
Sub Hide()
Rows("16:18").Select
Selection.EntireRow.Hidden = True
End Sub
___________________
Sub Unhide()
Rows("16:18").Select
Selection.EntireRow.Hidden = False
End Sub
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedI have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies View Relatedauto filter options in excel. For some reason it is only sorting up to row 140.
View 14 Replies View RelatedSay you have a long list of data, and you go to Data menu --> Filter --> AutoFilter
And then you want to use the Custom AutoFilter. Here's a screenshot:
[url]
Is there any way to do MORE than two autofilters?
I have a list of item numbers in a column which is Autofiltered. I can go to the autofilter drop down and then to custom and select two item numbers to allow through the filter, however I have about ten item numbers that I want to allow through the filter.
View 10 Replies View RelatedIs it possible to have multiple instances of autofilter on a single worksheet? The two autofilters should not be related to each other and are on different sets of data (in different rows as well as columns but in the same worksheet).
View 2 Replies View RelatedI have a situation - where I have a table and a "eSubtotal" cell that basically shows the subtotal value when Autofilter is ON and a SUMIFS calculated value when Autofilter is OFF. I have written this in the Selection change event of the sheet.
For this purpose, I have perform a regular check of AutoFilterMode = true or false and based on this result, I change the formula in the cell, eSubtotal.
Now the challenge is, I don't want to apply SUBTOTAL formula when AutoFilter is ON but there is no filter in any of the columns. I want to keep the SUMIFS just like that in this case.
So far, no good luck..
The following the code from the macro recorder. Is there a better way to execute this task in proper VBA code?
How do you add an [If...Then] critieria when 0 or 0.00 are not found, then continue to search for the next criteria, Non-Blanks?
I have 2 more questions within this .....
When I run the following code, it just returns records that matches "Commonwealth" and not a single record with "State". What am I doing wrong?
i am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.
Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?
I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.
I have an autofiltered sheet, on which I need to find the last visible row (all columns have the same # of rows in use). I am trying to use:
View 4 Replies View RelatedCode runs rather slow. Is there a better way to achieve the same results?
View 5 Replies View RelatedI have a workbook of approx. 60,000 rows, with about 20 columns including a source identity column, such as 'Leeds' , 'Barnet' etc..
What i need is a solution that will auto filter all rows that have a value of 'Leeds' in the source column into a new workbook called 'leeds.xls' for eg. and so on (for each unique source value) and loop until the whole data set has been filtered.
Saves manually filtering, copying and pasting....over and over.....
Im guessing the VBA needs to build / look at an array etc...
This macro was working just fine, now it generates an error on the line in red.
View 2 Replies View RelatedI have an spead sheet with 9 sheets 1 master and 8 sub sheets. The master sheet runs for col A to AV and the it can have x amount of rows. At the moment i use the following but this takes an age.
View 9 Replies View RelatedThe attached file has two sheets, “order” sheet and “stock” sheet. Can some help me with the followings:
1-In either sheet, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, the VBA performs an Auofilter of the cell I have selected.
2-In sheet “Orders”, I would like a VBA where if I put the mouse on a cell and then click the VBA button it takes me to sheet “stock” and performs a find function of the cell I choose in sheet “orders”. For examples, if I put the mouse on cell E21 in sheet “orders” and press the VBA button, it takes me to cell H297 in sheet “stock”.
3-In sheet “stock”, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, it takes me to sheet “orders” and performs an Auofilter of the cell I choose in sheet “stock”
I have a code that will look in column C for all rows with data, turn on autofilter and copy the data into column A.
Then do the same in column D, paste into column A.
And finally the same in column E, paste into column A.
But I think the autofilter turning on and off might be a problem.
Code:
Sheets("PP").RAnge("A2:A50").ClearContents
Dim LastRow1 As Long
RAnge("C1").Select
[Code].....
How to achieve by formula what was selected in autofilter without use vba.
View 4 Replies View RelatedFrom my data set I would like to delete all rows that show "Yes" in Column I.
I copied this piece of code a few days ago from this site and have attempted to modify it eg by altering Columns to "I" and Autofilter Field to 9 and Criteria1 to = "Yes", but without success. Can you please help?
With Columns("A")
.AutoFilter Field:=1, Criteria1:=""
.Range("A2:A" & Rows.Count).EntireRow.Delete
.AutoFilter
End With