Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Such theatrics,
Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Shows invoice to be applied, 2nd list) Shows what has been applied that are on-account. Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the on-account data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and on-account data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. Im not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
On Oct 16, 5:33 pm, pgarcia wrote:
Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Thanks JW.
Data list 1st list: GL Date Batch Name Difference Status Responsible 1-Oct-07 ML-014-011007-49 $321.15 Warning David 1-Oct-07 ML-015-011007-50 $153.01 Warning David 1-Oct-07 ML-017-011007-52 $100.00 Warning David 1-Oct-07 ML-023-011007-58 $4.85 Warning David 4-Oct-07 1MLCLSAMEX100107 $2,336.30 Warning Betsy 4-Oct-07 2MLCLSAMEX100107 $206.40 Warning Betsy 2-Oct-07 ML-006-021007-32 $150.00 Warning Tally 2-Oct-07 ML-008-021007-34 $262.78 Warning Tally 2-Oct-07 ML-006-021007-6 $63.24 Warning David 2nd list: Customer# GL Date On Account $ Days Old Responsible 13449170 10-OCT-07 $4,934.08 7 Jackie 12858370 14-DEC-06 $2,304.00 309 Jenni 222196855 26-SEP-07 $1,980.65 28 Jackie 702319450 12-DEC-06 $1,551.00 309 Jenni 252432972 28-JUL-07 $1,408.10 85 David 252350184 28-FEB-07 $1,167.07 232 Jackie 292526500 15-AUG-07 $1,085.65 63 David 43155965 05-SEP-07 $1,065.38 42 Julio 222199552 16-MAR-07 $983.12 217 David And this is what it should look like 1st tab: GL Date Batch Name Difference Status Responsible 1-Oct-07 ML-014-011007-49 $321.15 Warning David 1-Oct-07 ML-015-011007-50 $153.01 Warning David 1-Oct-07 ML-017-011007-52 $100.00 Warning David 1-Oct-07 ML-023-011007-58 $4.85 Warning David 2-Oct-07 ML-006-021007-6 $63.24 Warning David Customer# GL Date On Account $ Days Old Responsible 252432972 28-JUL-07 $1,408.10 85 David 292526500 15-AUG-07 $1,085.65 63 David 222199552 16-MAR-07 $983.12 217 David 2nd tab: GL Date Batch Name Difference Status Responsible 4-Oct-07 1MLCLSAMEX100107 $2,336.30 Warning Betsy 4-Oct-07 2MLCLSAMEX100107 $206.40 Warning Betsy Customer# GL Date On Account $ Days Old Responsible no data tabs 3,4,5 ect. Hope that make sence. Thanks "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Any luck yet?
"JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
pgarcia,
How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
When click on a "Button", please. But I can creat that. Would this be a VB
code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Assuming that your sheets are named as follows: "Invoice", "On
Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Hello,
The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
What is the value of strEmployee? Put the cursor on strEmployee when
it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''*'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''*'''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
strEmployee = "ML-500-011107-30"
"Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''*'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''*'''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
I think that your data layout must be different from the tables you
have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Hello again. It stoped in the same spot, but this time strEmployee = "Tally".
Aslo, the first part of the does not see to be working, the pasted header part, but that's alright. It is picking up the infomation and pasteting it, but it also paste some part of the VB code. Wierd. FYI, it picked up all of Daves infomation by the way. Do you want me to email the spread sheet to you? "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Ok, I formated "Tally" to be the same as the rest of the list. For some
reson, that worked. But now it hangs up at the following line: Sheets("David").Select Range(Cells(i_david + 2, 1)).Select <---- ActiveSheet.Paste Aslo, I just added Selection.Copy to the header postion and that did the trick. Thanks! "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''**'''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
It's good that it worked out for you. When you mean select name, do
you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account")..Cells(i, 1) ... read more - Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select
a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account")..Cells(i, 1) ... read more »- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
On Nov 12, 10:23 am, pgarcia
wrote: Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select ... read more If you have a lot of stations to deal with then I might take a different approach than the VBA that is hard coded to each employee. I just did one of these for you, so I'll describe how I set it up using only formulas and lookups rather than VBA. 'Data' Tab Row 1 is your headings, A2:E10 is the data you provided. 'Result' tab A1 contains the name of the employee to lookup (David, for example.) A2: =IF(ISERROR(MATCH($A$1,OFFSET(Data!$E$2,B1,0,100,1 ),0)),"",MATCH($A $1,OFFSET(Data!$E$2,B1,0,100,1),0)) B2: =IF(ISERROR(+B1+A2),"",+B1+A2) C2: =IF(ISERROR(INDEX(Data!A$2:A$10,$B2)),"",INDEX(Dat a!A$2:A$10,$B2)) Copy C2 across to column G and format each column appropriately. A2 find the first instance of the name AFTER the last time it found the name. (The OFFSET does that). Copy-paste row 2 down to whatever the maximum number of rows you are likely to have for a single employee in a day. The ISERROR portions will find when the formula "stops finding" David and prevent it from showing #NAs or #VALUEs. As for your last question, I think you want to have a pulldown with the list of stations and the option to override it. Again, it CAN be accomplished w/o VBA, though it would be more elegant and foolproof with it. Depending on who wants to use it, make your decision accordingly. Set up a pulldown menu with: Input range = I2:I29 (or whereever you want it and as large as you need) Cell link of I1 Put an "override" cell in K1 (where they can type in a name/site instead of using the pulldown). In A1 (where "David" was typed in before) put: =IF(K2="",INDEX(I2:I29,I1),IF(ISNA(MATCH(K2,I2:I29 ,0)),"Station Not Found--use the Menu or try again",K2)) A small amount of code assigned to the pulldown could be used to wipe out the override cell when a selection is made with the dropdown. I think this accomplishes what you're trying to do. I'd be happy to send along a copy of this in a workbook if you email me. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Thank for your effort, but it did not work. The new data does not have
employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily bases. What Faisal has given me, I thought it could be modified to lookup the station code (I'm thinking, yes, an input cell) and return the 22 lines of data, where LAX may be repeated 28 to 35 times (in todays data LAX is listed 282 times). Since it changes daily, I don't want to worry about copying a formula down to a certain cell or row. Thank you " wrote: On Nov 12, 10:23 am, pgarcia wrote: Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select ... read more » If you have a lot of stations to deal with then I might take a different approach than the VBA that is hard coded to each employee. I just did one of these for you, so I'll describe how I set it up using only formulas and lookups rather than VBA. 'Data' Tab Row 1 is your headings, A2:E10 is the data you provided. 'Result' tab A1 contains the name of the employee to lookup (David, for example.) A2: =IF(ISERROR(MATCH($A$1,OFFSET(Data!$E$2,B1,0,100,1 ),0)),"",MATCH($A $1,OFFSET(Data!$E$2,B1,0,100,1),0)) B2: =IF(ISERROR(+B1+A2),"",+B1+A2) C2: =IF(ISERROR(INDEX(Data!A$2:A$10,$B2)),"",INDEX(Dat a!A$2:A$10,$B2)) Copy C2 across to column G and format each column appropriately. A2 find the first instance of the name AFTER the last time it found the name. (The OFFSET does that). Copy-paste row 2 down to whatever the maximum number of rows you are likely to have for a single employee in a day. The ISERROR portions will find when the formula "stops finding" David and prevent it from showing #NAs or #VALUEs. As for your last question, I think you want to have a pulldown with the list of stations and the option to override it. Again, it CAN be accomplished w/o VBA, though it would be more elegant and foolproof with it. Depending on who wants to use it, make your decision accordingly. Set up a pulldown menu with: Input range = I2:I29 (or whereever you want it and as large as you need) Cell link of I1 Put an "override" cell in K1 (where they can type in a name/site instead of using the pulldown). In A1 (where "David" was typed in before) put: =IF(K2="",INDEX(I2:I29,I1),IF(ISNA(MATCH(K2,I2:I29 ,0)),"Station Not Found--use the Menu or try again",K2)) |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Right, what I gave you was an example of the approach, not The
Solution. You can make the "result" formulas go down 2000 rows or more if you want (whatever the max might ever be), and the results will only display as far as there is data for that station (which I assumed you were using "name" as a surrogate for). As far as the amount of data changing daily on the Data tab, you can make the match formulas look up the whole way down the column from 2 through 65536. Or look up how to create dynamic named ranges and use those to account for the varied amount of input data. On Nov 12, 2:13 pm, pgarcia wrote: Thank for your effort, but it did not work. The new data does not have employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily bases. What Faisal has given me, I thought it could be modified to lookup the station code (I'm thinking, yes, an input cell) and return the 22 lines of data, where LAX may be repeated 28 to 35 times (in today's data LAX is listed 282 times). Since it changes daily, I don't want to worry about copying a formula down to a certain cell or row. Thank you " wrote: On Nov 12, 10:23 am, pgarcia wrote: Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ... read more |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Could you email me the sample? I can't seem to get my to work or should I
send it to you? " wrote: Right, what I gave you was an example of the approach, not The Solution. You can make the "result" formulas go down 2000 rows or more if you want (whatever the max might ever be), and the results will only display as far as there is data for that station (which I assumed you were using "name" as a surrogate for). As far as the amount of data changing daily on the Data tab, you can make the match formulas look up the whole way down the column from 2 through 65536. Or look up how to create dynamic named ranges and use those to account for the varied amount of input data. On Nov 12, 2:13 pm, pgarcia wrote: Thank for your effort, but it did not work. The new data does not have employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily bases. What Faisal has given me, I thought it could be modified to lookup the station code (I'm thinking, yes, an input cell) and return the 22 lines of data, where LAX may be repeated 28 to 35 times (in today's data LAX is listed 282 times). Since it changes daily, I don't want to worry about copying a formula down to a certain cell or row. Thank you " wrote: On Nov 12, 10:23 am, pgarcia wrote: Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ... read more » |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
You can shoot me an email on my account and I'll forward what I've
done. Your email in the NG didn't work for me. On Nov 13, 9:56 am, pgarcia wrote: Could you email me the sample? I can't seem to get my to work or should I send it to you? " wrote: Right, what I gave you was an example of the approach, not The Solution. You can make the "result" formulas go down 2000 rows or more if you want (whatever the max might ever be), and the results will only display as far as there is data for that station (which I assumed you were using "name" as a surrogate for). As far as the amount of data changing daily on the Data tab, you can make the match formulas look up the whole way down the column from 2 through 65536. Or look up how to create dynamic named ranges and use those to account for the varied amount of input data. On Nov 12, 2:13 pm, pgarcia wrote: Thank for your effort, but it did not work. The new data does not have employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily bases. What Faisal has given me, I thought it could be modified to lookup the station code (I'm thinking, yes, an input cell) and return the 22 lines of data, where LAX may be repeated 28 to 35 times (in today's data LAX is listed 282 times). Since it changes daily, I don't want to worry about copying a formula down to a certain cell or row. Thank you " wrote: On Nov 12, 10:23 am, pgarcia wrote: Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select a station from a drop down menu or have the user input it. It they input the station code then there should be message indication that there was not match. Thanks "Faisal..." wrote: It's good that it worked out for you. When you mean select name, do you mean to select a name and do the (same) operation (as in this code) only for this name? You will have to trim the code down and maybe use a userform. Faisal... On 9 Nov, 19:03, pgarcia wrote: Hello again. I think I figered this out. I was a formating issue. This code is very sensitve. There was a stange space in font of the name. It's fixed and the code runs great. I did howeve modifyed it a bit. (see comment below the VB code) Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer Dim strEmployee As String ' ' ' ' Update the Invoice data on the Employe sheets i_david = 19 i_betsy = 19 i_tally = 19 i_julio = 19 i_jackie = 19 i_jenni = 19 i = 2 While Sheets("Invoice").Cells(i, 1) < "" Select Case Sheets("Invoice").Cells(i, 8) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("Invoice").Cells(i, 8).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7) i = i + 1 Wend ' ' ' ' Update the On Account data on the Employe sheets i_david = 79 i_betsy = 38 i_tally = 59 i_julio = 59 i_jackie = 79 i_jenni = 47 i = 2 While Sheets("On Account").Cells(i, 1) < "" Select Case Sheets("On Account").Cells(i, 10) Case "David" ii = i_david i_david = i_david + 1 Case "Betsy" ii = i_betsy i_betsy = i_betsy + 1 Case "Tally" ii = i_tally i_tally = i_tally + 1 Case "Julio" ii = i_julio i_julio = i_julio + 1 Case "Jackie" ii = i_jackie i_jackie = i_jackie + 1 Case "Jenni" ii = i_jenni i_jenni = i_jenni + 1 End Select strEmployee = Sheets("On Account").Cells(i, 10).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5) Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6) Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7) Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8) Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9) i = i + 1 Wend End Sub Thanks, that helped a lot. Question, could this code be modify to select a name? Meaning.. With a drop down list or inputing the name in a cell, could this return the same info. This would be for another project. "Faisal..." wrote: I think that your data layout must be different from the tables you have posted. I assumed that employee name would be in the 5th column. in the line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Update the 5 to another figure referring to the column number of where the employee name is stored. Faisal ... On Nov 8, 8:10 pm, pgarcia wrote: strEmployee = "ML-500-011107-30" "Faisal..." wrote: What is the value of strEmployee? Put the cursor on strEmployee when it stops. On Nov 8, 3:47 pm, pgarcia wrote: Hello, The VB code stops at the following line: strEmployee = Sheets("Invoice").Cells(i, 5).Value Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1) <---- Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2) Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3) Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4) Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5) Thanks "Faisal..." wrote: Assuming that your sheets are named as follows: "Invoice", "On Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***'''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select ... read more |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Max or Ron can answer this they are Gods! | Excel Worksheet Functions | |||
I'm not sure what you'd call it, but is it possible to do this? | Excel Discussion (Misc queries) | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
DLL Call | Excel Programming | |||
call sub | Excel Programming |