Private Sub bezb_Click()
Dim narzutr As String
Dim narzutm As String
Dim wName As String
Dim pName As String
Dim rsName As String
Dim cell As Range
Dim fname As String, wb As Workbook, project As String
Dim sh As Worksheet
narzutr = 1.15
pName = Range("C3").Value
wName = "Faktury" + Left(pName, 5)
rsName = "Godziny" + Left(pName, 5)
fname = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
Set wb = Workbooks.Open(fname)
For Each sh In wb.Worksheets
If sh.Name = "Wycena z dokładnymi materiałami" Then
sh.Visible = xlSheetVisible
sh.Copy After:=ThisWorkbook.Sheets(Sheets.Count): Exit For
End If
Next sh
Application.DisplayAlerts = False
wb.Close True
Application.DisplayAlerts = True
ActiveSheet.Columns("C:C").Select
Set cell = Selection.Find(what:="RAZEM m2*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("Y43").Value = 0
Else
Sheets(rsName).Range("Y43").Value = cell.Offset(0, 1).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="POMIARY", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S11").Value = 0
Sheets(rsName).Range("T11").Value = 0
Else
Sheets(rsName).Range("S11").Value = cell.Offset(0, 4).Value
Sheets(rsName).Range("T11").Value = cell.Offset(0, 13).Value
Sheets(rsName).Range("V11").Value = cell.Offset(0, 6).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="ORGANIZACJA*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S12").Value = 0
Sheets(rsName).Range("T12").Value = 0
Else
Sheets(rsName).Range("S12").Value = cell.Offset(0, 4).Value
Sheets(rsName).Range("T12").Value = cell.Offset(0, 13).Value
Sheets(rsName).Range("V12").Value = cell.Offset(0, 6).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="PLANY PRODUKCYJNE*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S13").Value = 0
Sheets(rsName).Range("T13").Value = 0
Else
Sheets(rsName).Range("S13").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T13").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V13").Value = cell.Offset(0, 3).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="PRACA CNC*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S14").Value = 0
Sheets(rsName).Range("T14").Value = 0
Else
Sheets(rsName).Range("S14").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T14").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V14").Value = cell.Offset(0, 3).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="PRACA WARSZTAT*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S15").Value = 0
Sheets(rsName).Range("T15").Value = 0
Else
Sheets(rsName).Range("S15").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T15").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V15").Value = cell.Offset(0, 3).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="ROZŁOŻENIE I ZŁOŻENIE DO MALOWANIA*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S16").Value = 0
Sheets(rsName).Range("T16").Value = 0
Else
Sheets(rsName).Range("S16").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T16").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V16").Value = cell.Offset(0, 3).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="ZABEZPIECZENIE MEBLA*", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S17").Value = 0
Sheets(rsName).Range("T17").Value = 0
Else
Sheets(rsName).Range("S17").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T17").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V17").Value = cell.Offset(0, 3).Value
End If
ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="MONTAŻ", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If cell Is Nothing Then
Sheets(rsName).Range("S18").Value = 0
Sheets(rsName).Range("T18").Value = 0
Else
Sheets(rsName).Range("S18").Value = cell.Offset(0, 1).Value
Sheets(rsName).Range("T18").Value = cell.Offset(0, 9).Value
Sheets(rsName).Range("V18").Value = cell.Offset(0, 3).Value
End If
Sheets("Wycena z dokładnymi materiałami").Activate
Sheets(rsName).Activate
ActiveSheet.Range("T159").FormulaR1C1 = "=SUMIF(C[-16],""R O B O C I Z N A*"",C[-4])"
ActiveSheet.Range("T160").FormulaR1C1 = "=SUMIF(C[-16],""TRANSPORT*"",C[-4])"
ActiveSheet.Range("T161").FormulaR1C1 = "=R[-2]C-R[-1]C"
Sheets(LastSheet).Range("T19").Value = Range("T161").Value
ActiveSheet.Range("X2").FormulaR1C1 = "=SUMIF(C[-20],""CENA ZA m2 LAKIER*"",C[-8])"
ActiveSheet.Range("X3").FormulaR1C1 = "=SUMIF(C[-20],""CENA ZA m2 LAKIER*"",C[-16])"
Sheets(LastSheet).Range("Q21").Value = Range("X3").Value
Sheets(LastSheet).Range("R21").Value = Range("X2").Value
Sheets(LastSheet).Range("T36").Value = Sheets(wName).Range("J31").Value
Sheets(LastSheet).Range("W36").Value = Sheets(wName).Range("M10").Value
ActiveSheet.Range("X4").FormulaR1C1 = "=SUMIF(C[-20],""CENA ZA m2 PŁYTY*"",C[-8])"
ActiveSheet.Range("X5").FormulaR1C1 = "=SUMIF(C[-20],""CENA ZA PCV*"",C[-8])"
ActiveSheet.Range("X6").FormulaR1C1 = "=SUMIF(C[-20],""A K C E S O R I A*"",C[-8])"
ActiveSheet.Range("X8").FormulaR1C1 = "=SUMIF(C[-20],""DODATEK:*"",C[-8])"
ActiveSheet.Range("X9").FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
ActiveSheet.Range("X12").FormulaR1C1 = "=SUMIF(C[-20],""TRANSPORT*"",C[-8])"
Sheets(LastSheet).Range("Q37").Value = Range("X12").Value
Sheets(LastSheet).Range("R37").Value = Sheets(wName).Range("M13").Value
Sheets(LastSheet).Range("S21").FormulaR1C1 = "=RC[-2]*2"
Sheets(LastSheet).Range("U21") = "Bezbarwny"
Sheets(LastSheet).Range("Q22").Value = Range("X9").Value
ActiveSheet.Move After:=Sheets(Sheets.Count)
Sheets(rsName).Activate
Range("Q36:S25").NumberFormat = "#,##0.00 $"
Range("Y26:Y42").NumberFormat = "#,##0.00 $"
Range("R23").NumberFormat = "#,##0.00 $"
Range("T23").NumberFormat = "#,##0.00 $"
Range("Y21").NumberFormat = "#,##0.00 $"
Range("W21").NumberFormat = "#,##0.00 $"
Range("R21,T21,S26,Q37,R37").NumberFormat = "#,##0.00 $"
Range("Y31").NumberFormat = "#,##0.00 $"
Range("Y43").NumberFormat = "0,00"
Range("Q22").NumberFormat = ";;;"
Range("T36").NumberFormat = ";;;"
End Sub