Recorded Macro doesn't work properly???

W

Willie03

Board Regular
Joined
Jun 21, 2013
Messages
50
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...

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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Sorry about that I put the pivots as well....

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


    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
321
RoryA
RoryA
P
  • Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
181
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
847
kerm007
K
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
478
kevin9999
K
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
495
JayB0730
J

Forum statistics

Threads
1,214,641
Messages
6,120,685
Members
448,978
Latest member
rrauni

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top