Sub DadosClientes()
'
' DadosClientes Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Impresso Abert Conta").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Dados").Select
Range("A1").Select
ActiveSheet.Paste
Range("B7").Select
Application.CutCopyMode = False
Rows("1:1").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.Copy
Range("B7").Select
ActiveSheet.Paste
Range("B8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=HLOOKUP(R[-7]C[1],R[-7]:R[-6],2,FALSE)"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B11"), Type:=xlFillDefault
Range("B8:B11").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = "=HLOOKUP(R[-1]C,R[-7]:R[-6],2,FALSE)"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B11"), Type:=xlFillDefault
Range("B8:B11").Select
Selection.AutoFill Destination:=Range("B8:CD11"), Type:=xlFillDefault
Range("B8:CD11").Select
Rows("7:11").Select
Range("Z7").Activate
Selection.Copy
Range("A14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("B1:B5").Select
Application.CutCopyMode = False
Selection.Copy
Range("A14").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A15").Select
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(VLOOKUP(Dados!RC[1],'Links Dados de Conta'!C2:C3,FALSE),Dados!RC[1])"
Range("A15").Select
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(VLOOKUP(Dados!RC[1],'Links Dados de Conta'!C[1]:C[2],FALSE),Dados!RC[1])"
Range("A15").Select
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(VLOOKUP(Dados!RC[1],'Links Dados de Conta'!C[1]:C[2],2,FALSE),Dados!RC[1])"
Range("A15").Select
Selection.AutoFill Destination:=Range("A15:A95"), Type:=xlFillDefault
Range("A15:A95").Select
ActiveWindow.SmallScroll Down:=-84
Range("B14").Select
Selection.Copy
Range("A14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E15").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "mailto:"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(CONCATENATE(RC[-1],RC[1]),RC[1])"
Range("E15:F15").Select
Range("F15").Activate
Selection.AutoFill Destination:=Range("E15:F95"), Type:=xlFillDefault
Range("E15:F95").Select
ActiveWindow.SmallScroll Down:=-72
Range("G14").Select
Selection.Copy
Range("F14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:13").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Range("D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
Range("A1:H1").Select
Range("H1").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:H1").Select
Selection.Font.Bold = True
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
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
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("A1:H1").Select
Range("H1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D5").Select
ActiveWindow.SmallScroll Down:=-21
Range("D1").Select
End Sub