401k Spreadsheets help

[Kevin Porter]

Hello,

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

WorksheetB (http://www.specialty-risk.com/Spreadsheet Help/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/Spreadsheet Help/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

 

[Joel]

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

 

[Joel]

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

 

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

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

 

[Dick Kusleika]

Hello,

WorksheetA (http://www.specialty-risk.com/Spreadsheet Help/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