$$Excel-Macros$$ Lookup multiple columns data using vba
Hello, I need your help. I was wondering if someone would tweak it or better please help me with a solution to my problem. Here is what I would to achieve: For example: from row2 to the lastrow in Sheet2: If any combination of Ai&Ci&Di in sheet2 is found in Sheet1, then copy entire sheet 1 row and paste it in sheet3; If Ai&Ci&Di is not found in sheet1 then highlight entire the row in sheet2. I hope it makes sense. Attached is the file. Thanks. -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. LookUp.xlsb Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
$$Excel-Macros$$ VBA Code to sort on multiple columns and print out result
Hello, I have a worksheet that I would like to sort on multiple columns and then print out the result with the title of the report on the first row. I would like to get the worksheet "Data", in the attached file, filtered by "Input date" (column "E"),"Sex"(Column "C" )and then "Name" (Column"A"); copy the result on another worksheet"MyFilteredResult", with the title on the first row and print it out. Any Help would be greatly appreciated. -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Book1.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Re: Item search in Userform
Paul, > I loaded all the available Begin Dates into the Begin Date combobox. > I created a change event in which when you select a begin date, > all the available dates AFTER that date are loaded into the End box > (no sense making it possible to select an end date that occurs BEFORE the > begin date) > > Once you select the End date, the list box is populated. > You are absolutely right. I am still a newbie; The "Search" code I have in the userform doesn't work. It was from another project i came across couple months ago. I surely appreciate your help. Regards On Wednesday, October 15, 2014 7:51:50 PM UTC-4, Awal wrote: > > Attached is what I've done thus far. > Regards > > On Wednesday, October 15, 2014 12:23:17 PM UTC-4, Awal wrote: >> >> Hello, >> With a Userform, I would like to search a database, display the result of >> the search in the form and be able to print it out. See screen shot below. >> I am attaching a sample file. I've already created the Userform and I would >> like someone to please help me. >> >> Regards. >> >> [image: Inline image 2] >> >> >> -- 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/d/optout.
$$Excel-Macros$$ Re: Item search in Userform
Attached is what I've done thus far. Regards On Wednesday, October 15, 2014 12:23:17 PM UTC-4, Awal wrote: > > Hello, > With a Userform, I would like to search a database, display the result of > the search in the form and be able to print it out. See screen shot below. > I am attaching a sample file. I've already created the Userform and I would > like someone to please help me. > > Regards. > > [image: Inline image 2] > > > -- 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/d/optout. Project 102.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ sharing of excel workbook
Try this: If ActiveWorkbook.MultiUserEditing Then Application.DisplayAlerts = False ActiveWorkbook.ExclusiveAccess End If Insert your code here If Not ActiveWorkbook.MultiUserEditing Then Application.DisplayAlerts = False ActiveWorkbook.saveas ActiveWorkbook.Name, accessmode:=xlShared 'Application.DisplayAlerts = True 'MsgBox "Now Shared" End If The trick worked for meHope it helps. On Friday, August 29, 2014 7:45:23 PM UTC-4, Awal wrote: > > Try this: > > Enter code here... If ActiveWorkbook.MultiUserEditing Then >Application.DisplayAlerts = False >ActiveWorkbook.ExclusiveAccess >End If > > YOUR CODE HERE > > Enter code here...If Not ActiveWorkbook.MultiUserEditing Then > Application.DisplayAlerts = False > ActiveWorkbook.saveas ActiveWorkbook.Name, accessmode:=xlShared > 'Application.DisplayAlerts = True > 'MsgBox "Now Shared" > End If > > > > > On Wednesday, August 27, 2014 9:47:12 AM UTC-4, hilary lomotey wrote: >> >> its a lot of macro for different functions can it be done by your >> suggestion? >> >> >> On Wed, Aug 27, 2014 at 2:35 PM, Vaibhav Joshi wrote: >> >>> Hi >>> >>> Try converting macro to excel add-in & can install it to all user. I am >>> not sure but it might work. >>> >>> Cheers!! >>> >>> >>> On Wed, Aug 27, 2014 at 6:57 PM, Hilary Lomotey >>> wrote: >>> >>>> Hello Experts, >>>> >>>> i have an excel templates where i want users to be able to access it >>>> almost at the smae time if the so wish so i activated the share workbook >>>> option on the review menu, however, its giving an error because the >>>> template contains macro and it says macros cannot be shared, pls is there >>>> a >>>> way around this where different users can access my file at the same time. >>>> thanks >>>> >>>> -- >>>> 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. >>>> To post to this group, send email to excel-...@googlegroups.com. >>>> Visit this group at http://groups.google.com/group/excel-macros. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >>> 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 confiden
Re: $$Excel-Macros$$ sharing of excel workbook
Try this: Enter code here... If ActiveWorkbook.MultiUserEditing Then Application.DisplayAlerts = False ActiveWorkbook.ExclusiveAccess End If YOUR CODE HERE Enter code here...If Not ActiveWorkbook.MultiUserEditing Then Application.DisplayAlerts = False ActiveWorkbook.saveas ActiveWorkbook.Name, accessmode:=xlShared 'Application.DisplayAlerts = True 'MsgBox "Now Shared" End If On Wednesday, August 27, 2014 9:47:12 AM UTC-4, hilary lomotey wrote: > > its a lot of macro for different functions can it be done by your > suggestion? > > > On Wed, Aug 27, 2014 at 2:35 PM, Vaibhav Joshi > > wrote: > >> Hi >> >> Try converting macro to excel add-in & can install it to all user. I am >> not sure but it might work. >> >> Cheers!! >> >> >> On Wed, Aug 27, 2014 at 6:57 PM, Hilary Lomotey > > wrote: >> >>> Hello Experts, >>> >>> i have an excel templates where i want users to be able to access it >>> almost at the smae time if the so wish so i activated the share workbook >>> option on the review menu, however, its giving an error because the >>> template contains macro and it says macros cannot be shared, pls is there a >>> way around this where different users can access my file at the same time. >>> thanks >>> >>> -- >>> 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 . >>> To post to this group, send email to excel-...@googlegroups.com >>> . >>> Visit this group at http://groups.google.com/group/excel-macros. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> 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 . >> To post to this group, send email to excel-...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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+uns
$$Excel-Macros$$ Re: assigning color to cells on basis of value
Here is code snippet I got from the net:
Dim MyRange As Range
'vba to do: I need to BG a cell based on it's value
'IF A,B or C it is green
'IF D,E,F it is Blue etc
'So, once I past a value in a cell I need to call a function to evaluate it
then give it the proper BG color
'What range do we care about?
Set MyRange = Range("A2:B10")
'Not a cell we care about?
If Intersect(Target, MyRange) Is Nothing Then Exit Sub
'Don't do anything if more than 1 cell changed
If Target.Count > 1 Then Exit Sub
'In case we run into trouble, make sure events get turned back on
On Error GoTo MyReset
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim myValue As String
myValue = UCase(Target.Value)
'We can give multiple possibilities in each case to
'indicate an "or" type logic
Select Case myValue
Case "A", "B", "C"
Target.Interior.Color = vbGreen
Case "D", "E", "F"
Target.Interior.Color = vbBlue
Case Else
Target.Interior.Color = xlNone
End Select
MyReset:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Hope it helps!!
On Saturday, June 7, 2014 5:58:13 AM UTC-4, Keertee Taneja wrote:
>
>
>
> I have drawn up an this excel sheet which contains returns on a security.
> It contains both positive and negative values. I want to assign different
> colours to positive and negative values. How can I do both of them
> together. I used conditional formatting of greater than 0 it highlighted
> the positive figures and when using the less than 0 only negative figures
> were highlighted.I want to get different colours for both positive and
> negative gigures simultaneously.
>
--
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/d/optout.
$$Excel-Macros$$ Shared WorkBook is slow
Hello, I've got a shared workbook which is used by a number of people in my office. It's got quite a lot of macros in it, but before I put it on the network as a shared workbook it worked fine - it was quick to respond. Now that I've made it a shared workbook it is horribly slow, both on opening, and when you run the macros within it. How will I optimize and speed up the macros in Shared status? Any help will be greatly appreciated. Thank you -- 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$$ Re: Lookup and Replace
Thank you all for your help. I greatly appreciated it. On Wednesday, January 2, 2013 10:17:41 PM UTC-5, Awal wrote: > > Hello, > I would like to wish y'all a Happy New year... > I am still learning VBA and I really need help: > I want to be able to look up for an item and change its price. > See attached file.Can someone please help? > Thanks. > -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Lookup and Replace
Hello, I would like to wish y'all a Happy New year... I am still learning VBA and I really need help: I want to be able to look up for an item and change its price. See attached file.Can someone please help? Thanks. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. test.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ List Box or copy past Shwon based on typing leter
Maries, That was awesome!! I was asking myself what if there is a need to expand the range? So I am wondering if using a range name will do the trick i.e update the range : Formula>>>name manager ( I am using excel 2010).I've tried it but it didn't work. Please advise. Regards On Feb 17, 5:39 am, Shaik Waheed wrote: > Hi Maries, > > Thats really great..Thank you.. > > > > > > > > On Fri, Feb 17, 2012 at 3:10 PM, Maries wrote: > > Hi Shaik, > > > Check this link... > > >http://www.screencast.com/t/69UdkpgmFv > > > Regards, > > > MARIES. > > > On Thu, Feb 16, 2012 at 3:05 PM, Shaik Waheed wrote: > > >> Hi Maries, > >> Can u tell me how did u do that.. > > >> On Thu, Feb 16, 2012 at 3:25 PM, Maries wrote: > > >>> Find the attachments.. > > >>> On Thu, Feb 16, 2012 at 1:35 PM, chandra sekaran >>> > wrote: > > Hi , > > Here with attchaed file we need smal macro when i type first > Letter than shown start with first if type second letter then based > on > my cretria shown all list can any one help me > > Like i have name list example > > Name Moahn Mohan Kumar Mohan Doss Mathan Mathan Kumar Ashok ashok > sing Ashok Kumar Ashish Kisan > > IF I ENTER CELL D2 M then shown all my name list start with M > then i type Mo THEN THEN ONLY Star with Mo like that > > Advance thanks for all > > Regards > chandru > > -- > 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 > > >>> -- > >>> 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 > > >> -- > >> 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 > > > -- > > 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 me
Re: $$Excel-Macros$$ Pivot Table data range update
Thanks Noorain. That was very thorough. I am using excel 2010 but I can find the Name Manager. I will encourage all our Excel gurus to follow your footsteps. This little tutorial made me like this forum even more!!! Thanks a lot and best regards. On Feb 13, 11:11 pm, NOORAIN ANSARI wrote: > Dear Awal, > > Please use it > > Press CTRL F3 or go to Edit Insert Name Define > > Name your range – e.g. pivot_table_data1 > > In the refers to section type or copy and paste this formulas > > =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) > > or > > =Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6) > > [image: clip_image002] <http://www.spyjournal.biz/files/clip_image002_0.jpg> > > Assuming that your data starts in cell A1 of Sheet1. Edit accordingly > before pasting in > > What this formula does is creates an array that starts at cell a1 goes zero > cells down and zero cells right, and is x cells deep and y cells wide. x = > the count of all non blank cells in column A and y the count of all non > blank cells in row 1. Assumes that column A and row 1 are contiguous data > blocks the same length and width as the whole data set. If not use columns > and rows that are. > > Now in your pivot table right click and go to Pivot Table Wizard > > [image: clip_image004] <http://www.spyjournal.biz/files/clip_image004_1.jpg> > > Hit Back and go to the range selection > > [image: clip_image006] <http://www.spyjournal.biz/files/clip_image006_1.jpg> > > Type = and the range name = e.g. =pivot_table_data1 > > In my case my range name was called test. > > Hit Finish and it is done. > > Now whenever you paste more data into the data sheet the pivot table just > needs to be refreshed to pick it up > > Click anywhere on the pivot table and click the exclamation mark in the > toolbar. > > The instructions above assume you are using office 2003. In office 2007 the > Name Manager is a little more confusing, but can be accessed with the same > keyboard shortcut and you will need to create a new range name and then use > the same formula listed above. > > -- > Thanks & regards, > Noorain Ansari > *http://noorainansari.com/* > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > > > > > > > > On Tue, Feb 14, 2012 at 6:49 AM, Awal wrote: > > I have code to query a database and return data to the "data" sheet. > > The number of rows of data returned will vary. > > Through VBA code, how do I tell the pivot table to update the range of > > the source data (which is really to update the last row). > > > (Selecting CTRL-End, goes past where the actual data ends) > > > Thank you. > > > -- > > 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 -- 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
$$Excel-Macros$$ Pivot Table data range update
I have code to query a database and return data to the "data" sheet. The number of rows of data returned will vary. Through VBA code, how do I tell the pivot table to update the range of the source data (which is really to update the last row). (Selecting CTRL-End, goes past where the actual data ends) Thank you. -- 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$$ Synchronizing two Pivot tables.
Maries,
With just the data validation the 2 pivot tables in the file you've
attached are not synchronized; Am I missing something? Please
advise.
Thanks.
On Feb 12, 8:50 am, pawel lupinski wrote:
> Maries,
>
> Thanks
>
> regards,
>
> Pawel
>
>
> From: Maries
> To: excel-macros@googlegroups.com
> Sent: Sunday, February 12, 2012 12:58 PM
> Subject: Re: $$Excel-Macros$$ Synchronizing two Pivot tables.
>
> Hi Pawal,
>
> Instead of using Combo box & Index formula, I have used Daa validation in B4.
>
> Also I have used the following code to update the data input to pivot filter
> when worksheet change condition.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target.Address(False, False) = "B4" Then
> ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage =
> Range("B4").Value
> ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").CurrentPage =
> Range("B4").Value
> End If
> End Sub
>
> Thats it.
>
> Regards,
>
> MARIES.
>
>
>
> On Sun, Feb 12, 2012 at 3:50 PM, pawel lupinski wrote:
>
> HI MARIES,
>
>
>
>
>
>
>
>
>
>
>
> >Can you let me know how you've done this, please
>
> >Regards,
>
> >Pawel
>
> >
> > From: Maries
> >To: excel-macros@googlegroups.com
> >Sent: Sunday, February 12, 2012 5:33 AM
> >Subject: Re: $$Excel-Macros$$ Synchronizing two Pivot tables.
>
> >Hi,
> >
> >Find the attached file. I have done without INDEX.
> >
> >Regards,
> >
> >MARIES.
>
> >On Sun, Feb 12, 2012 at 7:00 AM, Lawali wrote:
>
> >Mahesh,
> >>I have resolved the problem. I've used INDEX and Macro Recorder. See
> >>attached file. But any better solution is welcome..
> >>Thanks.
>
> >>--
>
> >>"Imagine life as a game in which you are juggling some five balls in the
> >>air. You name them - Work, Family, Health, Friends and Spirit and you're
> >>keeping all of these in the Air. You will soon understand that work is a
> >>rubber ball. If you drop it, it will bounce back. But the other four Balls
> >>- Family, Health, Friends and Spirit - are made of glass. If you drop one
> >>of these; they will be irrevocably scuffed, marked, nicked, damaged or even
> >>shattered. They will never be the same. You must understand that and strive
> >>for it." 30 second Speech by Bryan Dyson (CEO of Coca Cola)
>
> >>--
> >>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
>
> >--
> >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
>
> >--
> >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.
> >
> >-
$$Excel-Macros$$ Synchronizing two Pivot tables.
I have two pivot tables with the same underlying data set but show different views to the user. I need help synchronizing the 2 Pivot tables with one report filter. Thanks in advance for your Help ! -- 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
$$Excel-Macros$$ Date picker
In a userform I have a date picker and I would like to know if there is a way to pick only "month and year". -- -- 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$$ Subtotal
Noorain,
Thank you so much for the link.I tweak the code a little bit to
accommodate it to my needs.
On Sep 13, 1:26 am, NOORAIN ANSARI wrote:
> Dear Awal,
>
> Please see below link it will help to
> u.http://www.vbaexpress.com/kb/getarticle.php?kb_id=360
>
>
>
>
>
>
>
>
>
> On Tue, Sep 13, 2011 at 5:46 AM, Awal wrote:
> > I have a macro to sort and subtotal a set of data. This data will
> > always be different amount of lines. I can set the macro to subtotal
> > only current range. If I try to include all lines so that I account
> > for when the lines of data increase, it will not allow the first line
> > to be included.
> > Here is my recorded macro:
>
> > ActiveSheet.Range("$A$20:$H$2745").AutoFilter Field:=5,
> > Criteria1:="0"
> > Range("A21:A2745").Select
> > Selection.EntireRow.Delete
> > ActiveSheet.Range("$A$20:$H$634").AutoFilter Field:=5
> > Range("A20:H634").Select
> > Range("A634").Activate
>
> > ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
> > ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
> > Key:=Range _
> > ("A21:A634"), SortOn:=xlSortOnValues, Order:=xlAscending,
> > DataOption:= _
> > xlSortNormal
> > With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
> > .Header = xlYes
> > .MatchCase = False
> > .Orientation = xlTopToBottom
> > .SortMethod = xlPinYin
> > .Apply
> > End With
> > Selection.SUBTOTAL GroupBy:=1, Function:=xlSum,
> > TotalList:=Array(5), _
> > Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> > End Sub
> > Any ideas?
> > Thanks for your help.
>
> > --
>
> > ---
> > ---
> > 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
>
> --
> Thanks & regards,
> Noorain Ansari
> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
--
--
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$$ Subtotal
I have a macro to sort and subtotal a set of data. This data will
always be different amount of lines. I can set the macro to subtotal
only current range. If I try to include all lines so that I account
for when the lines of data increase, it will not allow the first line
to be included.
Here is my recorded macro:
ActiveSheet.Range("$A$20:$H$2745").AutoFilter Field:=5,
Criteria1:="0"
Range("A21:A2745").Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$20:$H$634").AutoFilter Field:=5
Range("A20:H634").Select
Range("A634").Activate
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
Key:=Range _
("A21:A634"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.SUBTOTAL GroupBy:=1, Function:=xlSum,
TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
Any ideas?
Thanks for your help.
--
--
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
