401k Spreadsheets help
 

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

401k Spreadsheets help
 

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

401k Spreadsheets help
 

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

401k Spreadsheets help
 

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

401k Spreadsheets help
 

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

401k Spreadsheets help
 

On Mon, 24 Mar 2008 08:08:04 -0700, Kevin Porter
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