Page 2 of 2 FirstFirst 1 2
Results 11 to 16 of 16

Thread: Grab info

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    it works!!! I dont want to be abusive... but these paste all information side-by-side... can we put them from top to bottom?

    If u can hand me on these question i will allways be thankfull..

    but for now and for all u give... THANK YOU!

  2. #12
    Not necessary... i made one... if u want i can post here.

  3. #13
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    You can post your solution it will help others that face similar problems

    We're glad you're sorted!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #14
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,343
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Simon Lloyd View Post
    You can post your solution it will help others that face similar problems

    We're glad you're sorted!
    Totally agree on both points.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #15
    Can you tell me how can i open all "mailto" in a column?

    i've a way to do that with hyperlinks.

    Code:
    Sub abriremails()
    Dim i, LastRow
    LastRow = Range("F" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
    If Cells(i, "F").Hyperlinks.Count > 0 Then
    Cells(i, "F").Hyperlinks(1).Follow
    End If
    Next
    End Sub
    What i've made was:

    Code:
    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

  6. #16
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Woah!!!!!! you don't need to select anything to manipulate it, all that selecting will make your code sluggish

    Take a look at it, look for places where you Range("Xx").Copy then Range("Yy").Select Selection.Pastespecial....etc, that can be done more efficiently like this
    Range("Xx").Copy
    Range("Yy").Pastespecial....etc

    See no selects, just like you have Cells.Select, Cells.EntireColumn.Autofit, you can do that in one line like this:
    ActiveSheet.UsedRange.Columns.AutoFit

    Gop through that code carefully and you'll condense it and optimise it easily
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Page 2 of 2 FirstFirst 1 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •