Paste/Special/Values

[ben_hogan]

Dear Mister(s) Vbax,
I use a Range of Cells to hold information, Ie: on Cell E9 I have =D9&" "&G9, then for E10 I have =D10&" "&G10 and so on and so forth about ten rows down. The Range E10:E25 is formatted for aesthetic purposes with white font and background.
I’m trying to Copy and Paste the information from such Range into another Worksheet. I’ve tried the Paste/ Special /Values and not only the Pasting is done keeping the source formatting, but it also forms a block if information I cannot see even if I reformat the Range to default settings, meaning for background and font color. See the code I'm using at the bottom.

So, could any of you please help me with some code that will help me solve this dilemma? - Note: I would also now need to transpose the pasting.

Thank you,

Marco.-
Code:
Sub go_to()
'
' go_to Macro
'
'
Sheets("Notes Tool").Select
Range("E8:E33").Select
Selection.Copy

Sheets("NOTE JOURNAL2").Select
'Range("A1").Select
Application.SendKeys "^{END}", Wait
Application.SendKeys "^{END}", Wait

'--------------------------------------------
Dim PauseTime, Start, Finish, TotalTime
'If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
'-----------------------------------------------

'Application.SendKeys "^"{END}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
''Application.SendKeys "{ENTER}", Wait
'Dim PauseTime, Start, Finish, TotalTime
'PauseTime = 5 ' Set duration.
' Start = Timer ' Set start time.
' Do While Timer < Start + PauseTime
' DoEvents ' Yield to other processes.
'Application.SendKeys "{ENTER}", Wait

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Application.SendKeys "^{ENTER}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait


End Sub

[lucas]

Hi Ben, welcome to the board. could you possibly post a sample of the data by selecting the go advanced button and scrolling down till you find a button that says "manage attachments"?

be sure no private or proprietary info is included in the example workbook.

[ben_hogan]

To simplify the question all I'm trying to do is a code to Copy/Paste/Special/Values/Transpose a Range into/and to be added to a database in another worksheet. I use the Values option to remove the cell and font special format in the data source. Thank you for your help . . . Marco.-

[mdmackillop]

This is pasting the values, then pasting again the whole of the cell formatting etc.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste

Try
[vba]
Sub go_to()
Sheets("Notes Tool").Range("E8:E33").Copy
Sheets("NOTE JOURNAL2").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

'To Transpose
Sub go_to_Transpose()
Sheets("Notes Tool").Range("E8:E33").Copy
Sheets("NOTE JOURNAL2").Range("A1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub


[/vba]