$$Excel-Macros$$ Create a macro based on a range of cells in excel and send via Lotus Notes with attachment
I have a spreadsheet with columns A through AD and have about 1000 rows on my report. I need to run the report every month and send an email to anyone that has a greater than or equal to 6 in Column W. If column W is greater than or equal to 6, then click a send emails button with an attachment of the worksheet to email recipients in column P via Lotus Notes. (There would be multiple addresses, and each month would be different depending on the criteria.) Also if the criteria does not match the 6, then delete the row, and always save a copy of the worksheet to my C drive. The code for the email with the subject, body text and attachment are working, but I am having trouble with selecting the range from the spreadsheet to send to the recipients and adding my signature to the end. Any help on this would be greatly appreciated! Thanks in advance. Option Explicit Const EMBED_ATTACHMENT As Long = 1454 Const stPath As String = C: Sub Send_Active_Sheet() Dim stFileName As String Dim Worksheets As Variant Set Worksheets = Sheets(FabricsProjectList) Dim myDocument As Range Dim rng As Range Dim vaRecipients As String Dim vaCopyTo As Variant Dim noSession As Object Dim noDatabase As Object Dim noDocument As Object Dim noEmbedObject As Object Dim noAttachment As Object Dim stAttachment As String Dim stSubject As Variant Dim vaMsg As Variant Dim x As Integer Dim c As Range Dim i As Long Dim lRow As Long Dim lCol As Long Dim UserName As String Dim MailDbName As String Dim Maildb As Object Dim MailDoc As Object Dim AttachME As Object Dim Session As Object Dim stSignature As String With Application .ScreenUpdating = False .DisplayAlerts = False 'Copy the active sheet to a new temporarily workbook. With ActiveSheet .Copy stFileName = .Range(A1).Value End With ' Select range of e-mail addresses Worksheets(FabricsProjectList).Range(P6, P1000).Value For i = 7 To Range(A Rows.Count).End(3)(2).Row ' Cells.Range (W6:W1000) If Cells(i, W:W) = 6 Then With Cells(i, P:P) vaRecipients = Worksheets(FabricsProjectList).Range(P i).Value .SendTo = vaRecipients stSubject = Hi, Enterprise Project Champion, vbCrLf This is just a FYI - the last review of your Enterprise Project is older than 6 months...which one ? Please see audit list attached ... vaMsg = Hi, vbCrLf vbCrLf What I am looking for.. the reason for this reminder vbCrLf vbCrLf It is my commitment vbCrLf vbCrLf To run an audit every month vbCrLf To find out which projects are not in the regular review process (6 months) vbCrLf To send out this info to the champions and RD leaders vbCrLf vbCrLf Please be so kind and let me know if there have been RWW's/ reviews in the meantime. If Yes, please send me the documentation. vbCrLf vbCrLf We will enter the document and the new last review date into the database. vbCrLf vbCrLf Thank you stSignature = Maildb.GetProfileDocument(CalendarProfile).GetItemValue(Signature)(0) stAttachment = stPath PROJECT TIME TRACKING\2013 Time Tracking Reports stFileName Fabrics RD Time Tracking Reports_Sep2013_rev2.xls End With 'Save and close the temporarily workbook. With ActiveWorkbook .SaveAs stAttachment .Close End With 'Instantiate the Lotus Notes COM's Objects. Set noSession = CreateObject(Notes.NotesSession) Set noDatabase = noSession.GETDATABASE(, ) 'If Lotus Notes is not open then open the mail-part of it. If noDatabase.IsOpen = False Then noDatabase.OPENMAIL 'Create the e-mail and the attachment. Set noDocument = noDatabase.CREATEDOCUMENT Set noAttachment = noDocument.CreateRichTextItem(stAttachment) Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, , stAttachment) 'Add values to the created e-mail main properties. With noDocument .Form = Memo .SendTo = vaRecipients .CopyTo = vaCopyTo .Subject = stSubject .Body = vaMsg .SaveMessageOnSend = True .PostedDate = Now() .SEND 0, vaRecipients End With 'Delete the temporarily workbook. Kill stAttachment 'Release objects from memory. Set noEmbedObject = Nothing Set noAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing Set Maildb = Nothing Set MailDoc = Nothing Set Session = Nothing MsgBox Congratulations! The e-mail has successfully been created and distributed, vbInformation End If Next i End With End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM
$$Excel-Macros$$ Know cell.address for a clicked hyperlink
Hi When clicking a hyperlink, I want to know the address for the cell that holds the hyperlink (internal to another sheet in the workbook)? When using the follow_hyperlink event I can get the address for followed link, but not the address from which I jumped. Regards¨ Kim -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ How can I translate text in many cells of an Excel sheet from English to Greek with VBA
Hi all, I have a spreadsheet with thousands of rows and columns of text that I'd like to translate into Greek. Can anyone tell me how I might do that using VBA with some free service or API online? Many thanks in advance. Kim -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ When do my subscribers pay plan
Thanks :) Mvh Kim Pedersen Strand Vinduspuss Tlf. +47 934 60 815 2011/9/24 dguillett1 dguille...@gmail.com See Attached *From:* Urghhh p...@strandvinduspuss.no *Sent:* Friday, September 23, 2011 2:33 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ When do my subscribers pay plan Hi Sorry :) Here it is. The sheet from csv is data copied from the csv file. The sheet Output idea is how I want the data to be sorted. The bold numbers is the next delivery month. (I did manage to get thatone by a IF statement) But i need to get the rest of the months to be filled by how many times a year I will delivery my service (window cleaning). Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi, I have a list of numbers and I need to find the highest of the most frequent occurrances. For example: List 1 1 3 3 4 4 Should return the value 4 because its the highest of the most frequent occurrances of any number. List 2 1 3 3 3 4 4 Should return 3 because it's the most frequent occurrance outright. I've tried using the mode command but it returns the first of the most frequent occurances but I want the highest of the most frequent occurrances. Any help would be greatly appreciated. Thanks Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Thanks so much Ashish but the problem is in the temporary column. I really need to do it in one cell only without introducing any new data. Any idea on how I can achieve that? Again, thanks for your help. Kim On Jan 6, 4:07 pm, ashish koul koul.ash...@gmail.com wrote: check the attachment see if it helps On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@gmail.com wrote: Hi, I have a list of numbers and I need to find the highest of the most frequent occurrances. For example: List 1 1 3 3 4 4 Should return the value 4 because its the highest of the most frequent occurrances of any number. List 2 1 3 3 3 4 4 Should return 3 because it's the most frequent occurrance outright. I've tried using the mode command but it returns the first of the most frequent occurances but I want the highest of the most frequent occurrances. Any help would be greatly appreciated. Thanks Kim -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;... -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. sample datat.xls 24KViewDownload- Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
Thanks Paul!!! Well, I wrote the whole code except for the last line ActiveCell.Offset(0, 1).Formula = =sum( Format(FBTFirstrow, H#) : Format(FBTlastrow, H#) ) All I want is the following one you explained. Thanks a lot for that. ActiveCell.Offset(0, 1).Formula = =sum(H FBTFirstrow :H FBTlastrow ) For the other parts, it works exactly the way I want. I just didn't know how to use sum with FBTLastrow and FBTfirstrow. I'm home now.. I will try your suggestion when I get to work on monday. You said I could simply define the range extremely large. In fact, I can get the result with Sumif and selection of the whole column. But I just wanted to do this way. And you are right about the following code: it will look strange.. ActiveCell.Value = Total FBT Transfer to Fund It's because the codes I provide is not the whole but part. There are other codes which come with the code. All I want was the last code.. how to write code for sum function with a variable starting row and a variable ending row. Thanks paul.. I will try on Monday and let you know how it went. On Fri, Jun 25, 2010 at 2:43 AM, Paul Schreiner schreiner_p...@att.netwrote: Is there other data in that column that you're NOT using? If there's not, you can simply define the range extremely large. Or... are you trying to find the last row with ANY data? that is to say: FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row will give you the row number of the last row of the entire spreadsheet that contains data. You said that you don't fully understand your code, so let's step through it: Range(C:C).Find(Description, LookIn:=xlValues).Select this selects column C and searches for the string Description, then selects this cell. FBTFirstrow = ActiveCell.Offset(1, 0).Row this returns the row number of the cell immediately below currently selected cell (that has the word description) FBTlastrow = ActiveCell.End(xlDown).Row this has the effect of hitting the end key and the down arrow. as you observed, it will take you to the last entry before the first blank space. Instead, I would use: FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row ActiveCell.Value = Total FBT Transfer to Fund This is curious. Unless you've left out something, the currently selected cell contains the word Description, and you're changing it to: Total FBT Transfer to Fund ActiveCell.Offset(0, 1).Formula = =sum( Format(FBTFirstrow, H#) : Format(FBTlastrow, H#) ) This offset statement identifies the cell 0 rows down and one column to the right and inserts a formula. The problem is that this is an improper use of the format function. In the formatting expression, H means to display the Hour without leading 0's. since this isn't a TIME, I suspect you were trying to use this format statement as if it were similar to C++. It's not. How it may have worked properly for F and G is beyond me .. what I would use is: ActiveCell.Offset(0, 1).Formula = =sum(H FBTFirstrow :H FBTlastrow ) but this whole thing looks suspicous. Because if you ran it for column F, then the sum would be placed in the column G header. I suspect something more is going on. I think we need to know more about what you're trying to do and what you want it to look like when you're done... Paul -- *From:* Mark Kim mark@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, June 24, 2010 7:15:11 AM *Subject:* Re: $$Excel-Macros$$ Re: Using sum function for Unknown range Can not use like that. All I know is which column i need to use. I do not know how long is the sum range and where to start. That's why I have FBTFirstrow and FBTlastrow . I need to have sum formular in the cell so i can verify the formula On Thu, Jun 24, 2010 at 3:22 PM, saggi realsa...@gmail.com wrote: If you are not using Dynamic Range then use simple Sum formula, Just change range n column number Sub SUM() ActiveSheet.Select ActiveCell.FormulaR1C1 = =sum(R[-1]C:R[-22]C) Range(B23).Select End Sub On Jun 24, 7:58 am, Markkim mark@gmail.com wrote: Hi I have a set of data which I need to sum up between unknown range.. The following is the code I am using Range(C:C).Find(Description, LookIn:=xlValues).Select FBTFirstrow = ActiveCell.Offset(1, 0).Row FBTlastrow = ActiveCell.End(xlDown).Row ActiveCell.Value = Total FBT Transfer to Fund ActiveCell.Offset(0, 1).Formula = =sum( Format(FBTFirstrow, H#) : Format(FBTlastrow, H#) ) The first paragraph - I assigned beginning of the row to FBTFirstrow and end of the row to FBTlastrow I can not use ActiveCell.End(xlDown) for sum range because there are blank cells in the columns which contains actual data. that's why I am getting FBTlatrow. Now Let's say FBTFirstrow is 50 and FBTlastrow is 80 then, I like to insert a formula sum(H50:H80) And the code above I
Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
Can not use like that. All I know is which column i need to use. I do not know how long is the sum range and where to start. That's why I have FBTFirstrow and FBTlastrow . I need to have sum formular in the cell so i can verify the formula On Thu, Jun 24, 2010 at 3:22 PM, saggi realsa...@gmail.com wrote: If you are not using Dynamic Range then use simple Sum formula, Just change range n column number Sub SUM() ActiveSheet.Select ActiveCell.FormulaR1C1 = =sum(R[-1]C:R[-22]C) Range(B23).Select End Sub On Jun 24, 7:58 am, Markkim mark@gmail.com wrote: Hi I have a set of data which I need to sum up between unknown range.. The following is the code I am using Range(C:C).Find(Description, LookIn:=xlValues).Select FBTFirstrow = ActiveCell.Offset(1, 0).Row FBTlastrow = ActiveCell.End(xlDown).Row ActiveCell.Value = Total FBT Transfer to Fund ActiveCell.Offset(0, 1).Formula = =sum( Format(FBTFirstrow, H#) : Format(FBTlastrow, H#) ) The first paragraph - I assigned beginning of the row to FBTFirstrow and end of the row to FBTlastrow I can not use ActiveCell.End(xlDown) for sum range because there are blank cells in the columns which contains actual data. that's why I am getting FBTlatrow. Now Let's say FBTFirstrow is 50 and FBTlastrow is 80 then, I like to insert a formula sum(H50:H80) And the code above I got it from somewhere in the internet, which I don't have full understanding.. what's very odd is the code above works perfect for sum(F50:F80) or sum(G50:G80) - I simply change H# to F# or G#. But it doesn't work with H#. I don't understand. Why is it not working. one other question I'd like to ask is is there any better way of doing this? I have first row and last row. with this information, I'd like to sum up different columns. Cheers -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Navigation Macro..
Hi Spud I mean for example, Cell A1... I have a formula = Sheet2!A3. I have a worksheet for for example Feb GST incurred ... (I have lots which are to caculate for exmpale.. FBT, Deposits.. different types of costs.. ) And i also have a workbook (separate file) which summarize all of these worksheets.. I can not have a absolute value for any of numbers presented in this summary.. All have to be linked.. simply.. All the value cells have a formula such as =[GST.xls]Feb2010'A3' .. something like this.. hm... I think unticking the option edit directly in cell .. will do the job.. but I really hope to get a macro.. well, I'm using 2007.. where is the option? can't seem to be able to locate it.. ^ ^;; I went throught all the options. On Tue, Mar 23, 2010 at 10:49 AM, Spud dean.t...@gmail.com wrote: Can you post an example showing how the format of the cell values (you mention that the cell value can be a workbook and a cell: does it show the path correctly for the workbook? Unless you know the correct path for the workbook, or they're all in the same path/folder, then it would be impossible to open the correct workbook). If by cell value you actually mean formula (ie. in cell A1 you have the formula =Sheet 2!A3) then you could just go into toolsoptions and untick edit directly in cell, and then when you double click in cell A1 it will take you to Sheet 2!A3. This will also open up the workbook if the formula is ='[Book1.xls]Sheet 2'!$A$3. Unticking edit directly in cell will mean you can't edit formula's in the cell, you have to do it in the formula bar. Cheers, Spud On Mar 22, 10:34 pm, Mark Kim mark@gmail.com wrote: The problem is then I need a create hyperlink manually.. Then I need to create hundreds hyperlink.. Also, everytime I click the cell, it takes me to the reference cell, which I don't want to. I want to go to the reference cell when I want to. That's why I don't think Hyperlink is a solution. Due to system problems, I am doing Reconcilation manually with thousands data. Creating a hyerlink for every cell I need to link is too much time consuming. I don't think it will be a complicated macro.. Get the value from a cell... if the value is workbook.;.. open the workbook and go to the reference cell if the value is a cell, go to the cell well, I think this logic will work.. I just don't know how to write this.. Anyone please? ^ ^ On Mon, Mar 22, 2010 at 5:57 PM, Yahya yahya...@gmail.com wrote: Dear Mark I think you can do it by just using a Hyperlink. No need for macros. Please see the attached file Yahya On Mon, Mar 22, 2010 at 2:37 AM, Markkim mark@gmail.com wrote: Hi I am wondering if anyone can help me creating a macro for the following.. Get a reference from the selected cell and take me there . For example, A1 contains C4. The macro will take me to C4. This is a very simple one. A1 contains a reference Sheet2!C4. I’d like the macro to take me sheet2 C4. And if A1 contains a location of the other file. Let’s say… A1 contains Sheet ‘sales’ C4 in the file named ‘Sales’. Then I want the excel to open the file and take me to C4 in a sheet called ‘sales’. I want a button to get back to where I was. Can you create a macro like this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+ unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice
Re: $$Excel-Macros$$ Navigation Macro..
The problem is then I need a create hyperlink manually.. Then I need to create hundreds hyperlink.. Also, everytime I click the cell, it takes me to the reference cell, which I don't want to. I want to go to the reference cell when I want to. That's why I don't think Hyperlink is a solution. Due to system problems, I am doing Reconcilation manually with thousands data. Creating a hyerlink for every cell I need to link is too much time consuming. I don't think it will be a complicated macro.. Get the value from a cell... if the value is workbook.;.. open the workbook and go to the reference cell if the value is a cell, go to the cell well, I think this logic will work.. I just don't know how to write this.. Anyone please? ^ ^ On Mon, Mar 22, 2010 at 5:57 PM, Yahya yahya...@gmail.com wrote: Dear Mark I think you can do it by just using a Hyperlink. No need for macros. Please see the attached file Yahya On Mon, Mar 22, 2010 at 2:37 AM, Markkim mark@gmail.com wrote: Hi I am wondering if anyone can help me creating a macro for the following.. Get a reference from the selected cell and take me there . For example, A1 contains C4. The macro will take me to C4. This is a very simple one. A1 contains a reference Sheet2!C4. I’d like the macro to take me sheet2 C4. And if A1 contains a location of the other file. Let’s say… A1 contains Sheet ‘sales’ C4 in the file named ‘Sales’. Then I want the excel to open the file and take me to C4 in a sheet called ‘sales’. I want a button to get back to where I was. Can you create a macro like this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+ unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: $$Excel-Macros$$ How to create a link to other sheets...
Hi Praveen Thanks Million!!! This is exactly what I was looking for.. Thanks a lot !!! On Sun, Jan 17, 2010 at 1:35 AM, kvc praveen kumar pkkara...@gmail.comwrote: Hi, Please find the attached file and let me know if this is what you are looking for. I used worksheet events to impement the same. Regards, Praveen On Sat, Jan 16, 2010 at 6:39 PM, Markkim mark@gmail.com wrote: Hi all Wow.. I didn't know there is a place like this where you can get some help from experts for Excel macro.. It's brilliant.. !! I have been thinking for some time that It would be very useful if I had this feature.. I hope it's possible. I have an excel file which has three sheets. First sheet contain a list of transaction and each of transaction has a unique ID and one of these transaction are related to the next sheet. Second sheet also contains a list of transactions and each of transaction has a unique ID as well. In the first sheet, I have a column that has a unique ID of transaction from the second sheet. For example Sheet 1 ID Company Product (ID from sheet 2) 1 A 3 2 C 1 3 D 2 Sheet 2 ID ProductCompany (ID from Sheet1) 1 X2 2 Y 3 3Z 1 As you can see above.. Sheet 1 and sheet 2 are related. What I need is .. when I click the cell which contains ID from sheet 2 in Sheet 1, I want excel to take me there.. so I don't need to look up in the sheet 2.. it will move there. or clicking the next cell..so.. when I click 3 in the product column from Sheet1, I want to move to ID 3 in the Sheet2 and vice versa. I am not good at explaining.. I hope I make sense. I will be really appreciated if you could help me.. Thanks for reading.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
