PDA

View Full Version : Paste Especial/Value/Transpose . . .



ben_hogan
04-12-2010, 08:08 AM
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.-

mdmackillop
04-12-2010, 09:34 AM
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.

ben_hogan
04-13-2010, 08:40 AM
I'm not sure if I'm sending it the right way.

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

ben_hogan
04-13-2010, 08:41 AM
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.

mdmackillop
04-13-2010, 09:36 AM
Try this. You need to remove the Hyperlink from your buttons.


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

ben_hogan
04-13-2010, 09:39 AM
I'll try it right now. I appreciate it. All the best and have a wondeful day, Marco.-

ben_hogan
04-13-2010, 10:32 AM
Try this. You need to remove the Hyperlink from your buttons.


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

ben_hogan
04-14-2010, 09:34 AM
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.-

mdmackillop
04-14-2010, 10:21 AM
Range("Reason").Copy
Sheets("Notes Tool").Range("I8").PasteSpecial Paste:=xlPasteValues, Transpose:=True