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 -
|