Hello,

WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
is the worksheet created by QuickBooks.

WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls)
is a worksheet I use to keep track of monthly info, I also update the SS#
and names field manually for now.

WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls)
is the worksheet required for upload to our 401k company.

I would like to match a name/SS# on WorksheetA with the name on WorksheetB,
update the fields on WorksheetB, then copy that info to WorksheetC. I can
move info from WorksheetB to WorksheetC, I need help going from WorksheetA to
WorksheetB.

Please any help would be appreciated. Let me know if their are any other
questions.

Thanks,

Kevin Porter

It woulde be a lot siomplier if you had SS# on worksheet A. Here is the
start of the code.


Sub move_data()


Const Grss_Comp_Col = "F"
Const Grss_Comp_Row = 20

With Sheets("WorksheetA")
StartCol = .Range("E1").Column
ColCount = StartCol
Do While .Cells(1, ColCount) <> "TOTAL"
MyName = .Cells(1, ColCount)
LastName = Trim(Left(MyName, InStr(MyName, ",") - 1))
FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1))
If InStr(FirstName, " ") > 0 Then
MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1))
FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1))
Else
MiddleName = ""
End If

Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2)
With Sheets("WorksheetB")
StartRow = 7
RowCount = StartRow
Found = False
Do While .Range("A" & RowCount) <> ""
FirstN = .Range("C" & RowCount)
MiddleN = .Range("D" & RowCount)
LastN = .Range("B" & RowCount)
If FirstName = FirstN And _
MiddleName = MiddleN And _
LastName = LastN Then

.Range("F" & RowCount) = Gross
Found = True
Exit Do
End If
Loop
If Found = False Then
MsgBox ("Did not find: " & FirstName & MiddleName & LastName)
End If
End With

ColCount = ColCount + 4
Loop

End With

End Sub


"Kevin Porter" wrote:

> Hello,
>
> WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
> is the worksheet created by QuickBooks.
>
> WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls)
> is a worksheet I use to keep track of monthly info, I also update the SS#
> and names field manually for now.
>
> WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls)
> is the worksheet required for upload to our 401k company.
>
> I would like to match a name/SS# on WorksheetA with the name on WorksheetB,
> update the fields on WorksheetB, then copy that info to WorksheetC. I can
> move info from WorksheetB to WorksheetC, I need help going from WorksheetA to
> WorksheetB.
>
> Please any help would be appreciated. Let me know if their are any other
> questions.
>
> Thanks,
>
> Kevin Porter

I left out one line that increments rowcount. see code below

Sub move_data()


Const Grss_Comp_Col = "F"
Const Grss_Comp_Row = 20

With Sheets("WorksheetA")
StartCol = .Range("E1").Column
ColCount = StartCol
Do While .Cells(1, ColCount) <> "TOTAL"
MyName = .Cells(1, ColCount)
LastName = Trim(Left(MyName, InStr(MyName, ",") - 1))
FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1))
If InStr(FirstName, " ") > 0 Then
MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1))
FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1))
Else
MiddleName = ""
End If

Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2)
With Sheets("WorksheetB")
StartRow = 7
RowCount = StartRow
Found = False
Do While .Range("A" & RowCount) <> ""
FirstN = .Range("C" & RowCount)
MiddleN = .Range("D" & RowCount)
LastN = .Range("B" & RowCount)
If FirstName = FirstN And _
MiddleName = MiddleN And _
LastName = LastN Then

.Range("F" & RowCount) = Gross
Found = True
Exit Do
End If
RowCount = RowCount + 1
Loop
If Found = False Then
MsgBox ("Did not find: " & FirstName & MiddleName & LastName)
End If
End With

ColCount = ColCount + 4
Loop

End With

End Sub


"Joel" wrote:

> It woulde be a lot siomplier if you had SS# on worksheet A. Here is the
> start of the code.
>
>
> Sub move_data()
>
>
> Const Grss_Comp_Col = "F"
> Const Grss_Comp_Row = 20
>
> With Sheets("WorksheetA")
> StartCol = .Range("E1").Column
> ColCount = StartCol
> Do While .Cells(1, ColCount) <> "TOTAL"
> MyName = .Cells(1, ColCount)
> LastName = Trim(Left(MyName, InStr(MyName, ",") - 1))
> FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1))
> If InStr(FirstName, " ") > 0 Then
> MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1))
> FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1))
> Else
> MiddleName = ""
> End If
>
> Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2)
> With Sheets("WorksheetB")
> StartRow = 7
> RowCount = StartRow
> Found = False
> Do While .Range("A" & RowCount) <> ""
> FirstN = .Range("C" & RowCount)
> MiddleN = .Range("D" & RowCount)
> LastN = .Range("B" & RowCount)
> If FirstName = FirstN And _
> MiddleName = MiddleN And _
> LastName = LastN Then
>
> .Range("F" & RowCount) = Gross
> Found = True
> Exit Do
> End If
> Loop
> If Found = False Then
> MsgBox ("Did not find: " & FirstName & MiddleName & LastName)
> End If
> End With
>
> ColCount = ColCount + 4
> Loop
>
> End With
>
> End Sub
>
>
> "Kevin Porter" wrote:
>
> > Hello,
> >
> > WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
> > is the worksheet created by QuickBooks.
> >
> > WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls)
> > is a worksheet I use to keep track of monthly info, I also update the SS#
> > and names field manually for now.
> >
> > WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls)
> > is the worksheet required for upload to our 401k company.
> >
> > I would like to match a name/SS# on WorksheetA with the name on WorksheetB,
> > update the fields on WorksheetB, then copy that info to WorksheetC. I can
> > move info from WorksheetB to WorksheetC, I need help going from WorksheetA to
> > WorksheetB.
> >
> > Please any help would be appreciated. Let me know if their are any other
> > questions.
> >
> > Thanks,
> >
> > Kevin Porter

Yes, things would be a lot easier if I could get the SS# on the first one.
It is a file created by Quickbooks, whose report creation leaves something to
be desired. All the info I want is in QB, but I cannot create a report that
shows me that info in those rows. I would have to create several reports
then combine them. (Shaking fist angrily at QuickBook creators).

Thanks for the help here. I am getting ready to plug this in, adjust and
see where I end up.

"Joel" wrote:

> It woulde be a lot siomplier if you had SS# on worksheet A. Here is the
> start of the code.
>
>
> Sub move_data()
>
>
> Const Grss_Comp_Col = "F"
> Const Grss_Comp_Row = 20
>
> With Sheets("WorksheetA")
> StartCol = .Range("E1").Column
> ColCount = StartCol
> Do While .Cells(1, ColCount) <> "TOTAL"
> MyName = .Cells(1, ColCount)
> LastName = Trim(Left(MyName, InStr(MyName, ",") - 1))
> FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1))
> If InStr(FirstName, " ") > 0 Then
> MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1))
> FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1))
> Else
> MiddleName = ""
> End If
>
> Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2)
> With Sheets("WorksheetB")
> StartRow = 7
> RowCount = StartRow
> Found = False
> Do While .Range("A" & RowCount) <> ""
> FirstN = .Range("C" & RowCount)
> MiddleN = .Range("D" & RowCount)
> LastN = .Range("B" & RowCount)
> If FirstName = FirstN And _
> MiddleName = MiddleN And _
> LastName = LastN Then
>
> .Range("F" & RowCount) = Gross
> Found = True
> Exit Do
> End If
> Loop
> If Found = False Then
> MsgBox ("Did not find: " & FirstName & MiddleName & LastName)
> End If
> End With
>
> ColCount = ColCount + 4
> Loop
>
> End With
>
> End Sub
>
>
> "Kevin Porter" wrote:
>
> > Hello,
> >
> > WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
> > is the worksheet created by QuickBooks.
> >
> > WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls)
> > is a worksheet I use to keep track of monthly info, I also update the SS#
> > and names field manually for now.
> >
> > WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls)
> > is the worksheet required for upload to our 401k company.
> >
> > I would like to match a name/SS# on WorksheetA with the name on WorksheetB,
> > update the fields on WorksheetB, then copy that info to WorksheetC. I can
> > move info from WorksheetB to WorksheetC, I need help going from WorksheetA to
> > WorksheetB.
> >
> > Please any help would be appreciated. Let me know if their are any other
> > questions.
> >
> > Thanks,
> >
> > Kevin Porter

I only did Gross Income. Left the rest of the code for you to write. I
tested this code with the sheets you posted on the webpages and works
perfrectly. You may need to change the worksheet names I used (worksheetA
and WorksheetB).



"Kevin Porter" wrote:

> Yes, things would be a lot easier if I could get the SS# on the first one.
> It is a file created by Quickbooks, whose report creation leaves something to
> be desired. All the info I want is in QB, but I cannot create a report that
> shows me that info in those rows. I would have to create several reports
> then combine them. (Shaking fist angrily at QuickBook creators).
>
> Thanks for the help here. I am getting ready to plug this in, adjust and
> see where I end up.
>
> "Joel" wrote:
>
> > It woulde be a lot siomplier if you had SS# on worksheet A. Here is the
> > start of the code.
> >
> >
> > Sub move_data()
> >
> >
> > Const Grss_Comp_Col = "F"
> > Const Grss_Comp_Row = 20
> >
> > With Sheets("WorksheetA")
> > StartCol = .Range("E1").Column
> > ColCount = StartCol
> > Do While .Cells(1, ColCount) <> "TOTAL"
> > MyName = .Cells(1, ColCount)
> > LastName = Trim(Left(MyName, InStr(MyName, ",") - 1))
> > FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1))
> > If InStr(FirstName, " ") > 0 Then
> > MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1))
> > FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1))
> > Else
> > MiddleName = ""
> > End If
> >
> > Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2)
> > With Sheets("WorksheetB")
> > StartRow = 7
> > RowCount = StartRow
> > Found = False
> > Do While .Range("A" & RowCount) <> ""
> > FirstN = .Range("C" & RowCount)
> > MiddleN = .Range("D" & RowCount)
> > LastN = .Range("B" & RowCount)
> > If FirstName = FirstN And _
> > MiddleName = MiddleN And _
> > LastName = LastN Then
> >
> > .Range("F" & RowCount) = Gross
> > Found = True
> > Exit Do
> > End If
> > Loop
> > If Found = False Then
> > MsgBox ("Did not find: " & FirstName & MiddleName & LastName)
> > End If
> > End With
> >
> > ColCount = ColCount + 4
> > Loop
> >
> > End With
> >
> > End Sub
> >
> >
> > "Kevin Porter" wrote:
> >
> > > Hello,
> > >
> > > WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
> > > is the worksheet created by QuickBooks.
> > >
> > > WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls)
> > > is a worksheet I use to keep track of monthly info, I also update the SS#
> > > and names field manually for now.
> > >
> > > WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls)
> > > is the worksheet required for upload to our 401k company.
> > >
> > > I would like to match a name/SS# on WorksheetA with the name on WorksheetB,
> > > update the fields on WorksheetB, then copy that info to WorksheetC. I can
> > > move info from WorksheetB to WorksheetC, I need help going from WorksheetA to
> > > WorksheetB.
> > >
> > > Please any help would be appreciated. Let me know if their are any other
> > > questions.
> > >
> > > Thanks,
> > >
> > > Kevin Porter

On Mon, 24 Mar 2008 08:08:04 -0700, Kevin Porter
<(E-Mail Removed)> wrote:

>Hello,
>
>WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls)
>is the worksheet created by QuickBooks.

Kevin: I didn't look at your sheets (it looks like Joel has you covered),
but I thought I'd share some information with you. I use QB Enterprise 8.0
and under Reports > Employees and Payroll there is a Summarize Payroll Data
in Excel option. In the resulting workbook, there is a Data worksheet (you
may have to unhide) with all the data in a nice table format for you to
use. It may help you eliminate some steps.

The downside is that it's horribly slow transferring data from QB to Excel -
slower than just dumping a report into Excel. I also don't know if that
option is available in all QB versions, so you may not have it
--
Dick