$$Excel-Macros$$ task notification in excel
Hi guys, I need help with designing a task notification system in Excel. *Some background:* I do project management in Excel and I've automated the entire process with VBA to great extent. The only remaining problem is the task notification system. When I assign a project task to a specific user email notification is required. Currently I'm using an external system which means additional manual work. (and Outlook doesn't work because it doesn't support chain-like ordered mails) *Desired result:* In my project file I'd like to trigger the task assignment to one (or all) project memeber(s) which would in turn copy task details to their specific spreadsheets for workload and task monitoring. In their spreadsheets there needs to be a status column (in my project file as well, of course) and when they change the status of the task it also changes in other respective places and if possible some sort of notification appears (in form of msg box or something). *Challenges:* 1. The two files I've mentioned are behind restricted access and can only be viewed by a user with proper access rights. Users can't access the project file.I guess to bypass this I need to setup a third file to be used for communication. 2. In most cases there are several users doing tasks in a specific workflow. So the notification system hast to handle multiple assignments in desired order. * My questions to you:* 1. Is this feasible in Excel or has anyone tried to do something like this? 2. Are my assumtions correct or should I try something different? In Excel that is. I know some might say I need to transfer this to proper application as it is too complex, but I ran out of time for 2014:( Thank you for your help. Any ideas are most welcome. Kind regards, seba -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ macro for parsing a column by 5 rows
Hi, thank you for your help. This is the final version of my code: Sub copy_transpose_chunks() Dim count, target, coordS, coordF As Integer coordS = 1 coordF = 5 target = 1 For count = 1 To 15 Range(A coordS, A coordF).Select Selection.Copy Range(C target).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True coordS = coordS + 5 coordF = coordF + 5 target = target + 1 Next End Sub kind regards, seba Dne sreda, 19. junij 2013 09:34:11 UTC+2 je oseba Vabz napisala: Hi, Use this code.. Sub Select_Row() Range(ActiveCell.Rows, ActiveCell.Offset(4, 0)).EntireRow.Select End Sub This will select 5 rows from active cell. Thanks On Wed, Jun 19, 2013 at 12:48 PM, Seba sebastja...@gmail.comjavascript: wrote: Hi, can someone please advise how to parse a column in excel, so that every 5 rows would be a selection, which I can then copy and further manipulate? Thank you. Kind regards, Seba -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ macro for parsing a column by 5 rows
Actually no, because I want the copied values in a list w/o blanks. regards seba Dne petek, 21. junij 2013 13:15:16 UTC+2 je oseba Vabz napisala: Superb!! I assume you need to increment target by 5 too, coz u r copying 5 rows. Thanks On Fri, Jun 21, 2013 at 4:38 PM, Seba sebastja...@gmail.com javascript: wrote: Hi, thank you for your help. This is the final version of my code: Sub copy_transpose_chunks() Dim count, target, coordS, coordF As Integer coordS = 1 coordF = 5 target = 1 For count = 1 To 15 Range(A coordS, A coordF).Select Selection.Copy Range(C target).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True coordS = coordS + 5 coordF = coordF + 5 target = target + 1 Next End Sub kind regards, seba Dne sreda, 19. junij 2013 09:34:11 UTC+2 je oseba Vabz napisala: Hi, Use this code.. Sub Select_Row() Range(ActiveCell.Rows, ActiveCell.Offset(4, 0)).EntireRow.Select End Sub This will select 5 rows from active cell. Thanks On Wed, Jun 19, 2013 at 12:48 PM, Seba sebastja...@gmail.com wrote: Hi, can someone please advise how to parse a column in excel, so that every 5 rows would be a selection, which I can then copy and further manipulate? Thank you. Kind regards, Seba -- 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/**discussexcelhttps://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...@**googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- 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
$$Excel-Macros$$ macro for parsing a column by 5 rows
Hi, can someone please advise how to parse a column in excel, so that every 5 rows would be a selection, which I can then copy and further manipulate? Thank you. Kind regards, Seba -- 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. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ get data from list and display it in msgbox
Hi, I need help with the getting the data from a list and display it in a msgbox. I don't wan't to do this via the ususal lookup functions in excel, because I'd like to avoid moving around the worksheet too much. Would it be possible to get a macro to do the following: 1. Open a inputbox to enter the search value A. 2. Lookup this value in the list on another worksheet in the same workbook. 3. Display the value pair in the next column in the msgbox. --- Example: Column A Column B Sistem User --- So, I would like to enter the sistem in the inputbox and get the corresponding user back in the msgbox. Thank you for your help. regards, seba -- 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$$ get data from list and display it in msgbox
I can't thank you enough:) It works like a charm! best regards, seba On 5 jan., 14:07, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Seba, Please see attached sheet, Hope it will help you otherwise pls share your workbook with group. On Thu, Jan 5, 2012 at 6:25 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I need help with the getting the data from a list and display it in a msgbox. I don't wan't to do this via the ususal lookup functions in excel, because I'd like to avoid moving around the worksheet too much. Would it be possible to get a macro to do the following: 1. Open a inputbox to enter the search value A. 2. Lookup this value in the list on another worksheet in the same workbook. 3. Display the value pair in the next column in the msgbox. --- Example: Column A Column B Sistem User --- So, I would like to enter the sistem in the inputbox and get the corresponding user back in the msgbox. Thank you for your help. regards, seba -- 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ Sample(Seba).xlsm 25 1KPrikažiPrenesi -- 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$$ excel vba If IsEmpty(Range) code problem
Hi guys, thank you very much for your help. You solved my problem and thank you for different posibilities from which I can learn. Best regards, seba On 10 nov., 18:21, Sam Mathai Chacko samde...@gmail.com wrote: Two options try If Not IsEmpty(Range(A1)) Then Range(B5).Value = 8 Else Exit Sub End If *OR* If Len(Trim(Range(A1))) Then Range(B5).Value = 8 Else Exit Sub End If Regards Sam Mathai Chacko On Thu, Nov 10, 2011 at 10:13 PM, dguillett1 dguille...@gmail.com wrote: you forgot RANGE but to make sure try If len(application.trim(range(**A1)))1 Then Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Seba Sent: Thursday, November 10, 2011 9:48 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ excel vba If IsEmpty(Range) code problem Hi, I am testing this fairly simple code for some other purposes but I always get the value 8 regardless of value in A1: --**--** Sub testPogoja() If IsEmpty(A1) Then Exit Sub Else Range(B5).Value = 8 End If End Sub --**--** I also tried Sub testPogoja() If IsEmpty(A1) = True Then Exit Sub Else Range(B5).Value = 8 End If End Sub --**--** and no luck. What am I doing wrong? regards seba -- FORUM RULES (934+ 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 -- FORUM RULES (934+ 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 -- Sam Mathai Chacko -- FORUM RULES (934+ 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
$$Excel-Macros$$ excel vba If IsEmpty(Range) code problem
Hi, I am testing this fairly simple code for some other purposes but I always get the value 8 regardless of value in A1: Sub testPogoja() If IsEmpty(A1) Then Exit Sub Else Range(B5).Value = 8 End If End Sub I also tried Sub testPogoja() If IsEmpty(A1) = True Then Exit Sub Else Range(B5).Value = 8 End If End Sub and no luck. What am I doing wrong? regards seba -- FORUM RULES (934+ 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
$$Excel-Macros$$ Selecting a range based on a cell value
Hi all, I am facing a problem regarding the range selection. I have a worksheet used for recording daily stuff. SOmething like this: ColAColB ColC January1.1.2011 9:00 - 10:00 January1.1.2011 10:00 - 11:00 January1.1.2011 etc. January2.1.2011 January2.1.2011 January2.1.2011 January3.1.2011 January3.1.2011 January3.1.2011 etc. Other columns contain daily specific data. At the end of each month I have to create reports on what was done in that month. Hence I need to copy the range for all stuff in that month (for example January) and paste it to another worksheet in the same workbook. My problem is that I can't use fixed ranges, because of the February 29th. I tried several things before posting here: 1) I wrote macro to copy and paste the entire range (all months) to the new sheet and have it delete all entries which didn't contain the month in question. This turned to be a pain due to 3500 and some rows. 2) I wanted to search for the required month and loop over all rows, but this would also be time consuming. 3) Then I tried filtering the range to display only entries with respective month, however I encountered date format issues: Macro for April recordes like this: ActiveSheet.Range($A$1:$AS$3653).AutoFilter Field:=3, Operator:= _ xlFilterValues, Criteria2:=Array(1, 4/1/2011) I modified this to: ActiveSheet.Range($A$3:$IN$3653).AutoFilter Field:=3, Operator:= _ xlFilterValues, Criteria2:=Array(1, MyMonth) Whereas MyMonth is the variable defined earlier so I can use one macro for all 12 months. Here the format is the problem. My locale entry convention is: dd.mm. But the macro syntax only works if I put in: mm/dd/, which is not the format used in our country. I also tried to modify the format with: MyMonth= Format(MyMonth, mm- dd-) but I always get the date displayed with full stops and not with slashes. How can I fix this formatting issue or maybe in a more efficient way select the required range for desired month? Thank you for your help. seba -- -- 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
Re: $$Excel-Macros$$ prompting for files for data import
I decided to go with Sam's suggestion, it suites me more. I would like to thank you both on such prompt help. Regards, seba On 5 okt., 19:37, Sam Mathai Chacko samde...@gmail.com wrote: If you need prompts, and if the files are in different folders, then Application.GetOpenFileName (my earlier code uses it) can be helpful Regards, Sam On Wed, Oct 5, 2011 at 11:06 PM, Sam Mathai Chacko samde...@gmail.comwrote: Just take care of the sheet activation also. If you are going with the loop, then use Sheets(List i).activate Regards, Sam On Wed, Oct 5, 2011 at 10:32 PM, dguillett1 dguille...@gmail.com wrote: That can be done but it's probably easier to know the folder you need and modify my idea to suit Sub GetDataSAS() 'DATA_20110101, DATA_20110201 myfolder = yourdriveletter:\**yourfoldername\ 'these variables could be entered in a cell startname = DATA_20110 endname = 01 For i = 1 To 3 mystring = myfolder startname i endname MsgBox mystring With ActiveSheet.QueryTables.Add(**Connection:=TEXT; _ mystring .csv, Destination:=Range(A1)) 'your stuff 'copy needed range to other sheet next i end sub -Original Message- From: Seba Sent: Wednesday, October 05, 2011 10:51 AM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ prompting for files for data import Thank you for your help, however, if I understand your suggestion correctly, this would still open files from one and only location? In my case, the source files are in different locations every month and have different endings in their names: DATA_20110101, DATA_20110201, etc... That is why I need to be prompted to select the files manually. On 5 okt., 16:56, dguillett1 dguille...@gmail.com wrote: The example you provide makes it fairly easy for i= 1 to 3 Sheets(List 1).QueryTables.Add(Connection:**= _ TEXT;Path to my file I .csv, Destination:= sheets(List 1).range(a1) 'etc next i However, you can probably do it with ONE query table using the variables and have your macro copy only the desired data elsewhere.' for i= 1 to 3 Sheets(sourcesheet).**QueryTables.Add(Connection:= _ TEXT;Path to my file I .csv, Destination:= sheets(sourcesheet).range(**a1) 'etc copy range(a2:z4) sheets(destinationsheet).**range(a1) next i -Original Message- From: Seba Sent: Wednesday, October 05, 2011 4:37 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ prompting for files for data import Hi all, I have to import data from 3 different files into 1 and format the data. Since everything is more or less fixed, I have recorded the macro below and removed the code for scrolling and such. The only problem for me is that each month the locations and filenames of the files to import are changing. What woudl the code be so the excel prompts me for each file? Thank you in advance for your help. Code: --**--** - Sub uvoz_podatkov() ' ' uvoz_podatkov Makro ' ' With ActiveSheet.QueryTables.Add(**Connection:= _ TEXT;Path to my file1.csv, Destination:= _ Range($A$1)) .Name = MyFile1 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets(List2).Select With ActiveSheet.QueryTables.Add(**Connection:= _ TEXT;Path to my file2.csv, Destination:= _ Range($A$1)) .Name = MyFile2 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited
$$Excel-Macros$$ prompting for files for data import
Hi all, I have to import data from 3 different files into 1 and format the data. Since everything is more or less fixed, I have recorded the macro below and removed the code for scrolling and such. The only problem for me is that each month the locations and filenames of the files to import are changing. What woudl the code be so the excel prompts me for each file? Thank you in advance for your help. Code: - Sub uvoz_podatkov() ' ' uvoz_podatkov Makro ' ' With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file1.csv, Destination:= _ Range($A$1)) .Name = MyFile1 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets(List2).Select With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file2.csv, Destination:= _ Range($A$1)) .Name = MyFile2 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets(List3).Select With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file3.csv, Destination:= _ Range($A$1)) .Name = MyFile3 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range(A:I,K:L).Select Range(K1).Activate Range(A:I,K:L,O:O,R:R,T:T,V:W,Y:Z).Select Range(Y1).Activate Range(A:I,K:L,O:O,R:R,T:T,V:W,Y:Z,AB:AC,AE:AF,AH:AI).Select Range(AH1).Activate Range(A:I,K:L,O:O,R:R,T:T,V:W,Y:Z,AB:AC,AE:AF,AH:AI,AK:AN).Select Range(AK1).Activate Selection.Delete Shift:=xlToLeft Rows(1:1).Select Selection.AutoFilter Range(D:D,A:A).Select Range(A1).Activate With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.53896298105 .PatternTintAndShade = 0 End With Sheets(List2).Select Columns(A:I).Select Range(A:I,N:N,R:R,T:U).Select Range(T1).Activate Range(A:I,N:N,R:R,T:U,W:X,Z:AA,AC:AD).Select Range(AC1).Activate Range(A:I,N:N,R:R,T:U,W:X,Z:AA,AC:AD,AF:AG,AI:AJ).Select
Re: $$Excel-Macros$$ prompting for files for data import
Thank you for your help, however, if I understand your suggestion correctly, this would still open files from one and only location? In my case, the source files are in different locations every month and have different endings in their names: DATA_20110101, DATA_20110201, etc... That is why I need to be prompted to select the files manually. On 5 okt., 16:56, dguillett1 dguille...@gmail.com wrote: The example you provide makes it fairly easy for i= 1 to 3 Sheets(List 1).QueryTables.Add(Connection:= _ TEXT;Path to my file I .csv, Destination:= sheets(List 1).range(a1) 'etc next i However, you can probably do it with ONE query table using the variables and have your macro copy only the desired data elsewhere.' for i= 1 to 3 Sheets(sourcesheet).QueryTables.Add(Connection:= _ TEXT;Path to my file I .csv, Destination:= sheets(sourcesheet).range(a1) 'etc copy range(a2:z4) sheets(destinationsheet).range(a1) next i -Original Message- From: Seba Sent: Wednesday, October 05, 2011 4:37 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ prompting for files for data import Hi all, I have to import data from 3 different files into 1 and format the data. Since everything is more or less fixed, I have recorded the macro below and removed the code for scrolling and such. The only problem for me is that each month the locations and filenames of the files to import are changing. What woudl the code be so the excel prompts me for each file? Thank you in advance for your help. Code: - Sub uvoz_podatkov() ' ' uvoz_podatkov Makro ' ' With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file1.csv, Destination:= _ Range($A$1)) .Name = MyFile1 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets(List2).Select With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file2.csv, Destination:= _ Range($A$1)) .Name = MyFile2 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets(List3).Select With ActiveSheet.QueryTables.Add(Connection:= _ TEXT;Path to my file3.csv, Destination:= _ Range($A$1)) .Name = MyFile3 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False
$$Excel-Macros$$ Audit Trail
Hi all, I need to setup a Audi Trail system for my excel workbooks and I found this code on the net by google search. - Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim strFileName As String strFileName = Location\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Multiple cells changed Else Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Target.Value End If Close #1 End Sub This does the job, but I would like to have all values logged when multiple cells are changed. Lets say I paste a range. In the log file it just states Multiple cells changed. I tried modifying it, however with no success. Can anyone please help? Thank you. Best regards, seba -- -- 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
Re: $$Excel-Macros$$ Audit Trail
Hi Paul, thank you very much for aour effort. There is just one thing: I paste 2 values in S14 and S15 and the macro records values in S13 and S14. Cold you please help. regards, seba On 29 sep., 14:15, Paul Schreiner schreiner_p...@att.net wrote: The trick here is knowing that when target.count 1, then the Target Range becomes an array. So, to get the cell address and values, you have to loop through the array.. like: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim inx Dim strFileName As String strFileName = c:\temp\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then For inx = 0 To Target.Count - 1 Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target(inx).Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target(inx).Value Next inx Else Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target.Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target.Value End If Close #1 End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, September 29, 2011 4:18:58 AM Subject: $$Excel-Macros$$ Audit Trail Hi all, I need to setup a Audi Trail system for my excel workbooks and I found this code on the net by google search. - Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim strFileName As String strFileName = Location\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Multiple cells changed Else Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Target.Value End If Close #1 End Sub This does the job, but I would like to have all values logged when multiple cells are changed. Lets say I paste a range. In the log file it just states Multiple cells changed. I tried modifying it, however with no success. Can anyone please help? Thank you. Best regards, seba -- -- 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 linkhttp://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
Re: $$Excel-Macros$$ Hijacked Thread
Hi Paul, I just took the your code above: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim inx Dim strFileName As String strFileName = MyLocation\MyFile.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then For inx = 0 To Target.Count - 1 Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target(inx).Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target(inx).Value Next inx Else Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target.Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target.Value End If Close #1 End Sub On 29 sep., 14:50, Paul Schreiner schreiner_p...@att.net wrote: Amresh, If you have a question, please post a NEW discussion thread instead of hijacking another discussion. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Amresh Maurya amreshkushw...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, September 29, 2011 8:42:48 AM Subject: Re: $$Excel-Macros$$ Audit Trail Guys, you have any presentation ppt on internet.if you have pls send me.. Regards Amresh On Thu, Sep 29, 2011 at 5:40 AM, Seba sebastjan.hri...@gmail.com wrote: Hi Paul, thank you very much for aour effort. There is just one thing: I paste 2 values in S14 and S15 and the macro records values in S13 and S14. Cold you please help. regards, seba On 29 sep., 14:15, Paul Schreiner schreiner_p...@att.net wrote: The trick here is knowing that when target.count 1, then the Target Range becomes an array. So, to get the cell address and values, you have to loop through the array.. like: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim inx Dim strFileName As String strFileName = c:\temp\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then For inx = 0 To Target.Count - 1 Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target(inx).Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target(inx).Value Next inx Else Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target.Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target.Value End If Close #1 End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, September 29, 2011 4:18:58 AM Subject: $$Excel-Macros$$ Audit Trail Hi all, I need to setup a Audi Trail system for my excel workbooks and I found this code on the net by google search. - Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim strFileName As String strFileName = Location\file_name.txt 'Change
Re: $$Excel-Macros$$ Audit Trail
Hi Paul, thank you again!!! It works perfectly:) Best regards, seba On 29 sep., 15:32, Paul Schreiner schreiner_p...@att.net wrote: Evidently, the Target array doesn't start at '0' like normal arrays! change the loop to: For inx = 1 To Target.Count and it should work just fine. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Paul Schreiner schreiner_p...@att.net To: excel-macros@googlegroups.com Sent: Thu, September 29, 2011 8:49:58 AM Subject: Re: $$Excel-Macros$$ Audit Trail Interesting... Can you please post the code you're using? (at least for the loop) Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, September 29, 2011 8:40:25 AM Subject: Re: $$Excel-Macros$$ Audit Trail Hi Paul, thank you very much for aour effort. There is just one thing: I paste 2 values in S14 and S15 and the macro records values in S13 and S14. Cold you please help. regards, seba On 29 sep., 14:15, Paul Schreiner schreiner_p...@att.net wrote: The trick here is knowing that when target.count 1, then the Target Range becomes an array. So, to get the cell address and values, you have to loop through the array.. like: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim inx Dim strFileName As String strFileName = c:\temp\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then For inx = 0 To Target.Count - 1 Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target(inx).Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target(inx).Value Next inx Else Print #1, ActiveWorkbook.Name _ vbTab _ Sh.Name _ vbTab _ Target.Address _ vbTab _ Environ(username) _ vbTab _ Date _ vbTab _ Time _ vbTab _ Target.Value End If Close #1 End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, September 29, 2011 4:18:58 AM Subject: $$Excel-Macros$$ Audit Trail Hi all, I need to setup a Audi Trail system for my excel workbooks and I found this code on the net by google search. - Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim strFileName As String strFileName = Location\file_name.txt 'Change as appropriate Open strFileName For Append As #1 If Target.Count 1 Then Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Multiple cells changed Else Print #1, ActiveWorkbook.Name vbTab Sh.Name vbTab Target.Address vbTab Environ(username) vbTab Date vbTab Time vbTab Target.Value End If Close #1 End Sub This does the job, but I would like to have all values logged when multiple cells are changed. Lets say I paste a range. In the log file it just states Multiple cells changed. I tried modifying it, however with no success. Can anyone please help? Thank you. Best regards, seba -- -- - Some important links for excel users: 1. Follow
$$Excel-Macros$$ Open file using criteria from input box
Hi all, I need help tweaking one of my simple macros. I would like to open a file after I enter a value into a input box. I have files stored in a specific folder and each one is named like year_sequence_number, ie: 2011_001. I need a macro which would open a file based on a value I enter. For example: I enter the value 123 and I get the file 2011_123 opened. First I wrote this using my basic knowledge: Sub OpenFile() Dim File As Integer File = InputBox(Vnesi nalog) If File = 001 Then Workbooks.Open Filename:=C:\Users\user\Desktop\2011_001.xlsx Else Exit Sub End If End Sub Now is it possible and how to modify this so I wouldn't have to copy and paste this a thousand times:) And if possible, I would only like to enter the last three digits as a value: just 001 and not the full name 2011_001. Thank you and best regards, seba -- -- 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
Re: $$Excel-Macros$$ Open file using criteria from input box
Sam, thank you very much for your help and efforts. Works like a charm. You just made my monday easier:) regards, seba On 11 sep., 22:55, Sam Mathai Chacko samde...@gmail.com wrote: Solution already posted D. Could you confirm this was not visible in the forum? If so, then I'd like the moderators to please check why other members are not able to see solutions or answers that are already posted, and waste their development time. Also D, please test the code before posting. Seba, Sub OpenFile() Dim File As String File = InputBox(Vnesi nalog) If Len(Dir(C:\Users\user\Desktop\2011_ File .xlsx)) 0 Then Workbooks.Open Filename:=C:\Users\user\Desktop\2011_ File .xlsx Else MsgBox File does not exists! End If End Sub On Sun, Sep 11, 2011 at 11:51 PM, dguillett1 dguille...@gmail.com wrote: try Sub OpenFile() Workbooks.Open Filename:= _ C:\Users\user\Desktop\2011_ InputBox(Vnesi nalog).xlsx End Sub -Original Message- From: Seba Sent: Sunday, September 11, 2011 3:31 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Open file using criteria from input box Hi all, I need help tweaking one of my simple macros. I would like to open a file after I enter a value into a input box. I have files stored in a specific folder and each one is named like year_sequence_number, ie: 2011_001. I need a macro which would open a file based on a value I enter. For example: I enter the value 123 and I get the file 2011_123 opened. First I wrote this using my basic knowledge: --**--** --**-- Sub OpenFile() Dim File As Integer File = InputBox(Vnesi nalog) If File = 001 Then Workbooks.Open Filename:=C:\Users\user\**Desktop\2011_001.xlsx Else Exit Sub End If End Sub --**--** --**-- Now is it possible and how to modify this so I wouldn't have to copy and paste this a thousand times:) And if possible, I would only like to enter the last three digits as a value: just 001 and not the full name 2011_001. Thank you and best regards, seba -- --**--** -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/** exceldailytip http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/**groups?gid=1871310http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.**blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros athttp://www.quickvba.blogspot.**comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.**comhttp://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/**discussexcelhttp://www.facebook.com/discussexcel -- --**--** -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/** exceldailytip http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/**groups?gid=1871310http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.**blogspot.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros athttp://www.quickvba.blogspot.**comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.**comhttp://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/**discussexcelhttp://www.facebook.com/discussexcel -- Sam Mathai Chacko -- -- 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
Re: $$Excel-Macros$$ copy selected range to target with params
Hi, thank you for your effort. This kind of macro would help, I would just like to improve on the pasting part. I will try and build on your code. Right now I have to select target sheet and target range for pasting manually. I need to upgrade this so that macro will also select the target apropriate sheet and range. In order to do this I probably have to define all existing subject areas (ie ranges) and then gather parameters via input boxes or ad list on the form you created. So it would go like this: 1. Select source range - manually 2. Define parameters: project, subproject and subject area - manually by inputing or selecting data from predefined list 3. Open target file - manually 4. Paste values with transpose to corresponding range, depending on the defined parameters. 5. Save and close I know its not as simple as I've just written, since there are many subject areas and consequently many if clauses If you or anyone wil try to help me with this further I appreciate it very much and odn't worry about the exact source and target ranges, I can modify them later. Just note as above: --- My source workbook contains info on several subprojects. From here I need 3 items to be copied: - project number (always located in A2) - subproject number (each subproject is offset 1 line downwards: A5 = subproject 1, A6 = subproj. 2, etc.) - details for each subproject (represented as values in range: from B5 to H5) these items need to be copied and pasted with transpose in the target range. However there are 2 tricks to it: There are different target ranges pertaining to different subject areas. If a project belongs to a subject area LEGAL it should be pasted to that subject area target range. The other trick is to look for the first empty column in the target range so that existing values are not overwritten. Example for target range LEGAL: columns from A56:66 to G56:66 --- thx and regards, seba On 11 avg., 17:10, Rajan_Verma rajanverma1...@gmail.com wrote: See if it helps After select the Range which need to be Copies Press Alt+F8 and Run the Macro Show, -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Seba Sent: Wednesday, August 10, 2011 8:07 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ copy selected range to target with params hi, I need help with writing a macro which would copy selected range and prompt me for target workbook, sheet and range. It would need to copy only values and transpose them. any help is greatly appreciated. Regards, seba -- -- 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 linkhttp://www.facebook.com/discussexcel Book2.xlsm 26 1KPrikažiPrenesi -- -- 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
Re: $$Excel-Macros$$ copy selected range to target with params
I apologize for insufficient info. I am starting from scratch, but I don't see the option for file upload (where can I upload it?) so I'll try and explain in more detail. My source workbook contains info on several subprojects. From here I need 3 items to be copied: - project number (always located in A2) - subproject number (each subproject is offset 1 line downwards: A5 = subproject 1, A6 = subproj. 2, etc.) - details for each subproject (represented as values in range: from B5 to H5) these items need to be copied and pasted with transpose in the target range. However there are 2 tricks to it: There are different target ranges pertaining to different subject areas. If a project belongs to a subject area LEGAL it should be pasted to that subject area target range. The other trick is to look for the first empty column in the target range so that existing values are not overwritten. Example for target range LEGAL: columns from A56:66 to G56:66 the problem for me is to get the excel to prompt me to select the target workbook and gather the parameters for pasting. Thx a lot, seba On 10 avg., 20:53, dguillett1 dguille...@gmail.com wrote: Post a file with a more complete explanation -Original Message- From: Seba Sent: Wednesday, August 10, 2011 9:36 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ copy selected range to target with params hi, I need help with writing a macro which would copy selected range and prompt me for target workbook, sheet and range. It would need to copy only values and transpose them. any help is greatly appreciated. Regards, seba -- --- --- 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 linkhttp://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$$ copy selected range to target with params
hi, I need help with writing a macro which would copy selected range and prompt me for target workbook, sheet and range. It would need to copy only values and transpose them. any help is greatly appreciated. Regards, seba -- -- 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
Re: $$Excel-Macros$$ build a database from multiple workbooks
Here is the solution, if anyone would benefit from this: --- Sub Delete_Empty() Application.ScreenUpdating = False Dim a As Range Dim SrchRnga Set SrchRnga = ActiveSheet.Range(D1, ActiveSheet.Range(D100).End(xlUp)) Do Set a = SrchRnga.Find(0, LookIn:=xlValues) If Not a Is Nothing Then a.EntireRow.Delete Loop While Not a Is Nothing End Sub -- regards seba On 2 feb., 19:40, Seba sebastjan.hri...@gmail.com wrote: Hi, I need further assistance on this matter. After I create a database, how can I delete entire rows where cell in column C (C2, C3,) equals 0? thank you in advance, seba On 28 jan., 15:33, ashish koul koul.ash...@gmail.com wrote: alright let us know if you require any help On Fri, Jan 28, 2011 at 2:51 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I made the following adjustment (marked with stars). It works fine. Do you see any error in code or possible improvements? I will make an addtional macro for: removing double entries + deleting rows with value=0 + sorting My modification: -- Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select *z = ActiveCell.Row + 1* Range(A z).Select ***Selection.PasteSpecial Paste:=xlPasteValues** ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub -- regards seba On 27 jan., 17:31, ashish koul koul.ash...@gmail.com wrote: Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook * 'chnage sheet name here* *Sheets(PRIPRAVA_PROJEKTA).Select * Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 2 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub On Thu, Jan 27, 2011 at 9:57 PM, ashish koul koul.ash...@gmail.com wrote: put sheetnames always in double quotes Sheets(PRIPRAVA_PROJEKTA). On Thu, Jan 27, 2011 at 9:23 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I made the following change for sheet name and I get the error Subscript out of range. Sheets(1) - Sheets(PRIPRAVA_PROJEKTA) Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k
Re: $$Excel-Macros$$ build a database from multiple workbooks
Hi, I need further assistance on this matter. After I create a database, how can I delete entire rows where cell in column C (C2, C3,) equals 0? thank you in advance, seba On 28 jan., 15:33, ashish koul koul.ash...@gmail.com wrote: alright let us know if you require any help On Fri, Jan 28, 2011 at 2:51 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I made the following adjustment (marked with stars). It works fine. Do you see any error in code or possible improvements? I will make an addtional macro for: removing double entries + deleting rows with value=0 + sorting My modification: -- Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select *z = ActiveCell.Row + 1* Range(A z).Select ***Selection.PasteSpecial Paste:=xlPasteValues** ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub -- regards seba On 27 jan., 17:31, ashish koul koul.ash...@gmail.com wrote: Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook * 'chnage sheet name here* *Sheets(PRIPRAVA_PROJEKTA).Select * Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 2 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub On Thu, Jan 27, 2011 at 9:57 PM, ashish koul koul.ash...@gmail.com wrote: put sheetnames always in double quotes Sheets(PRIPRAVA_PROJEKTA). On Thu, Jan 27, 2011 at 9:23 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I made the following change for sheet name and I get the error Subscript out of range. Sheets(1) - Sheets(PRIPRAVA_PROJEKTA) Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(PRIPRAVA_PROJEKTA).Select Workbooks.Open Filename:=Sheets(PRIPRAVA_PROJEKTA).Range(a i).Value Set ask2 = ActiveWorkbook Sheets(PRIPRAVA_PROJEKTA).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets
Re: $$Excel-Macros$$ build a database from multiple workbooks
Hi, I made the following adjustment (marked with stars). It works fine. Do you see any error in code or possible improvements? I will make an addtional macro for: removing double entries + deleting rows with value=0 + sorting My modification: -- Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select *z = ActiveCell.Row + 1* Range(A z).Select ***Selection.PasteSpecial Paste:=xlPasteValues** ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub -- regards seba On 27 jan., 17:31, ashish koul koul.ash...@gmail.com wrote: Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook * 'chnage sheet name here* *Sheets(PRIPRAVA_PROJEKTA).Select * Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 2 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub On Thu, Jan 27, 2011 at 9:57 PM, ashish koul koul.ash...@gmail.com wrote: put sheetnames always in double quotes Sheets(PRIPRAVA_PROJEKTA). On Thu, Jan 27, 2011 at 9:23 PM, Seba sebastjan.hri...@gmail.com wrote: Hi, I made the following change for sheet name and I get the error Subscript out of range. Sheets(1) - Sheets(PRIPRAVA_PROJEKTA) Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k As Long s = 1 k = 1 Dim x As String Dim temp As String Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(PRIPRAVA_PROJEKTA).Select Workbooks.Open Filename:=Sheets(PRIPRAVA_PROJEKTA).Range(a i).Value Set ask2 = ActiveWorkbook Sheets(PRIPRAVA_PROJEKTA).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 1 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate ask2.Close End If Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub - regards seba On 27 jan., 16:44, ashish koul koul.ash...@gmail.com wrote: shannur can you attch the sample workbook On Thu, Jan 27, 2011 at 9:13 PM, ashish koul koul.ash...@gmail.com wrote: use 2 for second sheet or 3 for 3 rd sheet like sheets(2).select or you can also sheets
Re: $$Excel-Macros$$ build a database from multiple workbooks
Hi Ashish, I can't thank you enough for this great solution:) I'd hate to be a pain in the neck but I do have a couple of questions more. Would it be possible to specify only one sheet to be merged? My workbooks contain several, but only one is relevant for the database. And, if I run this macro once per month I guess there would be double or triple entries and so forth for the same workbook in the database. I guess one workaround is to manually delete the files that have already been merged from the list the macro makes. But can this be done automatically? Thank you. regards, seba On 27 jan., 07:07, ashish koul koul.ash...@gmail.com wrote: Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k, d As Long s = 1 k = 1 Dim x As String Dim temp As String Dim sht As Worksheet Set ask2 = ActiveWorkbook Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook For d = 1 To ask2.Sheets.Count Sheets(d).Activate Sheets(d).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select ' Selection.End(xlToRight).Select ' ' 'temp = ActiveCell.Address 'x = Mid(temp, 2, (InStr(2, temp, $) - 2)) ' ' ' ' Range(A65356).Select ' Selection.End(xlUp).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 2 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate End If Next d ask2.Activate ask2.Close ask.Activate ask.Sheets(1).Activate ActiveWorkbook.Save Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub On Thu, Jan 27, 2011 at 11:23 AM, Squall squall.l...@gmail.com wrote: Hi guys, Could you please share us the coding especially when helping... it really help to improve those being helped (especially me) to understand the vba macro/coding. Sometimes when I try to open the module/code, it's protected by password :( Nonetheless, thanks for the help. On 1/26/2011 10:33 PM, ashish koul wrote: try this macro see if it helps On Wed, Jan 26, 2011 at 4:22 PM, Seba sebastjan.hri...@gmail.com wrote: Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- 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/discussexcel -- *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. -- -- Some important links for excel
Re: $$Excel-Macros$$ build a database from multiple workbooks
Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- 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/discussexcel -- *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. -- -- 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$$ macro for adding comments to cells
Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- 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
Re: $$Excel-Macros$$ build a database from multiple workbooks
Thank you both. I hadn't had the chance the above macro. I will report back how that turns out. To answer your questions Paul, I already have all workbooks in one folder and there aren't any other files there. And any other new workbook I create is also saved in this folder. Workbooks are named for example: year_sequence_number: 2011_001; 2011_002, ... Thank you all for your help. regards, seba On 26 jan., 17:03, Paul Schreiner schreiner_p...@att.net wrote: There are a several ways to accomplish this... Are all of the files in a single folder? Are there other files there? Or...how do you want to identify the files? We can: A)use a sheet to list all of the files. then, loop through the list and process each workbook. B)Place all of the files in one folder, then process each workbook in the folder. The loops required to copy the data is pretty simple. Let me know what approach you'd like to take and I'd be glad to help put together the macro. Paul From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wed, January 26, 2011 5:52:59 AM Subject: Re: $$Excel-Macros$$ build a database from multiple workbooks Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- 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/discussexcel -- *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. -- -- 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 linkhttp://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
Re: $$Excel-Macros$$ macro for adding comments to cells
Hello Roberto, works like a charm:) I just have one additional question. How can I define the target range on a separate sheet. Example: Sheet1 = source range Sheet2 = target range And to make the matter even harder, the target range is transposed, so if source = a1:a10, target range = a1:j1 Thank you very much. This is a lifesavior for me. And I would be very grateful if someone could explain how to attach a sample workbook or any file for that matter. I don't see any option for that. regards, seba On 26 jan., 17:12, roberto mensa robb@gmail.com wrote: try Sub test() Dim rng As Excel.Range Dim v As Excel.Range Set rng = [a1:a10] For Each v In rng write_comment v.Offset(, 1), v Next End Sub Sub write_comment(rngc As Excel.Range, rngt As Excel.Range) If TypeName(rngc.Comment) = Nothing Then rngc.AddComment.Text End If rngc.Comment.Text CStr(rngt.Value) End Sub regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- 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/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$$ build a database from multiple workbooks
Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- 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
