Hello I recorded a macro on my personal PC but does not work properly on my work computer...
What is wrong and the main issue is the VLOOKUP that is not populating the information...
What is wrong and the main issue is the VLOOKUP that is not populating the information...
Code:
Sub paso1()
'
' paso1 Macro
'
'
file_1 = Sheets("START").Range("H12").Value
file_2 = Sheets("START").Range("H13").Value
base = ActiveWorkbook.Name
Rows("1:2").Select
Selection.ClearContents
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("G").Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Windows(file_2).Activate
Rows("1:1").Select
Selection.Copy
Windows(base).Activate
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
tam = Range(Selection, Selection.End(xlDown)).Count + 1
Application.CutCopyMode = False
Windows(base).Activate
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & file_1 & "]Sheet1'!C1:C3,2,0)"
Selection.AutoFill Destination:=Range("D2:D" & tam)
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[" & file_1 & "]Sheet1'!C1:C4,3,0)"
Selection.AutoFill Destination:=Range("E2:E" & tam)
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[" & file_1 & "]Sheet1'!C1:C4,4,0)"
Selection.AutoFill Destination:=Range("F2:F" & tam)
Range("D2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Windows(file_2).Activate
Range("K2:P2").Select
Selection.Copy
Windows(base).Activate
Range("K2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K2:P" & tam)
Cells.Select
Range("B1").Activate
Cells.EntireColumn.AutoFit
Range("M2").Select
End Sub
Sub paso2()
'
' paso2 Macro
'
'
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(MONTH(RC[-10]),months!R[1]C[-16]:R[12]C[28],45,0)"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(MONTH(RC[-11]),months!R[1]C[-17]:R[12]C[28],46,0)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/" & Range("Q2").Value & "*" & Range("R2").Value
Range("A2").Select
tam = Range(Selection, Selection.End(xlDown)).Count + 1
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & tam)
Range("Q2").Clear
Range("R2").Clear
End Sub
Sub paso3()
'
' paso3 Macro
'
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("E16").Select
End Sub
Sub paso4()
'
' paso4 Macro
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B10000")
Columns("A:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
tam = Range(Selection, Selection.End(xlDown)).Rows.Count + 1
Rows(tam & ":20000").Delete
End Sub
Sub RUN_ALL()
'
' RUN_ALL Macro
'
file_1 = Sheets("START").Range("H12").Value
file_2 = Sheets("START").Range("H13").Value
base = ActiveWorkbook.Name
Ruta = ThisWorkbook.Path & "\"
Workbooks.Open Filename:=Ruta & file_1, origin:=xlWindows
Workbooks.Open Filename:=Ruta & file_2, origin:=xlWindows
Windows(base).Activate
Sheets("Funcionarios regulares").Select
With ActiveWorkbook.Sheets("funcionarios regulares").Tab
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
Call paso1
Call paso2
Call paso3
Call paso4
Windows(file_1).Close False
Windows(file_2).Close False
Sheets("Funcionarios regulares").Select
Call pivot1
Ruta = ActiveWorkbook.Path
Sheets(Array("funcionarios regulares", "Informação de custo regulares", _
"Pivot Table regulares")).Select
Sheets("Pivot Table regulares").Activate
Sheets(Array("funcionarios regulares", "Informação de custo regulares", _
"Pivot Table regulares")).Move
ActiveWindow.TabRatio = 0.87
Range("A2").Select
fecha = Month(Date) & "_" & Day(Date) & "_" & Year(Date)
ActiveWorkbook.SaveAs Filename:= _
Ruta & "\Relatório de Horas Extra " & fecha & ". Manaus Plásticos.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close Save
Windows(base).Activate
Sheets.Add , Sheets("months")
ActiveSheet.Name = "Funcionarios regulares"
Sheets("Funcionarios regulares").Select
With ActiveWorkbook.Sheets("funcionarios regulares").Tab
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
Sheets("Start").Select
Range("A7").Select
MsgBox "DONE.", vbInformation, "Relatorio Completed"
'
End Sub
Sub Sheets_Format()
'
' paso5 Macro
'
'
' Recuerda cambiar si te falla el nombr "Hoja1" por "Sheet1"
Sheets.Add
ActiveSheet.Name = "Funcionarios regulares"
Sheets("Funcionarios regulares").Select
With ActiveWorkbook.Sheets("funcionarios regulares").Tab
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
End Sub
Sub pivot1()
'
' pivot1 Macro
'
'
hoja_inicial = ActiveSheet.Name
Range("B2").Select
tam = Range(Selection, Selection.End(xlDown)).Rows.Count + 1
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Sheets.Add
ActiveSheet.Name = "Hoja4"
Sheets("Hoja4").Move after:=Sheets(hoja_inicial)
' NOTA: la comilla simple es para deshabiliar o comentar,
' NOTA_2: Si hay una falla aquí será por cuestión de versiones del Office Excel, cambiar de versión Excel 2007 a 2010 o viceversa
' VERSION 2010
' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
hoja_inicial & "!R1C1:R" & tam & "C16", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Hoja4!R3C1", TableName:= _
"Tabla dinámica2", DefaultVersion:=xlPivotTableVersion14
' VERSION 2007
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"funcionarios regulares!R1C1:R" & tam & "C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Hoja4!R3C1", TableName:= _
"Tabla dinámica2", DefaultVersion:=xlPivotTableVersion12
Sheets("Hoja4").Select
Cells(3, 1).Select
Range("B7").Select
With ActiveSheet.PivotTables("Tabla dinámica2")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("CC")
.Orientation = xlRowField
.Position = 1
End With
Range("A7").Select
ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("CC").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Descrição do CC")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Diretoria").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Soma com Encargo")
.Orientation = xlRowField
.Position = 3
End With
Range("C4").Select
ActiveSheet.PivotTables("Tabla dinámica2").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica2").PivotFields("Soma com Encargo"), "Suma de Soma com Encargo" _
, xlSum
Range("A3").Select
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields( _
"Suma de Soma com Encargo")
.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""??_-;_-@_-"
End With
Call pivot2(hoja_inicial)
Call pivot3(hoja_inicial)
Call pivot4(hoja_inicial)
Call pivot5(hoja_inicial)
Call pivot6(hoja_inicial)
End Sub
Sub pivot2(hoja_inicial)
'
' pivot2 Macro
'
'
Range("B8").Select
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("A:D").Select
Selection.Copy
Range("F1").Select
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E3").Select
Application.CutCopyMode = False
Selection.ClearContents
Columns("E:H").Select
Columns("E:H").EntireColumn.AutoFit
Columns("A:C").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("G:G").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Selection.Style = "Comma"
Range("E2").Select
End Sub
Sub pivot3(hoja_inicial)
'
' pivot3 Macro
'
'
Call Format_Table
If hoja_inicial = "Funcionarios regulares" Then
ActiveSheet.Name = "Informação de custo regulares"
Sheets("Informação de custo regulares").Select
With ActiveWorkbook.Sheets("Informação de custo regulares").Tab
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
Else
ActiveSheet.Name = "Informação custo temp"
Sheets("Informação custo temp").Select
With ActiveWorkbook.Sheets("Informação custo temp").Tab
.Color = 5287936
.TintAndShade = 0
End With
End If
Range("E4").Select
End Sub
Sub pivot4(hoja_inicial)
'
' pivot1 Macro
'
'
'hoja_inicial = ActiveSheet.Name
Sheets(hoja_inicial).Select
Range("B2").Select
tam = Range(Selection, Selection.End(xlDown)).Rows.Count + 1
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Sheets.Add
ActiveSheet.Name = "Hoja4"
If hoja_inicial = "Funcionarios regulares" Then
Sheets("Hoja4").Move after:=Sheets("Informação de custo regulares")
Else
Sheets("Hoja4").Move after:=Sheets("Informação custo temp")
End If
' VERSION 2010
' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
hoja_inicial & "!R1C1:R" & tam & "C16", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Hoja4!R3C1", TableName:= _
"Tabla dinámica2", DefaultVersion:=xlPivotTableVersion14
' VERSION 2007
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
hoja_inicial & "!R1C1:R" & tam & "C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Hoja4!R3C1", TableName:= _
"Tabla dinámica2", DefaultVersion:=xlPivotTableVersion12
Sheets("Hoja4").Select
Cells(3, 1).Select
Range("B7").Select
With ActiveSheet.PivotTables("Tabla dinámica2")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Mão de Obra")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Diretoria")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Diretoria").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields("Soma com Encargo")
.Orientation = xlRowField
.Position = 3
End With
Range("C4").Select
ActiveSheet.PivotTables("Tabla dinámica2").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica2").PivotFields("Soma com Encargo"), "Suma de Soma com Encargo" _
, xlSum
Range("A3").Select
With ActiveSheet.PivotTables("Tabla dinámica2").PivotFields( _
"Suma de Soma com Encargo")
.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""??_-;_-@_-"
End With
End Sub
Sub pivot5(hoja_inicial)
'
' pivot2 Macro
'
'
ActiveSheet.PivotTables("Tabla dinámica2").PivotSelect _
"'Mão de Obra'[All;Total]", xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Range("B8").Select
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("A:D").Select
Selection.Copy
Range("F1").Select
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E3").Select
Application.CutCopyMode = False
Selection.ClearContents
Columns("E:H").Select
Columns("E:H").EntireColumn.AutoFit
Columns("A:C").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("G:G").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Selection.Style = "Comma"
Range("E2").Select
End Sub
Sub pivot6(hoja_inicial)
'
' pivot3 Macro
'
'
Call Format_Table
If hoja_inicial = "Funcionarios regulares" Then
ActiveSheet.Name = "Pivot Table regulares"
Sheets("Pivot Table regulares").Select
With ActiveWorkbook.Sheets("Pivot Table regulares").Tab
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
End With
Else
ActiveSheet.Name = "Pivot Table temp"
Sheets("Pivot Table temp").Select
With ActiveWorkbook.Sheets("Pivot Table temp").Tab
.Color = 5287936
.TintAndShade = 0
End With
End If
Columns("E:G").Select
Selection.Cut
Range("E4").Select
If hoja_inicial = "Funcionarios regulares" Then
Sheets("Informação de custo regulares").Select
Range("I1").Select
ActiveSheet.Paste
Range("F1").Select
Else
Sheets("Informação custo temp").Select
Range("I1").Select
ActiveSheet.Paste
Range("F1").Select
End If
Application.CutCopyMode = False
Range("E4").Select
End Sub
Sub Format_Table()
Range("G4").Select
tam = Range(Selection, Selection.End(xlDown)).Rows.Count + 3
Range("E4:G" & tam).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("E4:G4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E" & tam & ":G" & tam).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub