VBA: Add Specific Cell Values to Table in Existing Workbook?? (HELP)

[cotufa-ssdd]

Hey guyz,

I am having a little trouble and would like some help.

I have a template worksheet in excel with payroll information.

This template is used for all employees.

Now I want to create a button that copies certain cell values (Name, date, total, etc) and adds them to a table in a separate existing workbook. In other words it would be a summary of the payroll.

I want it to add the information in the next empty row in the table, so it would keep old info.


Anyone have anything for me??

Thank you!

cotufa-ssdd Reviewed by cotufa-ssdd on 15th Dec 2011. VBA: Add Specific Cell Values to Table in Existing Workbook?? (HELP) Hey guyz, I am having a little trouble and would like some help. I have a template worksheet in excel with payroll information. This template is used for all employees. Now I want to create a button that copies certain cell values (Name, date, total, etc) and adds them to a table in a separate existing workbook. In other words it would be a summary of the payroll. Rating: 5

[Rox]

why not use pivot table? its pretty simple

if your requirement IS what you asked above - writing macros would do the job, im not aware of it but yes, ive seen it being used in my company.

[cotufa-ssdd]

figured it out, tought i would share it...

Option Explicit
Sub TransferData()
    Dim wkb As Workbook, wks As Worksheet, LastRow As Long
    Dim FilePath As String, FileName As String
    Dim ws As Worksheet, blnOpened As Boolean
    FilePath = "C:\test" 'change path here
    FileName = "Book2.xls" 'change name here
    Call ToggleEvents(False)
    Set ws = ThisWorkbook.Sheets("Sheet1") 'source sheet
    If WbOpen(FileName) = True Then
        Set wkb = Workbooks(FileName)
        blnOpened = False
    Else
        If Right(FilePath, 1) <> Application.PathSeparator Then
            FilePath = FilePath & Application.PathSeparator
        End If
        Set wkb = Workbooks.Open(FilePath & FileName)
        blnOpened = True
    End If
    Set wks = wkb.Sheets("Sheet1") 'destination sheet name
    LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
    wks.Cells(LastRow, "A").Value = ws.Cells(1, "B").Value
    wks.Cells(LastRow, "B").Value = ws.Cells(4, "B").Value
    wks.Cells(LastRow, "C").Value = ws.Cells(7, "B").Value
    wks.Cells(LastRow, "D").Value = ws.Cells(7, "E").Value
    If blnOpened = True Then
        wkb.Close SaveChanges:=True
    End If
    If MsgBox("Clear values?", vbYesNo, "CLEAR?") = vbYes Then
        Call ClearData
    End If
    Call ToggleEvents(True)
End Sub
Sub ClearData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("B1").ClearContents 'Name
    ws.Range("B4").ClearContents 'Address
    ws.Range("B7").ClearContents 'Age
    ws.Range("E7").ClearContents 'Sex
End Sub
Sub ToggleEvents(blnState As Boolean)
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub
Function WbOpen(wbName As String) As Boolean
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

thnx for opinion Rox...