Consulting

Results 1 to 9 of 9

Thread: Paste Especial/Value/Transpose . . .

  1. #1

    Paste Especial/Value/Transpose . . .

    Dear Experts,
    I wonder if any of you could please help me with the following dilemma.
    I’d like to be able to search for a name stored in Cell G3 of the Worksheet called NOTE JOURNAL 3, down on column G, so that if the one is found is not the one I want, when Click on the button, the macro will take it from there to the way down. When the correct match is found, I would like to move 1 column to the left [Column F] and from there, at that row, to select from column F to column O. Then to copy/paste special/value/ transpose into range G9 to G 18 of the Worksheet called Notes Tool. To finish be able to do almost the same thing for column R of the Worksheet called NOTE JOURNAL 3 [at the same row where the name was last found] and then copy/paste special/value/into Cell G12 of the Worksheet called Notes Tool.
    I appreciate a lot your help and wish you all the best. Thank you very much . . .
    Marco.-

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Marco,
    If you create a sample workbook and post it, that saves each potential answerer having to do so, and increases the chance of a correct solution to your problem.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3

    Hereby is the sample of the Workbook doctor . . .

    I'm not sure if I'm sending it the right way.

    Thank you for your help Doctor. . . . Marco.-

  4. #4

    I place it in the thread. Thank you Doctor . . . Marco.-

    Quote Originally Posted by mdmackillop
    Hi Marco,
    If you create a sample workbook and post it, that saves each potential answerer having to do so, and increases the chance of a correct solution to your problem.

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try this. You need to remove the Hyperlink from your buttons.

    [vba]
    Option Explicit
    Dim LastVal As Range
    Sub SearchNow()
    Dim c As Range
    If LastVal Is Nothing Then Set LastVal = Range("G3")
    Set c = Columns("G").Find(Range("G3"), lookat:=xlWhole, after:=LastVal)
    If c.Row = 3 Then
    Set LastVal = c
    SearchNow
    Exit Sub
    End If
    Set LastVal = c
    c.Select
    End Sub

    Sub PasteNotes()
    If ActiveCell <> Range("G3") Then
    MsgBox "Select name cell"
    Exit Sub
    End If
    ActiveCell.Offset(, -1).Resize(, 10).Copy
    Sheets("Notes Tool").Range("G9").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    ActiveCell.Offset(, 11).Copy
    Sheets("Notes Tool").Range("G12").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End Sub

    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6

    Thank you very much doctor . . .

    I'll try it right now. I appreciate it. All the best and have a wondeful day, Marco.-

  7. #7

    got it sorry the hyperlinks. thanks again marco

    Quote Originally Posted by mdmackillop
    Try this. You need to remove the Hyperlink from your buttons.

    [vba]
    Option Explicit
    Dim LastVal As Range
    Sub SearchNow()
    Dim c As Range
    If LastVal Is Nothing Then Set LastVal = Range("G3")
    Set c = Columns("G").Find(Range("G3"), lookat:=xlWhole, after:=LastVal)
    If c.Row = 3 Then
    Set LastVal = c
    SearchNow
    Exit Sub
    End If
    Set LastVal = c
    c.Select
    End Sub

    Sub PasteNotes()
    If ActiveCell <> Range("G3") Then
    MsgBox "Select name cell"
    Exit Sub
    End If
    ActiveCell.Offset(, -1).Resize(, 10).Copy
    Sheets("Notes Tool").Range("G9").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    ActiveCell.Offset(, 11).Copy
    Sheets("Notes Tool").Range("G12").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End Sub

    [/vba]

  8. #8

    Copy/Paste into a Range

    Dear Experts,
    I wonder if you could help me with this little dilemma.
    From the previous question I posted I just realized I need to add another piece of information to be Copy/Pasted from NOTE JOURNAL 3 Worksheet into Note Tool Whorksheet. MD gave the code for a single cell and it works fine, but in this case I have the Range B28:V30 which I have also tried as “REASON” and neither
    Sheets("Notes Tool").Range("B28").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    ActiveCell.Offset(, 11).Copy OR
    'Sheets("Notes Tool").Range("REASON").PasteSpecial Paste:=xlPasteValues
    . . . seem to work.

    All I want is to be able to paste the content of a cell in a Woorksheet into a Range of another Worksheet. What shall I do?
    Thank you very much for your kind help . . .

    Marco.-

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]Range("Reason").Copy
    Sheets("Notes Tool").Range("I8").PasteSpecial Paste:=xlPasteValues, Transpose:=True[/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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