Solved: Pull data from other sheets

[Yukio]

Hello everyone,

I want to make a workbook where I can monitor how many days my employees have been on sick leave and if they brought the appropriate documents from the medic ("Attest?"-column).
In the "Master"-sheet I wish to see all employees' status and how many days he/she had a sick leave in total.
Adding the formulas for every new employee is tiresome, because we get about 5-10 new employees every month.
I wish to automate everything with VBA - tried multiple times, but sadly didn't manage to make it work...

Task: When I add a new worksheet with the title format "name, surname", the contents of the cell E5 should be shown in the "Master"-sheet in the column E and the total sick days in cell F40 should be shown in column D.

Thanks in advance,

Robert

[joms]

to get data from another sheet, you can use this code snippet:


Sub xval()
'Sheet with the data
xSheetData = ThisWorkbook.Sheets("sheet2").Range("B1").Value

'Get data from sheet2 on Cell B1
'And place the data on the active sheet on B1 also
ActiveSheet.Cells(1, 2).Value = xSheetData

End Sub

[lotuxel]

Option Explicit
Sub update_all()
Dim wb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim row As Long
Dim row_l As Long
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Master")
ws.Activate
row = 4
row_l = ws.Cells(Rows.Count, "A").End(xlUp).row
With ws
'clear all data first
.Rows(row & ":" & row_l).ClearContents

For Each sh In wb.Worksheets
If sh.Name <> ws.Name Then
.Cells(row, 1) = Left(sh.Name, InStr(1, sh.Name, ",") - 1)
.Cells(row, 2) = Trim(Right(sh.Name, Len(sh.Name) - InStr(1, sh.Name, ",")))
.Cells(row, 3) = sh.Range("C8").Value
.Cells(row, 4).Formula = "= '" & sh.Name & "'!F$40"
.Cells(row, 5).Formula = "='" & sh.Name & "'!$E$5"
.Cells(row, 6) = 1
.Cells(row, 7) = sh.Range("e2").Value
row = row + 1

End If
Next

End With

End Sub


In master file didn't know what should put in column i

[Yukio]

Awesome, thank you! :-)

[joms]

Hi Yukio, if this thread is solved. Please mark it solved.

Cheers!! :)

[lotuxel]

Hi Yukio,
U welcome!,
:friends:
lotuxel

[lotuxel]

Hi Yukio,
Just add some function for hyperlink
1). in master sheet click the column A employee name will show his/her sheet
2). from that sheet click the A7 will come back to Master sheet
hope you enjoy this implements.
lotuxel


Sub update_all()
Dim wb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim row As Long
Dim row_l As Long
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Master")
ws.Activate
row = 4
row_l = ws.Cells(Rows.Count, "A").End(xlUp).row
With ws
'clear all data first
.Rows(row & ":" & row_l).ClearContents

For Each sh In wb.Worksheets
If sh.Name <> ws.Name Then
.Cells(row, 1) = Left(sh.Name, InStr(1, sh.Name, ",") - 1)
.Cells(row, 2) = Trim(Right(sh.Name, Len(sh.Name) - InStr(1, sh.Name, ",")))
.Cells(row, 3) = sh.Range("C8").Value
.Cells(row, 4).Formula = "= '" & sh.Name & "'!F$40"
.Cells(row, 5).Formula = "='" & sh.Name & "'!$E$5"
.Cells(row, 6) = 1
.Cells(row, 7) = sh.Range("e2").Value
.Cells(row, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & row), Address:="", _
SubAddress:="'" & sh.Name & "'!A1", _
ScreenTip:="Click me to VIEW - [ " & sh.Name & " ] Sheet"
With Selection
.Font.ColorIndex = xlAutomatic
.Font.Underline = xlUnderlineStyleNone
.Font.Name = "Arial"
.Font.Size = 8
End With

row = row + 1

End If
Next

End With
Application.ScreenUpdating = False
For Each sh In wb.Worksheets

If sh.Name <> ws.Name Then
sh.Activate
ActiveSheet.Hyperlinks.Add Anchor:=Range("A7"), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
ScreenTip:="Click me to VIEW - [ " & ws.Name & " ] Sheet"

End If
Next
ws.Activate
Set ws = Nothing
Set wb = Nothing

End Sub